<?php
// DON'T DO THIS
// this is only to set up for my demonstration. don't do this in your code.
$_GET["size"] = 5;
$_GET["manufacturer"] = array("Nike", "Adidas", "Reebok");
$_GET["invisible"] = 'N';
$_GET["instock"] = 'Y';
$_GET["startat"] = 0;
$_GET["results"] = 30;
// END "DON'T DO THIS" section
// the following lines are commented out so that the code will work on Codepad.
// in your code, these lines should not be commented out (and of course, they should have your credentials).
//$con = mysqli_connect("[CENSORED]", "[CENSORED]", "[CENSORED]") or die("Could not connect: ". mysqli_error());
//mysqli_select_db("[CENSORED]") or die("Could not select database");
// this array maps your form fields to your table columns.
// anytime you add another form field, you should add an entry to this array.
$columns = array(
// [form field] => [table column]
'size' => 'product_size',
'manufacturer' => 'product_manufacturer',
'invisible' => 'hideproduct'
// ...
);
// initialize arrays
$whereClause = array();
// set default $limit parameters. these will be overwritten if these values are passed into the file.
$limit = array("startat" => 0, "results" => 15);
// loop through each variable passed in via $_GET
foreach ($_GET as $key=>$value)
{
// THIS IS IMPORTANT!!!!!!
// this will escape your variables to prevent sql injection
$key = mysqli_real_escape_string($key);
if (is_array($value))
{
for ($i = 0; $i < count($value); $i++)
$value[$i] = mysqli_real_escape_string($value[$i]);
}
else
$value = mysqli_real_escape_string($value);
// if there is anything special that should happen for a particular variable passed in,
// it should be done here. So if you don't want it to show up in the query as:
// [column] = '[value]'
// then you need to add another `case` statement and add it to the $whereClause in its own special way.
// if there is nothing special, and it's ok to just add it as [column] = '[value]', don't do anything here.
switch ($key)
{
// the `size` column must be added in its own special way. so there is a special exception here:
case 'size':
$whereClause[] = "({$value} BETWEEN productsizeDOWN AND productsizeUP)";
break;
// these variables don't go into the $whereClause. they go into the $limit part.
// notice there is no `break` between the two cases. both of these cases will go into $limit.
case 'startat':
case 'results':
$limit[$key] = $value;
break;
// another special case:
case 'instock':
$whereClause[] = "(stockstatus ". ($value == 'N' ? "<=" : ">") ." '0' ". ($value == 'N' ? "AND" : "OR") ." donotallowbackorders = '". ($value == 'N' ? "Y" : "N") ."')";
break;
// if it's not a special case from above, then we will add it to the $whereClause in the regular way.
// we will convert the form field name to the table column name using the lookup array $columns.
default:
{
// if the value passed in is an array (like product_manufacturer), we need to list them out differently:
if (is_array($value))
$whereClause[] = "{$columns[$key]} IN ('". implode("', '", $value) ."')";
// this is the standard way of adding a parameter onto the $whereClause:
else
$whereClause[] = "{$columns[$key]} = '{$value}'";
}
}
}
$sql = "SELECT * FROM ioa7pd_Products". (empty($whereClause) ? "" : " WHERE ". implode(" AND ", $whereClause)) ." LIMIT {$limit['startat']}, {$limit['results']}";
echo $sql;
// DON'T DO THIS
// the only reason this function is here is so that the code will work on Codepad.
// this is a built-in PHP function. do not attempt to define it in your code.
function mysqli_real_escape_string($string)
{
return addslashes($string);
}
// END "DON'T DO THIS" section
?>