Parameter binding for a PDO statement inside a loop

Question

I am trying to bind parameters for an 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 above code inserts all 3 fields of 2010-whatever into the database.

This works well:

 $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 the wrong data into the fields?

Answer 1

The problem is that bindParam requires a link. It binds a variable to an expression, not a value. Since the variable in the foreach not set at the end of each iteration, you cannot use the code in the question.

You can do the following using the link in 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 it using bindValue :

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