Binding params for PDO statement inside a loop

Question

I'm trying to bind parametres for SQL query inside a loop:

$db = new PDO('mysql:dbname=test;host=localhost', 'test', '');   $stmt = $db->prepare('INSERT INTO entries VALUES (NULL, ?, ?, ?, NULL)');  $title = 'some titile'; $post = 'some text'; $date = '2010-whatever';    $reindex = array(1 => $title, $post, $date); // indexed with 1 for bindParam  foreach ($reindex as $key => $value) {       $stmt->bindParam($key, $value);       echo "$key</br>$value</br>";  //will output: 1</br>some titile</br>2</br>some text</br>3</br>2010-whatever</br> } 

The code above inserts in database in all 3 fields 2010-whatever.

This one works fine:

$stmt->bindParam(1, $title); $stmt->bindParam(2, $post); $stmt->bindParam(3, $date); 

So, my question is why the code in the foreach-loop fails and inserts wrong data in the fields?

Answer 1

The problem is that bindParam requires a reference. It binds the variable to the statement, not the value. Since the variable in a foreach loop is unset at the end of each iteration, you can't use the code in the question.

You can do the following, using a reference in the foreach:

foreach ($reindex as $key => &$value) {  //pass $value as a reference to the array item     $stmt->bindParam($key, $value);  // bind the variable to the statement } 

Or you could do this, using bindValue:

foreach ($reindex as $key => $value) {     $stmt->bindValue($key, $value);  // bind the value to the statement }