query with php array in mysql

Question

I want to fetch contents with multiple filters, right now there's only one. For Example:

SELECT * FROM Table1 WHERE status=true AND category = 'Camera' AND model = 'Samsung' AND type = 'New'

I want to create an array for it. But as I'm a newbie in this one not getting a lead.

function getAllRequests($filter){
if(empty($filter)){
    $addfilter = '';    
    }else{
    $addfilter = 'AND cat_id=' . $filter;
    }
}

$sql = 'SELECT * FROM Table1 WHERE status=true' . $filter;

Any help will be appreciated.

Answer 1

This will get you closer to the solution, though it will not replace the cat_id in the query, which will certainly be wrong - though impossible to do too much more without the array structure:

function getAllRequests($filter)
{
    $addfilter="";
    if(!empty($filter))
    {
        foreach($filter as $val)
        {
            $addfilter. = ' AND cat_id=' . $val .'\'';
        }
    }
    return $addFilter;
}

$myFilters=getAllRequests($filter);
$sql = 'SELECT * FROM Table1 WHERE status=true' . $myFilters;

On the other hand, if your array is strucutred in a way like this:

array{ category => camera, model => samsung); // etc

you could use the following:

function getAllRequests($filter)
{
    $addfilter="";
    if(!empty($filter))
    {
        foreach($filter as $key => $val)
        {
            $addfilter. = " AND `$key` = '$val'";
        }
    }
    return $addFilter;
}

$myFilters=getAllRequests($filter);
$sql = 'SELECT * FROM Table1 WHERE status=true' . $myFilters;

Edit: You can loop through all the filters in the following manner:

function getAllRequests()
{
    $addfilter="";
    if(!empty($_REQUEST))
    {
    foreach($_REQUEST as $key => $val)
    {
        $addfilter. = " AND `$key` = '$val'";
    }
    }
    return $addFilter;
}

$myFilters=getAllRequests();
$sql = 'SELECT * FROM Table1 WHERE status=true' . $myFilters;

You don't need to pass the $_REQUEST (which will work for both GET and POST) as it already a superglobal.

Answer 2

function getAllRequests($filter){
if(empty($filter)){
$addfilter = '';    
}else{
$addfilter = 'AND cat_id=' . $filter;
 }
}

$sql = 'SELECT * FROM Table1 WHERE status=true' . $addfilter;

Answer 3

You can use another approach, which is using optional parameters and it will make your WHERE clause dynamic as you want. In this approach you pass all parameters' values directly to the sql query which should look like so:

SELECT * 
FROM Table1 
WHERE 1 = 1
  AND (@status   IS NULL OR status   = @statusParam)
  AND (@category IS NULL OR category = @categoryParam)
  AND (@model    IS NULL OR model    = @modelParam)
  AND (@type     IS NULL OR type     = @typeParam)

Then If any of the parameters @statusParam, @categoryParam, @modelParam or @typeParam passed to the query with NULL values, then the comparison with the column holding that value will be ignored. I used the predicate 1 = 1, in case all the values passed to the query with all NULL values in the case all the WHERE clause will be ignored as it won't presented, since WHERE 1 = 1 always true and it will be like SELECT * FROM Table1.

Answer 4

use this

function getAllRequests($filter){
    if(empty($filter)){
      $addfilter = '';    
     }else{
      $addfilter .= 'AND cat_id=' . $filter;
    }
  return $addfilter;
}

$sql = 'SELECT * FROM Table1 WHERE status=true' . getAllRequests($filter);

Answer 5

When you are sending array make sure it has indexes

 $conditions = array('category' => 'Camera', 'model' => 'Samsung' , 'type' => 'New')

Now loop through it. in your else condition

foreach($conditions as $key =>$value){

    $addfilter .= 'AND ' . $key . ' = ' . $value;

}