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.

You used `foreach` incorrectly. You override the parameters in each loop. — Raptor, Jan 16 '15 at 06:10
I know that much, that is why I am writing this post. I need to know how to fix it! — user982853, Jan 16 '15 at 06:12
We need to know how is your SQL statement before we can help. — Raptor, Jan 16 '15 at 06:12

score 16 · Accepted Answer · answered Jan 16 '15 at 06:18

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.

score 1 · Answer 2 · answered Mar 18 '17 at 11:36

Similar to Raptor's response, you can simply put the array inside execute() like so:

$stmt =  $db->prepare($sql);
$stmt->execute(array(':City' => 'string1', ':State' => 'string2'));

This could end up very unreadble with a long enough statement though.

PDO bindParam() PHP Foreach Loop

2 Answers2