I am working on an e-commerce website. While displaying the available mobiles, user can filter the results by the following filters:
Brand name
Price range
Category
I want to use the following GET parameters to generate a sql query which will execute everytime a filter is applied. Suppose the current filter is Brand=Samsung then my link will be http://xyz.com/mobiles.php?brand=samsung
For the above filters, PHP code for generating the SQL query is as follows (using lots of if/else statements and isset() function):
$sql = "SELECT * from items ";
if(isset($_GET['brand']))
{
$sql = $sql . "where brand=".$_GET['brand']." ";
/*similar statements*/
}
Please don't go on the accuracy of the above PHP statements, I have not mentioned the full code. Finally I have the following SQL generated which will provide the result:
SELECT * FROM ITEMS
WHERE
BRAND=SAMSUNG;
This SQL query will result the matching products and I will display the results on webpage accordingly. Please answer the following questions:
After the above filters (brand), suppose price filter is also applied. How can I know that brand filter is already there so that I can redirect the user to
http://xyz.com/mobiles.php?brand=samsung&priceMin=100&priceMax=200
INSTEAD OF THE FOLLOWING URL
http://xyz.com/mobiles.php?priceMin=100&priceMax=200
i.e. how can i just append the price criteria to the url?
Is there any software/library/code available to filter the products?
Is there any better way to filter out products or any better way to generate the SQL than the method I mentioned above?
I am using PHP, MySQL, Apache on Windows machine.
Let me try answer your question
1.If user already filter for specific brand, simply save the brand in the session variable
$sql = "SELECT * from items ";
if(isset($_GET['brand']))
{
$_SESSION['brand'] = $_GET['brand'];
//your code
}
Then in next request check for the existence of that variable
if($_SESSION['brand'])
{
$url = $_SERVER['PHP_SELF'] . '?brand=' . $_SESSION['brand'];
header( 'Location:' . $url );
exit;
}
2.I didnt aware of..
3.You can build better query by adding WHERE 1=1
$query = "SELECT * FROM items WHERE 1=1";
if($_GET['brand')
{
$query .= " AND brand={$_GET['brand'}";
}
//another condition perhaps
I'd be tempted to build a dispatch table that calls a function for each query parameter. This allows you to create a whitelist of safe query parameters. I would also use parameterized statements to help guard against SQL injection (something that your existing code is not protected against). PDO makes using parameterized statements easy.
Creating a separate function for each query parameter may seem unnecessary at first, but it means that you can put all your conditions in a separate file, thus keeping your main query function tidy. It also makes future enhancements easier to implement.
The following is an off-the-top-of-my-head rough example. It's not meant to be ready to cut and paste into an application. It's just to give you an idea of what I mean. In a real app, amongst other things, you'd need to include error checking, and would likely move the database connection stuff elsewhere.
// ** query_params.php **
function query_brand () {
return "brand = ?";
}
function query_price () {
return "price BETWEEN ? AND ?";
}
function query_category () {
return "category = ?";
}
// ** product_search.php **
function search () {
// Build a test GET array.
$_GET = array(
'brand' => 'HTC',
'price' => array(100, 200),
'category' => 'Android Mobiles'
);
// Build a dispatch table of safe query parameters.
$dispatch = array(
'brand' => 'query_brand',
'price' => 'query_price',
'category' => 'query_category'
);
// An array to hold the conditions.
$cond = array();
// An array to hold the bind values.
$bind = array();
foreach ( $_GET as $param => $value ) {
if( isset($dispatch[$param]) ) {
$cond[] = call_user_func( $dispatch[$param] );
$bind[] = $value;
}
}
$sql = "SELECT item, brand, price, category " .
"FROM products";
if( count($cond) ) {
// Combine the conditions into a string.
$where = implode( ' OR ', $cond );
$sql .= " WHERE $where";
}
// Use PDO to connect to the database. This should
// probably be done somewhere else.
$dbh = new PDO(
"mysql:host=localhost;dbname=$dbname", $user, $pass,
);
// Prepare the SQL statement.
$stmt = $dbh->prepare( $sql );
// Execute the statement, passing the values to be
// bound to the parameter placeholders.
$stmt->execute( $bind );
// Fetch and return results...
}