Searching a combined "name" field is complicated

PHP
1

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:

$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:

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

Look for full text search with relevance sorting

3

Thanks! It looks like trying something like this (taken from the MySQL Reference Manual):


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!