Dynamic SQL SELECT Statement with PHP based on use

Question

First of all I want to mention that I've been trying and searching like crazy to find a solution to this but no luck so far. My issue is the following:

I have a MySQL database with dozens of rows and I created a jQuery grid to display the data; this page is working already. As per requirements I'm putting together a page where people can select a couple options from checkboxes and filter the results. This is how it will work:

Page 1 will have 3 groups of checkboxes (Name, Location, Language) and each set has different options ([Name: David, Nick, etc], [Location: New York, Washington, Mumbai, London, Miami], [Language: English, Spanish]. Once the user selects his options it will submit a form with something like this

grid.php?location=Washington&location=Mumbai&language=English&name=David

Creating the string is easy but the receiver that will take this parameters and place then in an array to then create the SQL statement is where I'm struggling.

I've tried with something like this:

$location= $_GET['location'];
$language= $_GET['language'];
$name= $_GET['name'];

if($location!=""){
    $where[] = 'location=';
    $where_args[] = $location;
}
//same code for the other 2 options

$where_clause = implode(' OR ', $where_args);

$sql_json = "SELECT * FROM myTable WHERE $where_clause ORDER BY id DESC";

None of the options are mandatory so the query needs to accept the possibility that the value may or may not be set so it may end up being something like grid.php?language=English.

I know that the code above doesn't quite work but I will appreciate if someone can please point me in the right direction.

Answer 1

try this:

<?php
$location= $_GET['location'];
$language= $_GET['language'];
$name= $_GET['name'];

if($location!=""){
    $where[] = " `location` = '".mysql_real_escape_string($location)."'";
}
if($language!=""){
    $where[] = " `language` = '".mysql_real_escape_string($language)."'";
}
if($name!=""){
    $where[] = " `name` = '".mysql_real_escape_string($name)."'";
}
$where_clause = implode(' OR ', $where);
//same code for the other 2 options

$sql_json = "SELECT * FROM myTable WHERE $where_clause ORDER BY id DESC";
?>

Answer 2

You could always just iterate through the $_GET and grab keys with values, so:

foreach ($_GET as $key=>$val) {
   if ($val != "") {
      $where_args[] = "$key='$val'";
  }
} 
$where_clause = implode(' OR ', $where_args);

You'd probably want to do better validation than the above example, though, and you could add select/case statement if you needed to perform checks on particular values...