Creating a MySQL query based on published variables

Question

It seems such a simple task, but it's hard for me to find a solution that I like. I can not find anything that could be considered something other than awkward. Here is what I work with:

There is a search form that sends variables to script processing. These variables are filters for the requested data. Depending on the user rights, depending on the filters to which they have access, there may be more or less variables. Each filter refers to a field in the table, the results of which come mainly. One option for each filter is ANY, so there is no WHERE clause.

What a good way to build a query string. Let's say four variables are returned there: $ firstname, $ lastname, $ age, $ dob. But only some users have access to the 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'";
}
...

Etc. But it looks stupid, awful and ridiculously ineffective for me. I use a slightly modified MVC pattern, so it makes sense to build methods in the search model for every possible filter?

Answer 1

I would go for this:

$ query = "SELECT * FROM people";

$ whereClause = "WHERE 1 = 1";
if ($ firstname! = 'ANY' &&! empty ($ firstname)) {
    $ whereClause. = "AND firstname = '$ firstname'";
}
if ($ lastname! = 'ANY' &&! empty ($ lastname)) {
    $whereClause .= " AND lastname='$lastname' ";
}

$query .= $whereClause;

:

if (count($arr)>0) { 
   $query = "$query 
              WHERE ". implode(" AND ",$arr); 
}

Answer 2

:

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

Answer 3

here is some code that will pull out all published variables and merge 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 do not match the table names, or if you want to handle fields differently in your SQL, you can use the 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);