SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Searching a combined "name" field is complicated

    My database has 1 field for "name", instead of a f_name, l_name set. Users of the form type all kinds of stuff in there, like "Mr. John F. Doe" and "Sir Walter T Scott III". This makes searching difficult for me. Also, there is another field for their spouse, called "spouse_name" and it's got the same thing going on.

    I'd like to search both 'name' and 'spouse_name' and come up with good results. The current query I'm using is:
    Code MySQL:
    $s = mysqli_real_escape_string($dbc, $_GET['s'])
     
    SELECT 
        name, spouse_name 
    FROM 
        members
    WHERE
        (name LIKE '%$s%' OR spouse_name LIKE '%$s%')

    The search works all right, but some things get in the way. For example, a search for "John Doe" will not find the record "John J. Doe".

    I performed a full text search on the name and spouse_name fields, then tried this:
    Code MySQL:
    SELECT 
        name, spouse_name 
    FROM 
        members 
    WHERE 
        (MATCH(name,spouse_name) AGAINST ('$s') )

    And that turned up thousands of results. The same search for "John Doe" finds every John and every Doe in either field.

    I'm not sure what to do next, so your suggestions are much appreciated!

  2. #2
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Look for full text search with relevance sorting

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks! It looks like trying something like this (taken from the MySQL Reference Manual):
    Code MySQL:
    SELECT 
        MATCH (name) AGAINST ('$s') AS Relevance
    FROM 
        members
    WHERE
        MATCH (name) AGAINST ('$s' IN BOOLEAN MODE)
        HAVING 
            Relevance > 0.1
    ORDER BY Relevance DESC
    Produces some better results, because it's ordering them by their score. Then I can LIMIT 100 since it still returns a ton of names. Thanks for the tip!


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
  •