Database query for desired results

Question

I am using some filters to display the products. Filters like colors, price and stuff.

Link : http://www.applechain.com/products/iPod.php

I use this query

$sql = "Select * 
  from tbl_product  
  where device='iPhone'  
    and (color='$c1'   
      or color='$c2'    
      or color='$c3'    
      or color='$c4'    
      or color='$c5'    
      or color='$c6'    
      or color='$c7'    
      or color='$c8'    
      or color='$c9'    
      or color='$c10'   
    ) and (storage='$cp1'    
      or storage='$cp2'    
      or storage='$cp3'    
      or storage='$cp4'    
      or storage='$cp5'   
    ) and (f_unlock='$factory')    
    and (warranty='$warranty')    
    and (price >= '$price1'    
      and price <= '$price2'   
    ) 
  order by product_id desc";

I am using the AND condition for main parameters. Now how do I display the result if my only two parameters gets satisfied. How to achieve that if color and storage parameters gets satisfied,it shows result based on them only irrespective of whether the others are selected or not.

score 0 · Answer 1 · answered Nov 18 '13 at 09:37

0

Simple. Remove the other conditions from the query.

BTW, you can rewrite that query this way...

SELECT * 
  FROM tbl_product 
 WHERE device = 'iPhone' 
   AND color IN ('$c1','$c2','$c3','$c4','$c5','$c6','$c7','$c8','$c9','$c10')
   AND storage IN ('$cp1','$cp2','$cp3','$cp4','$cp5')
   AND f_unlock = '$factory'
   AND warranty = '$warranty'
   AND price BETWEEN '$price1' and '$price2'
 ORDER 
    BY product_id DESC;

answered Nov 18 '13 at 09:37

Strawberry

33,750
13
40
57

I applied this but din't get anything. I want that if any of two my parameters are satisfied, it should show results. Doesn't matter others are selected. – user1960856 Nov 18 '13 at 14:51

score 0 · Answer 2 · answered Nov 18 '13 at 09:39

You could build your query based on what is set, normally an ORM tool would do this for you.

As it's probably too late to start using a PHP framework, you should do something like:

if(isset($colours))
{
    $sub_query = concatenateOrClauses($colours, 'color');
    $main_query .= $sub_query;
}

private function concatenateOrClauses($values, $name)
{
    $query_string = "";
    for($i = 0; $i < sizeof($values); $i++)
    {
        if($i == 0)
        {
            $query_string = $name."=".$values[$i];
        }
        else
        {
            $query_string = $query_string." OR ".$name."=".$values[$i];
        }
    }
    return $query_string;
}

Database query for desired results

2 Answers2