Dynamic SQL SELECT Statement with PHP based on custom parameters
First of all, I want to mention that I tried and searched like crazy to find a solution for this, but no luck so far. My problem is this:
I have a MySQL database with dozens of rows and I created a jQuery grid to display the data; this page is already running. As required, I am putting together a page where people can select a couple of options from checkboxes and filter the results. This is how it will work:
Page 1 will have 3 groups of flags (name, location, language), and each set has different options ([Name: David, Nick, etc.], [Location: New York, Washington, Mumbai, London, Miami ] [Language: English, Spanish] After the user selects their options, they will present a form with something like this
?grid.php location = Washington & location = Mumbai & language = English & name = David
The creation of the string is very simple, but a receiver who will take these parameters and then place them in an array to then create an SQL statement is where I fear.
I've tried 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 parameters are required, so the query should consider the possibility that the value may or may not be set so that it could be something like grid.php? language = English.
I know the above code doesn't quite work, but I would appreciate if someone can point me in the right direction.
source to share
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";
?>
You can always just iterate over the $ _GET keys and capture with values, so:
foreach ($_GET as $key=>$val) {
if ($val != "") {
$where_args[] = "$key='$val'";
}
}
$where_clause = implode(' OR ', $where_args);
You probably want to do a finer check than the example above, and you can add a select / case statement if you need to perform checks on specific values ...
source to share