Building an SQL query using multiple (optional) search fields

Question

Hi,

I have a form that is going to be used to search through a table of support tickets.

the user can search from a few difficult optional fields.

• Date (to/from)
• Ticket Status
• Engineer
• Ticket Contact

I'm wondering what is the best way to deal with optional search filters. So I have a query that takes in parameters from the user. So if the user searches using both the from and to dates then the query would want to include BETWEEN. So do I have to write a different query for if the user searches for only from. or another query when the user has not added any date parameters? Then what if the status dropdown is blank? Is that another query?

Any help to clear this up would be great!

Jonesy

Answer 1

I think it would be better if You generate query dynamically at runtime based on which fields are filled. So You could make some helper which appends specific query fragments if only one date is passed and the other one is null, or when both are passed and so on.

Answer 2

Build your query in parts. Start with whatever is constant in your query, and add on more SQL depending on what extra conditions:

$query = "SELECT ...
  FROM ...
  WHERE [where conditions that are always going to be present]";

if (isset($_POST['date_from']) && isset($_POST['date_to']))
{
  $query .= ... // query code for dealing with dates
}

if (isset($_POST['status']))
{
  $query .= ... // deal with status
}

// etc.

// Once you have your query fully built, execute it
$result_set = mysql_query($query);

This code is obviously just a skeleton, but that's how I would construct my query.

Answer 3

Hard to say without knowing what sort of DB abstraction you're using, but assuming you're hand-writing the SQL, it's fairly simple, just build up sections of your where clause individually for each variable. (Assuming here that your vars are already escaped/quoted.)

$where_clause = array();
if (!empty($date_from)) {
    $where_clause[] = "table.date >= $date_from";
}
if (!empty($date_to)) {
    $where_clause[] = "table.date <= $date_to";
}
if (!empty($status)) {
    $where_clause[] = "status = $status";
}

$query = 'select * from table where ' . join(' and ', $where_clause);