homepage Welcome to WebmasterWorld Guest from 54.226.173.169
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL searching multiple fields on multiple strings
kajje




msg:4096869
 9:36 am on Mar 13, 2010 (gmt 0)

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




msg:4097063
 7:59 pm on Mar 13, 2010 (gmt 0)

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




msg:4097250
 4:39 am on Mar 14, 2010 (gmt 0)

$anwhere = " ($andwhere)";


Oops.

$andwhere = " ($andwhere)";

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved