根据过滤器优化搜索结果

Question

I am working on an e-commerce website. While displaying the available mobiles, user can filter the results by the following filters:

1. Brand name

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

2. Price range

   • $100-$200
   • $200-$300
   • (and so on)

3. Category

   • Android Mobiles
   • Low cost mobiles
   • Music Phones
   • 3G Phones
   • ...(and many more)

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:

1. 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?

2. Is there any software/library/code available to filter the products?

3. 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.

Answer 1

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