Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : MySQL word search

[dommymedia]

Climbing the mySQL learning curve, need a hand.

I have a field with Company names, say 1-6 words. What is the best way to set up a multiple keyword query (assume AND search) to do a word search on company names? e.g. searching on "Ford Motor" should match "Ford Motor Company" but not "Waterford Motoring Mills"

[bill_jpn]

Do you also want "Ford Motor" to find such things as "Fordham Motoring" and "Motorboats by Fordham"? If so, one way to do it is,

$xArray = explode(" ", $search);
foreach($xArray as $key=>$value)
{
$searchWhere .= " (MySQL_Field like '$value%'
or MySQL_Field like '% $value')
and ";
}
// finish the final "and"
$searchWhere .= " MySQL_Field != ''";
// Make the query
$query = "SELECT * from TABLE where $searchWhere ";
// etc.

It's best of course to also clean the data; each term should be stripped of HTML and other code, and quotes and so on should be stripped. The foreach will fail if there's an empty $search so that's got to be tested too.