WHERE query with multiple variables.

[lspiehler]

I've set up a ticket\work order system that I am trying to create a form to be able to search for tickets with varying attributes. The form includes a field to enter the clients name, and dropdowns to select whether the ticket has been billed, closed, who took the call, and who was dispatched to the job. There are a lot of variables involved, but not all of them will always have values specified.

 

If my query looks like this:

 

$query = "SELECT * FROM fieldtickets WHERE (clientname = '$clientname'  AND openorclosed = '$openorclosed' AND billstatus = '$billstatus' AND secretary = '$secretary' AND technician = '$technician' ORDER BY apptdatetime";

 

This query works fine if every section of the field is filled out, but if you only want to see all open tickets, essentially marking all other fields as "all". It doesn't work. I've tried finding a wildcard to use in the case that a field is set to all, but I did not see that one exists without using LIKE. I know I can make it work by making the whole query conditional, based on which fields are set, but that would become a huge amount of conditions depending on how many search options I have. How should I go about doing this? Thanks for any help!

 

 

[requinix]

I know I can make it work by making the whole query conditional, based on which fields are set, but that would become a huge amount of conditions depending on how many search options I have.

Really?

$conditions = array();

if ($clientname) $conditions[] = "clientname = '{$clientname}'";
if ($openorclosed) $conditions[] = "openorclosed = '{$openorclosed}'";
if ($billstatus) $conditions[] = "billstatus = '{$billstatus}'";
// ...

There's a shorter alternative, but the only reason for using it would be laziness on your part. You would still need a series of conditions anyways.