search by last name or first name or by last name and first name

[sunder]

I have a search form which processes by searching for the first name or last name entered, but when the user enters both first and last name it does not seem to work.

here is the code:

mysql_connect("localhost","root","password");


mysql_select_db("transdata") or die("Unable to select database");

  $var =$HTTP_GET_VARS['q'] ;

  $trimmed = trim($var);
  $limit1=10;
if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }

if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }


$query = "select distinct last_name, first_name, agencies.agency_name,
mailing_address, city, state, zip_code,area_code,phone_number, extension 
from agencies,contacts,phone_numbers 
where agencies.agency_id = contacts.agency_id and contacts.contact_id=phone_numbers.contact_id and
(last_name like '%$trimmed%' or first_name like '%$trimmed%' ) 
order by last_name"; 

echo $query;

Suppose i have "John Smith " in the database, If I search by "John", I get the correct result, If I search with "Smith", I get the correct result, But If I search with "John Smith", the code does not generate any result.

Can anyone please point out as to what I am doing wrong here?

Thanks
Sunder.

[barand]

I guess iits because nobody has a first name like 'John Smith' or a last name like 'John Smith'

[Sxooter]

What you'll need to do is split the entry string on spaces

$query = "select * from table where ";
$tokens = (split(" +",$input));
$flag=0;
foreach($tokens as $token){
  if ($flag) $query.=", OR ";
  #if ($flag) $query.=", AND "; # uncomment to make it an anded group of ORs.
  $query.= "(last_name like '%$token%' OR first_name like '%$token%' )";
  $flag=1;
}

Untested code, but you get the idea.