How can I avoid separating the search query to search a MySQL database?

Question

When a user tries to search for people on our system, we've noticed that they often type a full name for their search.
How can we enable full name searching on our database when first name and last name are stored in different columns?

Answer 1

A working but neither fast nor reliable solution would be:

SELECT foo
FROM   bar
WHERE  CONCAT(firstname, ' ', lastname) = $search_name

(Not sure about MySQL CONCAT syntax atm, you might need to concat twice)

There is a high chance that this won't use any indexes, slowing down the search a lot.

A better solution would be to just split the name:

$names = explode(' ', $search_name);

SELECT foo
FROM   bar
WHERE  firstname = $names[0]
AND    lastname  = $names[1]

Even better:
Provide two input boxes and label them first and lastname so users will enter the search correctly.

Answer 2

Your 99% case is probably going to be this:

1. Split the string on the first whitespace
2. Assume the first part is the first name, and the 2nd part is the last name

Depending on the number of users in your system, and the... "creativeness" of their names, you may get a few false negatives from this approach.

You could also consider providing separate fields to the user.

Answer 3

How do you search the people in your DB?

If you have a sql-querry you can do it somehow like that:

1. Separate the search text into parameters. so you have firstname and lastname in 2 separate parameters. (Stackoverflow: How to split a string in php)

2. then you can use the different parameters to build a querry that fits your needs:

   select * from People where (firstname LIKE param1 AND lastname LIKE param2) OR (firstname LIKE param2 AND lastname LIKE param1) OR ...

Answer 4

Even better - If you are using CakePHP 1.3 then simply create a 'full_name' virtual field. Do your query against that field.

http://book.cakephp.org/view/1609/Creating-virtual-fields
http://book.cakephp.org/view/1610/Using-virtual-fields

class User extends AppModel {
    ...
    var $virtualFields = array(
        'full_name' => 'CONCAT( User.first_name, " ", User.last_name )'
    );
    ...
}

Since Cake treats virtual fields as regular fields for the primary model you can simply do a find like so.

$User->find( 'all', array(
    'conditions' => array(
        'User.full_name' => $search_name
        ...
    ),
    ...
);

You could even throw in some MySQL LIKE goodness in the conditions

$User->find( 'all', array(
    'conditions' => array(
        'User.full_name LIKE' => '%' . $search_name . '%',
        ...
    ),
    ...
);

This syntax means the end-user can put part of the first, part of the last or the whole name into the search field and still get results without worrying about if the code is doing finds on first, last or both.

Answer 5

You could set up a single fulltext index on first_name and last_name. Then, just include a test for MATCH (first_name, last_name) AGAINST ($query). Or if you have more than just that field to search against, add those to the index as well.

Let SQL do the work for you.