[Resolved] SELECT with optional fields?

zdislaw

I have a search form where folks can search on first initial and last name. How do I structure the query to say:

$result = mysql_query("SELECT * FROM $table WHERE (fi LIKE '$query_fi' AND lname LIKE '$query_lname%')",$db);

BUT I want it to return results even if the user doesn't type a first initial. The fields in question in the table are all populated.

Basically, I would like any search term to be optional. There are only two now, but I plan on adding more once I get it working this way.

Thanks!!

zdislaw

tomhath

You have a couple of choices. Either build up the WHERE clause in php to have only the non-null values, or always handle the input whether the user entered anything or not. I prefer the latter, usually something like:

SELECT * FROM $table 
WHERE (fi LIKE '$query_fi'  OR '$query_fi' = ' ')
  AND (lname LIKE '$query_lname%' OR  '$query_lname%' = ' ')
...

That way you can look at the query and see what all the expected inputs are and how you have decided to handle each.

Sxooter

Assuming you have two vars, lname and initial, something like this:


$query .= "select * from table where ";
$lpart = "lname like '%$lname%' ";
$ipart = "initial = '$initial' ";
if (eregi("^[a-z]{1}$",$initial)) $i=1;
else $i=0;
if (strlen($lname)>0) $l=1;
else $l=0;
if ($i && $l){
  $query.= $ipart."and ".$lpart;
}else if ($i && !$l){
  $query.= $ipart;
} else if (!$i && $l){
  $query.= $lpart;
} else {
  print "Gimme a fricken bone here people";
}

That code's not tested, but the regex works. Here's the disection of it:

^[a-z]{1}$
^ left side anchor
[a-z] any char from a to z
{1} one occurance of the [a-z]
$ right anchor.

so, it means one character, and one character only, from a-z. using eregi means it acts like [a-zA-Z].

zdislaw

Thanks guys, worked like a charm. Funny how after you see something it just makes sense, huh?