MYSQL: where column IN(Any_value)

Question

I am trying to make a dynamic where clause. I am getting some array of check boxes in PHP as following code

$brand = array();
if(isset($_GET['brand']) && !empty($_GET['brand']))
    $brand=$_GET['brand'];  
    $brand_str = implode("' , '",$brand);  
}

MY SQL Query is

$sql="SELECT DISTINCT * FROM products WHERE brand IN('$brand_str')";

if brand is not defined it gives error or no row is fetched but its a simple problem can be solved using following approach.

MY approach:

I use a variable like 'flag_for_filter_brand' inside if statement that is if flag_for_filter_brand=1 the QUERY is

$brand = array();
$flag_for_filter_brand=false;
if(isset($_GET['brand']) && !empty($_GET['brand']))
    $brand=$_GET['brand'];  
    $brand_str = implode("' , '",$brand);  
    $flag_for_filter_brand=true;
}


if(flag_for_filter_brand);
    $sql="SELECT DISTINCT * FROM products WHERE brand IN('$brand_str')";
else
     $sql="SELECT DISTINCT * FROM products;

MY PROBLEM: But this is also a big problem because my code become so large because there are two three where clauses as below

$sql="SELECT DISTINCT * FROM products WHERE brand IN('$brand_str') and Quantity IN ($var2) and type IN($var3)";

how to solve this in a optimal way?

Any suggestion or help is appreciated

Answer 1

Put each of your WHERE conditions in an array. Then test whether the array contains anything.

$wheres = array();

if(isset($_GET['brand']) && !empty($_GET['brand']))
    $brand=$_GET['brand'];  
    $brand_str = implode("' , '",$brand);  
    $wheres[] = "brand IN ('$brand_str')";
}
if(isset($_GET['quantity']) && !empty($_GET['quantity']))
    $quant=$_GET['quantity'];  
    $quant_str = implode("' , '",$quant);  
    $wheres[] = "Quantity IN ('$quant_str')";
}
// Repeat this for other conditions

if (!empty($wheres)) {
    $where_str = "WHERE " . implode(' AND ', $wheres);
} else {
    $where_str = "";
}
$sql = "SELECT DISTINCT * FROM Products $where_str";

If you have lots of conditions, you can put the names of the fields in an array, and then make the first part of this answer into a loop:

$fields = array('brand', 'quantity', 'type', ...);
foreach ($fields as $field) {
    if (!empty($_GET[$field])) {
        $field_str = implode("' , '", $_GET[$field]);
        $wheres[] = "$field IN ('$field_str')";
    }
}