tags:

views:

124

answers:

3

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?

A: 

You can extend this:

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

inakiabt
Unfortunately I'm not utilizing any sort of PDO at this point; though that is definitely something I'm looking into. I inherited some of this code and I can only change so much in one go.
Nathan Loding
User is redirected to: http://github.com/indeyets/MySQL-Query-Builder/tree/master
Smandoli
+1  A: 
Saggi Malachi
Never thought of an approach like that. I still feel like there's some sort of pattern or something I could follow, but this will at least clean up the code for now.
Nathan Loding
That's a cleaner approach. In fact I'm able to improve some code of mine from both Nathan's opener and this reply. I believe the general answer to Nathan's question is "Yep, PHP can look a bit convoluted. Sorry 'bout that!" I'll stay tuned -- maybe I'm wrong. (And maybe I should look into PDO, per other posted answer.)
Smandoli
When dealing with multiple variables and, depending on their value, the end result changes -- it's going to be convoluted no matter what. The difference is in making it easier to read rather than messier. I'm still playing with it -- I really feel like there's something I'm still missing.
Nathan Loding
Another nice approach would be collecting all statements into an array and then just if (count($arr)>0) { $query .= "WHERE ". implode(" AND ",$arr); }
Saggi Malachi
@Saggi: I went with the array implosion suggestion and it worked beautifully. I feel pretty dumb missing something as simple as that!
Nathan Loding
I added the implosion suggestion to my answer
Saggi Malachi
A: 

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