CODEWITHSUNDEEP
phpsqlajaxforeachwhere-clause
Tyler Watts
Tyler Watts

Reputation: 3

PHP - add new WHERE clause to SQL statement within foreach loop

I've got a checkbox filter where multiple options can be 'checked'. Once they are checked they are stored in a JavaScript array and passed to the php file via ajax. From there, I'm trying to do a foreach loop to get each element in the array and add a new where condition to the SQL statement for each element, and run the query.

JavaScript

var vendors = [];

function filterResults($this)
{
  var vendor = $($this).attr('data-id');
  if($($this).prop('checked'))
  {
    var action = 'add';
    vendors.push(vendor);
  }
  else
  {
    var action = 'remove';
    var index = vendors.indexOf(vendor);
    if(index >= 0)
    {
        vendors.splice(index, 1);
    }
}

PHP Script that is run (filter-results.php)

if(is_array($_POST['vendors']))
{
   $collaterals = $vendor->updateResults($_POST['vendors']);
   var_dump($collaterals);

   foreach($collaterals as $col)
   {
     include '../parts/item.php';
   }
}

PHP Function containing foreach loop

public function updateResults($vendors)
{
    try
    {   
        $items = array();
        $sql = "SELECT * FROM collaterals WHERE ";
        foreach ($vendors as $ven)
        {
            echo $ven;
            $sql .= "vendor = ".$ven." OR ";
        }

        $stmt = $this->db->prepare($sql);
        $stmt->execute();

        while($row = $stmt->fetchObject())
        {
            $items[] = $row;
        }

        return $items;

    }
    catch(Exception $e)
    {
        $e->getMessage();
    }
}

The 'echo' within the PHP function is working, but the var_dump() is turning 'NULL' which means there is an error within the SQL statement somewhere.

Any help would be greatly appreciated. Thanks!

Upvotes: 0

Views: 1213

Answers (4)

apokryfos
apokryfos

Reputation: 40683

Problems:

  1. You're using vendor = item1 OR vendor = item2 OR (notice the hanging OR)
  2. Using IN achieves the same result
  3. You should be using prepared statements

Do this:

$items = array();
$sql = "SELECT * FROM collaterals WHERE vendor IN (".
     implode(",",array_fill(0, count($vendors), "?")) 
.")";
// $sql would look like e.g. SELECT * FROM collaterals WHERE vendor IN (?,?,?)
$stmt = $this->db->prepare($sql);
$stmt->execute($vendors);

Note: All $vendors will be treated as strings in the above code, if you need to bind them as a different type you should call bindParam or bindValue on each array entry

Upvotes: 0

Heru Prasetyo Utomo
Heru Prasetyo Utomo

Reputation: 129

it will produce "SELECT * FROM collaterals WHERE vendor = 1 OR vendor = 2 OR "

use "Select * FROM collaterals where vendor in(1,2,3)" instead,

Upvotes: 0

YaniMan
YaniMan

Reputation: 303

Your query looks like

SELECT * FROM collaterals WHERE vendor = x OR vendor = x OR

You must remove the last OR. Also i would suggest using the IN clause with implode() php function.

Upvotes: 0

Gyandeep Sharma
Gyandeep Sharma

Reputation: 2327

You can this run query without foreach loop as follows

 $sql = "SELECT * FROM collaterals WHERE vendor IN ('".implode("','",$vendors)."')";

Upvotes: 1

Related Questions