CODEWITHSUNDEEP
phpmysqlloopspdo
user982853
user982853

Reputation: 2488

PDO bindParam() PHP Foreach Loop

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.

Upvotes: 4

Views: 7993

Answers (2)

user3434233
user3434233

Reputation: 19

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.

Upvotes: 1

Raptor
Raptor

Reputation: 54212

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.

Upvotes: 16

Related Questions