Searching a mysql field contents, in any order

    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.
Who is Participating?
Nathan RileyFounderCommented:
Does the variable always include spaces?

You could explode your variable with PHP by space, then loop them through your query statement.
Ray PaseurCommented:
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().
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.