How to properly search for names

[pioneerx01]

Lets say that I have a table (participant_registrations) with following columns amongst others: first_name, middle_name, last_name, full_name. full_name consists of first_name last_name and middle_name in the midddle is it is available.

 

Let's Say that I have Michael John Smith registerd. How would I set up the query so when someone searches for "Michael John Smith" or "Michael Smith" they would get the record in the results. Right no I have it set up in manner of "... WHERE full_name LIKE '%$term%' ... " which will show the record in results if you search for "Michael John Smith" but not when you search for "Michael Smith".

 

Thanks

[ginerjm]

Dont' duplicate data in your db.  Ie, remove fullname.  Then do you queries with where last_name = ?? and first_name=?? or last_name=??

 

to get something close to the first/last name desired.

[Psycho]

Yep, don't duplicate the First/Middle/Last into a column for Full Name. All you do is make more work and create the potential to get things out of sync. Unless, you have a legitimate business need to have a separate Full Name value. We have this in our application. We auto-populate the Report name field, but the users can put a 'modified' version of the name in the "Report name" field if they wish:

 

FirstName: Robert

MiddleName: David

LastName: Smith

ReportName: Bob Smith

 

If you don't have such a requirement - ditch the Fullname field.

 

Ideally, you would want to set up fulltext searching, but an easy workaround is to split the search string and create multiple LIKE conditions on the concatenated names.

 

Note: I used the mysql_ functions because I'm feeling lazy, you should really use mysqli_ or PDO with prepared statements

 

 

//Process the input
$searchString = $_POST['search'];
$searachWords = explode(' ', $searchString);
 
//Create the where condition for each word
$WHERE_CONDITIONS = array();
foreach($searachWords as $word)
{
    $word = mysql_real_escape_string($word);
    $WHERE_CONDITIONS[] = "CONCAT(first_name, middle_name, last_name) LIKE '%$word%'"
}
 
$query = "SELECT *
          FROM table_name
          WHERE " . implode( " OR ", $WHERE_CONDITIONS);