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

Question

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!

Gyandeep Sharma · Accepted Answer · 2017-11-20 09:49:44Z

1

You can this run query without foreach loop as follows

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

answered Nov 20, 2017 at 9:49

Gyandeep Sharma

2,3274 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler Watts Over a year ago

Thank you very much. All answers where extremely helpful!

YaniMan · Accepted Answer · 2017-11-20 09:50:48Z

0

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.

answered Nov 20, 2017 at 9:50

YaniMan

3032 silver badges14 bronze badges

Comments

Heru Prasetyo Utomo · Accepted Answer · 2017-11-20 09:53:12Z

0

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

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

answered Nov 20, 2017 at 9:53

Heru Prasetyo Utomo

1292 silver badges6 bronze badges

Comments

apokryfos · Accepted Answer · 2017-11-20 09:55:42Z

0

Problems:

You're using vendor = item1 OR vendor = item2 OR (notice the hanging OR)
Using IN achieves the same result
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

answered Nov 20, 2017 at 9:55

apokryfos

40.9k11 gold badges85 silver badges128 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related