PHP PDO foreach issue

Question

So I have a php function that updates 2 columns in a database. It looks like this:

$fields = array("firstname" => "Joe", "lastname" = "Dunno");

$stmt = $connection->prepare("UPDATE users SET firstname = :firstname, lastname = :lastname WHERE user_id = :user_id");

foreach ($fields as $key => $value)
{
    $stmt->bindParam(":" . $key, $value);
} 

$stmt->bindParam(":user_id", $user_id);

However when i execute the statement for some reason it likes to update firstname and lastname both to Dunno instead of Joe and Dunno.

I tried echo'ing the $key and $value and it prints out correctly.

For some weird reason if i use this for loop it works correctly.

for ($fieldsKeys = array_keys($fields), $x = 0; $x < count($fields); $x++)
{
    $stmt->bindParam(":" . $fieldsKeys[$x], $fields[$fieldsKeys[$x]]);
}

Yury Fedorov · Accepted Answer · 2017-01-04 15:52:52Z

4

bindParam binds to a variable, that's why both fields are set to the same value (the last value of $value). You should use bindValue instead:

$stmt->bindValue(":" . $key, $value);

In your code, PDO remembers that it needs to use $value variable for :firstname and :lastname. At statement execution time the $value is Dunno, so both fields get this value. If you use bindValue, PDO does not remember the variable that was used, but its value, and this is what you need.

edited Jan 4, 2017 at 15:52

answered Jan 4, 2017 at 15:43

Yury Fedorov

15k6 gold badges56 silver badges70 bronze badges

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

2 Comments

Resantic Over a year ago

Thank you very much. I'll accept your answer as soon as it lets me.

v010dya Over a year ago

In the event that somebody will find it and will actually need to bindParam (for example if the values in the array can change before the statement execution) you can do $stmp->bindParam(':'.$key, $fields[$key]);

Collectives™ on Stack Overflow

PHP PDO foreach issue

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related