searching multiple fields

[crobinson42]

I have a form with drop menu's where i'd like to search a table for 3 things.
-employee
-location
-date

here is my code:
$query = "SELECT * FROM dar WHERE date='date' AND employee='$employee' AND location='$location'";

It only works if you select a value for all 3 drop menu's.

My question: How can i make the SELECT query still work if the user does not select a value for all of the drop menu's on the form? IE: I only pick a date and hit submit...

Thanks for the help!

Cory

[djr33]

You can't.
You'll need to dynamically generate the conditions (everything after WHERE) based on what you have:

$conditions = array('date','employee','location');

foreach($conditions as $c) {
if isset($$c) {
$condition[] = $c."='".$$c."'";
}
}
$c = isset($c)?'WHERE '.implode(' AND ',$c):'';

$query = 'blah blah blah'.$c.';'; //becomes BLAH BLAH BLAH WHERE X=...........;


That's messy, but I think it will work.
It uses the kinda ugly $$ format which references a dynamically named variable (or variable variable):
$a = 'b';
$b = 1;
echo $$a; //echos '1'



The best way to redesign all of this is to deal with the information already in an array then use implode like I did above. Simpler, but that should patch what you currently have.

By the way, I set it up to handle nothing sent so you don't ahve a where statement at all. You could also refuse the query if no value was sent.

Note also that I added a semicolon to end the query. While this isn't required for one-statement MySQL queries, it's a good idea for security reasons: it helps ensure that no one is injecting something malicious at the end of the query. (It's not foolproof, but it helps along with other things like escaping the data.)

[crobinson42]

i found a tutorial that explains 'prepared' statements really well.

It is a short and sweat way to create dynamic query's, thanks for the input!

http://forum.codecall.net/php-tutorials/12442-php-5-mysqli-prepared-statements.html