several form fields for search

Hello there,

I'm fighting a problem with a search function with several form fields.

Three of the fields (below: city, dep and title)are filled out by choosing something out of a pull-down menu, two of the fields are filled out by writing.

The problem is to get all of these to function together. At the moment it works only if the user fills out all form fields. If a pull-down menu form field is left unfilled, the function doesn't find any matching rows, due to the fact that the unchosen field is interpreted as a "NULL" by the function.

How do I make the search function work even if all of the fields are not filled out, so that the unchosen would be interpreted as "whatever"? The fields that are filled out by writing (name and surname) do not mess anything up, even though left unfilled.


$sql="SELECT * FROM persons WHERE city='$city' and dep='$dep' and title='$title' and name like '%$name%' and surname like '%$surname%' ORDER BY surname, name ASC";

I'd be thankful for any help.

You're going to need to build your query dynamically in your script based on what fields are filled out. If there's no name choice, leave out the name part of the conditional entirely.

Hi there Madpawn,

I've tried it by specifying it like this, one query for each situation:
if ($city==''){
$sql="SELECT * FROM persons WHERE dep='$dep' and title='$title' and name like '%$name%' and surname like '%$surname%' ORDER BY surname, name ASC";
}

The problem is, though, that even though I specified all the possible situations (if one isn't filled out - if two aren't - if none of them are and so on), it at some point didn't work anymore. Meaning I guess that I'd need one query that allows a situation where any or all of the fields are not filled out.

Edit: OK, I got it working now in the way I said I've tried before. Must have left something out earlier.

Instead of all those if's another format you may like better would be:



That way, you're only adding the AND clauses you need.