CODEWITHSUNDEEP
phpmysqlarrays
Dan
Dan

Reputation: 73

query with php array in mysql

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.

Upvotes: 1

Views: 145

Answers (5)

Fluffeh
Fluffeh

Reputation: 33542

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.

Upvotes: 1

Manoj
Manoj

Reputation: 373

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

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

Upvotes: 1

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

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;

}

Upvotes: 0

Yogesh Suthar
Yogesh Suthar

Reputation: 30488

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);

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

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.

Upvotes: 0

Related Questions