SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2006
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Search with multiple fields

    I have a form with essentially 5 different selects, let's call them country, state, subject area, tag, and age range. What I want to do is find all of the users who are the closest match to the search query (once I know how to do this, I can modify the query to find their names, email addresses etc...)

    In the database, users have a country_id (not name!), state_id in one table, and subject area id, tag id, and age range id in another table (all in the same column as multiple rows for the same user).

    I want to do a database search and match all of the users who meet at least 1 of the criteria, and sort them in order of closest match (all 5 criterion) to least match. Assuming I consider the order of preference to be subject area, tag, age range, country, and then state, how do I do this?

    To make the problem even slightly more difficult, the form doesn't return the actual country name, it returns the row the country shows up in the country table (same for the other fields) so I'm currently doing a look up for each field to figure out which country/state/etc... I'm actually dealing with.

    I don't see how I can do a FULLTEXT search for this information because it is all in different columns.

    Anyone have a clue how to solve this complicated query?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    see this thread -- http://www.sitepoint.com/forums/show....php?p=1642882

    assign each field that is matched a different weight, and then sort by combined weight descending
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2009
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In case the columns are fixed, you can search multiple columns for a given text using invert of an IN query, can see an example here
    alltips.in/mysql-search-multiple-columns/
    hope this helps...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •