Refining filter-based search results

Question

I am working on an e-commerce site. By showing available mobile phones, the user can filter the results by the following filters:

• The brand

  • Samsung
  • Nokia
  • HTC
  • (and much more)

• Price range

  • $ 100- $ 200
  • $ 200- $ 300
  • (etc.)

• Category

  • Android Mobile Phones
  • inexpensive mobile phones
  • Music phones
  • 3G Phones
  • ... (and much more)

I want to use the following GET parameters to generate an SQL query that will be executed every time 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, the PHP code for generating the SQL query is as follows (using many if/else and isset() if/else ):

 $sql = "SELECT * from items "; if(isset($_GET['brand'])) { $sql = $sql . "where brand=".$_GET['brand']." "; /*similar statements*/ } 

Please do not pay attention to the accuracy of the above PHP instructions, I did not mention the full code. Finally, I have the following SQL code that will give the result:

 SELECT * FROM ITEMS WHERE BRAND=SAMSUNG; 

This SQL query will result in a product mapping, and I will accordingly display the results on the web page. Answer the following questions:

• After the above filters (brand), suppose that a price filter is also applied. How can I know that the brand filter already exists, so that I can redirect the user to

  http://xyz.com/mobiles.php?brand=samsung&priceMin=100&priceMax=200

  INSTEAD OF NEXT URL

  http://xyz.com/mobiles.php?priceMin=100&priceMax=200

  i.e. how can i just add price criteria to the url?

• Is there any software / library / code for filtering products?

• Is there a better way to filter products or any other way to generate SQL than the method mentioned above?

I use PHP, MySQL, Apache on a Windows computer.

Answer 1

Let me answer your question.

1.If the user is already filtering a specific brand, just save the brand in a session variable

 $sql = "SELECT * from items "; if(isset($_GET['brand'])) { $_SESSION['brand'] = $_GET['brand']; //your code } 

Then in the next query check for the presence of this variable

 if($_SESSION['brand']) { $url = $_SERVER['PHP_SELF'] . '?brand=' . $_SESSION['brand']; header( 'Location:' . $url ); exit; } 

2.I did not know about ..

3. You can create a 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 

Answer 2

I will be tempted to create a dispatch table that calls a function for each query parameter. This allows you to create a whitelist of secure query parameters. I would also use parameterized statements to help protect against SQL injection (that your existing code is not protected from). PDO simplifies the use of parameterized statements.

Creating a separate function for each request parameter may seem unnecessary at first, but this means that you can put all your conditions in a separate file, thereby preserving the main function of the request. It also facilitates the implementation of future improvements.

The following is an example of a non-standard example. It should not be ready to cut and paste into the application. This is just to give you an idea of ​​what I mean. In a real application, among other things, you need to enable error checking and, most likely, move the material to connect to the database to another location.

 // ** 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... }