PHP PDO proper way to bind values from array

Question

I'm trying to get better at using PDO, I have this code:

 $answers_count = count($answers);

 $save_answers = $conn->prepare("INSERT INTO answers (answer, is_correct, question_id) VALUES (:answer, :is_correct, :question_id)");


 for($i = 0; $i < $answers_count; $i++) {
    $save_answers->bindParam(':answer', $answers[$i]);
    $save_answers->bindParam(':is_correct', $answers_state[$i]);
    $save_answers->bindParam(':question_id', $last_insert_id);
    $save_answers->execute();
 }

This code works for me well, but I have read that I should call execute() method just once, if I understood it correctly, I have to prepare sql statement once and execute it after I bind params? If I use execute() method for inserting one new record at a time it works, but if place $save_answers->execute(); statement outside of for loop only one INSERT query will be executed.

am I doing something wrong here, is there other easier way to bind values from the array where each time the number of array elements can be different.

Thank you in advance for the information you can provide me.

You prepare a query once, then you execute that query as many times as you like, placing new value into the query. __Just like you are doing__ — RiggsFolly, Dec 23 '15 at 15:29

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

but if place $save_answers->execute(); statement outside of for loop only one INSERT query will be executed.

This is because if you place the execute statement outside of your loop it will only execute the query once for the values bound from the last iteration of the for loop. Therefore your current code is correct and rebinding and re-executing the query should be the way to go.

The query needs to bind the values from each iteration (each answer has different values and thus, each insertion has different insertion values). Obviously you need to re-bind the values from each answer, so doing it once will not cut it for you.

If you don't want to execute it via a for loop, you can try batch insertion:

PDO Prepared Inserts multiple rows in single query

This will allow you to do the insertion of multiple rows in one request to the database, which might be what you are looking for.

edited May 23 '17 at 12:07

Community

1
1

answered Dec 23 '15 at 15:36

the_critic

12,720
19
67
115

Thanks for your fast response, at first I somehow misunderstood the idea of preparing query once and executing it as many times as needed with different params. So if I understand it correctly I don't need to put $conn->query(..'SQL STATEMENT..'); inside for loop. And thanks for try batch insertion method as an alternative way. – Erasus Dec 23 '15 at 15:49
1

@Erasus That is correct, the `$conn->query` call can stay outside of the `for` loop, as the insertion SQL statement does not change, you merely change the parameters bound to the query. So this way it will work just fine. – the_critic Dec 23 '15 at 15:52

score 1 · Answer 2 · answered Dec 23 '15 at 16:03

prepare query first and then execute

$answers_count = count($answers);
$writeArguments = array();
$writeQuery="insert into $tableName (answer, is_correct, question_id) values ";
for($i = 0; $i < $answers_count; $i++) {
    if (i > 0) {
            $writeQuery .= ',';
    }
    $writeQuery .= '(?,?,?)';
    array_push($writeArguments, $answers[$i], $answers_state[$i], $last_insert_id);
}
$save_answers = $conn->prepare($writeQuery);
$save_answers->execute($writeArguments);

PHP PDO proper way to bind values from array

2 Answers2