MySQL searching multiple fields on multiple strings

[kajje]

I have a MySQL table with loads of fields.
On this table I would like to do a multiple 'AND' search.

For example I would 'Microsoft New York'
In this case in my table in the
c_company field there would be 'microsoft'
and in the c_location there would be 'New York'

I guess I'll have to use the php 'explode' function

explode($searchstring,char(32));

to split up my searchstring, but how I could construct the actual query without having a query which is still short enough to be human-readable?


My current code:

$return = "SELECT id, o_companyid, o_staffid, c_source, p_name, c_company, c_alias, p_email1, p_email2, c_location, c_address1, c_address2, c_address3, c_city, c_regio, c_postal, c_body, ...MANY MORE...
FROM addx
WHERE 
id LIKE '%%$search%%'
OR o_companyid LIKE '%%$search%%'
OR o_staffid LIKE '%%$search%%'
OR c_source LIKE '%%$search%%'
OR p_name LIKE '%%$search%%'
OR c_company LIKE '%%$search%%'
OR c_alias LIKE '%%$search%%'
OR p_email1 LIKE '%%$search%%'
OR p_email2 LIKE '%%$search%%'
OR c_location LIKE '%%$search%%'
OR c_address1 LIKE '%%$search%%'
OR c_address2 LIKE '%%$search%%'
OR c_address3 LIKE '%%$search%%'
OR c_city LIKE '%%$search%%'
OR c_regio LIKE '%%$search%%'
OR c_postal LIKE '%%$search%%'
OR c_body LIKE '%%$search%%'
...MANY MORE...
$sort
";

[rocknbil]

You'd construct the select in programming. What you need to decide, though, is if you want AND for each word. Just "or" alone is likely to match on "New" in one field and "Microsoft" in another, giving confusing results. Probably a better way to do it is make it user configurable like what has been proven to work: radio buttons for exact term, all of these words, any of these words. Adjust accordingly.

Something like this, going on the premise of "any of these words in the same field."

//First make sure there's no double spaces 
$term = preg_replace('/\s+/',' ',$_post['term']); 
// Can't recall exact trim syntax ATM, reverting to regexps, sorry. :-) 
$term = preg_replace('/^\s|\s$/','',$term); 
$words = explode(' ',mysql_real_escape_string($term)); 
// 
// Better off using a function to get these field names. 
// You might skip numeric fields, like "id". 
$fields = Array( 
'o_companyid',  
'o_staffid',  
'c_source',  
'p_name',  
'c_company',  
'c_alias',  
'p_email1',  
'p_email2' 
// etc. 
); 
$where=NULL; 
foreach ($fields as $field) { 
 $andwhere=NULL; 
 // only add an OR if $where has been populated 
 if ($where) { $where .= ' or'; } // Note space locations 
 foreach ($term as $wd) { 
 // The opening string alts should have caught 
 // this, but J.I.C. . . . 
 if (! empty($wd)) { 
  if ($andwhere) { $andwhere .= ' and'; } 
  $andwhere .= " $field like '%$wd%'"; 
 } 
 } 
 // Encapsulate and's in (), long story but will give  
 // unexpected results if you don't. Again, note space. 
 if ($andwhere) { $anwhere = " ($andwhere)"; } 
} 
// 
// You **could** just "select *", but I find this is good 
// for specific field selections and efficiency if you  
// don't need them all . . . create second array in that case. 
// 
$select = "select"; 
foreach ($fields as $field) { $select .= " $field,"; } 
// Chop last , or change the above to only add , if it's  
// not the last item in the selected fields array 
$select = preg_replace('/,$/','',$select); 
$select .= " from tablename"; 
// 
// Finally, add the where, if it exists 
if ($where) { $select .= " where $where"; } 
// add order and limit after 

May contain syntax errors, typed on the fly, but something like that.

A note on the alternating use of quotes, often I see coders forget that single quotes do not interpolate variables, doubles do, but this will interpolate $wd:

$andwhere .= " $field like '%$wd%'";

The variable is encapsulated in PHP by the double quotes, the single quote is "just another character" as far as PHP is concerned, but is needed by mysql for non-numeric fields:

.. where field like '%some value%'....

[rocknbil]

$anwhere = " ($andwhere)";

Oops.

$andwhere = " ($andwhere)";