Page 1 of 1

mysql searches help - need specifics

Posted: Mon Mar 06, 2006 10:53 am
by silent assassin
Hey guys, I'm having a slight problem and need help.

I have a database with a table called 'customers', which has the fields firstname, lastname, homephone, workphone, and emailaddress. I need to be able to search this field. $searchterm is the input the enduser has put into the search bar. So currently, I have it set up as such...

Code: Select all

$splitsearch = explode(" " $searchterm);
$termcount = count($splitsearch);

$i=0
while($i<$termcount)
{
     $query = "SELECT firstname, lastname, homephone, workphone, emailaddress, FROM customers, WHERE firstname LIKE '%$splitsearch[$i]%' || lastname LIKE '%$splitsearch[$i]%' || homephone LIKE '%$splitsearch[$i]%' || workphone LIKE '%$splitsearch[$i]%' || emailaddress LIKE '%$splitsearch[$i]%'";

$i++;
}

$result = mysql_query($query);
However, this only works well if there is only ONE search term. So if I put in someone's first name, everything works fine, but if i put in there first AND last name, it only pulls up lastname results. I actually need to be able to pull up matches that match ALL the search terms.

Can anyone help please? This is quite important, and I would be most grateful. Thanks!!

Posted: Mon Mar 06, 2006 11:17 am
by feyd
the logic error is due to overriding $query each pass of the loop.

Code: Select all

$query = 'SELECT `firstname`, `lastname`, `homephone`, `workphone`, `emailaddress` FROM `customers` WHERE ';
$added = '';
foreach($termcount as $s)
{
  $added .= (empty($added) ? '' : ' OR ') . "`firstname` LIKE '%{$s}%' OR `lastname` LIKE '%{$s}%' OR `homephone` LIKE '%{$s}%' OR `workphone` LIKE '%{$s}%' OR `emailaddress` LIKE '%{$s}%'
}