Building MySQL query based on posted variables

Question

This seems like such a simple task, but I'm having a hard time finding a solution that I like for this. I can't find anything I would consider anything other than clunky. Here's what I'm working with:

There is a search form that posts variables to the processing script. These variables are the filters for the data being queried. Depending on the rights of the user, there may be more or less variables coming in, depending on the filters they have access to. Each filter refers to a field in the table the results are coming from, basically. One option for each filter is "ANY" as well, so no WHERE clause is needed.

What's a good way to build the query string. Let's say there's four variables coming back: $firstname, $lastname, $age, $dob. But only some users have access to filter by $age and $dob.

$query = "SELECT * FROM people";
if(($firstname != 'ANY' && !empty($firstname)) ||
   ($lastname != 'ANY' && !empty($lastname)) ||
   ($age != 'ANY' && !empty($age)) ||
   ($dob != 'ANY' && !empty($dob))) {
    $query .= " WHERE";
}

if($firstname != 'ANY' && !empty($firstname)) {
    $query .= " firstname='$firstname'";
}
if($lastname != 'ANY' && !empty($lastname)) {
    if($firstname != 'ANY' || !empty($firstname)) {
        $query .= " AND";
    }
    $query .= " lastname='$lastname'";
}
...

And so on. But that just looks dumb, horrible, and ridiculously inefficient to me. I'm using a slightly modified MVC pattern, so would it make sense to build out methods in the search model for each possible filter?

Answer 1

You can extend this:

http://code.google.com/p/mysql-query-builder/

Answer 2

here's some code that will pull all posted variables and string them together.

foreach($_POST as $name=>$value){
    $arrFields[] = $name." = '".$value."'";
}
$sSql = "SELECT * FROM people WHERE 1 AND ".implode(" AND ",$arrFields);

OR if your field names are not the same as your table names, or if you want to treat the fields differently in your SQL, you can use a switch.

foreach($_POST as $name=>$value){
    switch($name){
     case "firstname":
      $arrFields[] = "fName = '".$value."'";
      break;
     case "lastname":
      $arrFields[] = "lName = '".$value."'";
      break;
     case "age":
      $arrFields[] = "bioAge >= ".$value;
      break;
    }
}
$sSql = "SELECT * FROM people WHERE 1 AND ".implode(" AND ",$arrFields);