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
– Raptor, Commented Jan 16, 2015 at 6:10
I know that much, that is why I am writing this post. I need to know how to fix it! — user982853
– user982853, Commented Jan 16, 2015 at 6:12
We need to know how is your SQL statement before we can help. — Raptor
– Raptor, Commented Jan 16, 2015 at 6:12

Raptor · Accepted Answer · 2015-01-16 06:18:04Z

16

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.

answered Jan 16, 2015 at 6:18

Raptor

54.4k47 gold badges254 silver badges405 bronze badges

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

Comments

user3434233 · Accepted Answer · 2017-03-18 11:36:43Z

1

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.

answered Mar 18, 2017 at 11:36

user3434233

197 bronze badges

Collectives™ on Stack Overflow

PDO bindParam() PHP Foreach Loop

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related