CODEWITHSUNDEEP
phpmysqlarrayspdo
Erasus
Erasus

Reputation: 706

PHP PDO proper way to bind values from array

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.

Upvotes: 0

Views: 220

Answers (2)

Elixir Techne
Elixir Techne

Reputation: 1856

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);

Upvotes: 1

the_critic
the_critic

Reputation: 12820

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.

Upvotes: 1

Related Questions