CODEWITHSUNDEEP
phpmysql
Nathan Loding
Nathan Loding

Reputation: 3235

Building MySQL query based on posted variables

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?

Upvotes: 3

Views: 3962

Answers (3)

dogatonic
dogatonic

Reputation: 2788

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);

Upvotes: 0

grateful.dev
grateful.dev

Reputation: 1437

I'd 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;

You could alternatively collect all statements into an array and just go:

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

Upvotes: 5

inakiabt
inakiabt

Reputation: 1963

You can extend this:

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

Upvotes: 0

Related Questions