We help IT Professionals succeed at work.

Searching a mysql field contents, in any order

damianb123 asked
    I have a php script which basically has a select statement, something along the lines of:

select * FROM TABLE x WHERE Name LIKE '%$variable%'

$variable is a passed variable from an input text field.

This works great IF the text being searched is in that order, so if the name field contains:

John William Smith

and I search for John William - it returns the record, but not if I search for:

John Smith, or Smith William

How can this be achieved?

Many thanks for your help.
Watch Question

Does the variable always include spaces?

You could explode your variable with PHP by space, then loop them through your query statement.
Most Valuable Expert 2011
Top Expert 2016
You can use the MySQL "OR" clause to create the query.  Something like this in the WHERE clause:

WHERE Name LIKE %john% OR Name LIKE %smith%

You will want to experiment with the exact patterns - set up lots of test cases.  "John" will also match "Johnson" and "Smith" will also match "Blacksmith."  You may find that padding the strings with blanks may help.  However "Smith " would not necessarily match "Smith" at the end of the name.  A regular expression might help.

More advanced name matching can be achieved with sound-alike terms using soundex() and metaphone().