PDO bindParam() PHP Foreach Loop

Question

I have a foreach loop that I would like to execute a prepare statement pdo. I have read a few posts on making a reference but I do not know how to do it with my code:

$str = ":City, Aurora; :State, CO";
$wherestr = explode(";",$str);

$sql = "SELECT * FROM Organization WHERE City = :City AND State= :State";
$stmt = $db->prepare($sql);

foreach ($wherestr as $ws) {
 $ws = explode(",",$ws);
 $ws0 = trim($ws[0]);
 $ws1 = trim($ws[1]);
 $stmt->bindParam($ws0,$ws1);
}
$stmt->execute();

I have read here Binding params for PDO statement inside a loop that you can make this work by creating a reference with the & symbol but I am not using a Key=>Value like they are. Can anyone help me get this to loop through the Param and execute?

If i only have one WHERE (:var) then it works fine but if i have multiple WHERE filters than it seems to override the previous filter instead of executing all of the bindParams in the execute statement. I don't get any errors it just doesn't filter the results properly with more than one var.

Thank you.

Answer 1

You should use bindParam like this:

$sql = 'SELECT * FROM Organization WHERE City = :City AND State= :State';
$wherestr = array('string1', 'string2');
$stmt = $db->prepare($sql);
$stmt->bindParam(':City', $wherestr[0]);
$stmt->bindParam(':State', $wherestr[1]);
$stmt->execute();

if you insist using foreach, here is a way:

$stmt = $db->prepare($sql);
$params = array(':City' => 'string1', ':State' => 'string2');
foreach ($params as $key => &$val) {
    $stmt->bindParam($key, $val);
}
$stmt->execute();

note that we use pass by reference in the loop, which is a must.