CODEWITHSUNDEEP
phpmysqlpdo
jon
jon

Reputation: 1581

multiple PDO insert with single statement not working

I am trying to loop through $vars_array['selected_ids'] array and insert the selected_id and page_id. It is inserting 3 records (as expected), but each record is getting a selected_id of 7.. i.e. the last selected value inserted.

$vars_array=array();
$vars_array['page_id']=10;
$vars_array['selected_ids']=array(2,3,7);

$sql = "INSERT INTO template_values ( page_id, selected_id) VALUES";
$sqlPart = array_fill(0, count($vars_array['selected']), "(?, ?)");
$sql .=  implode(",",$sqlPart);
$stmt=$this->database->prepare($sql);

$i = 1;
foreach($vars_array['selected_ids'] as $selected_id) {
    $stmt -> bindParam($i++, $vars_array['page_id']);
    $stmt -> bindParam($i++, $selected_id);
}
$stmt -> execute();

Any ideas? regards J

Upvotes: 2

Views: 65

Answers (2)

jon
jon

Reputation: 1581

Alternate method:

    $i = 0;
    $new_array=array();
    foreach($vars_array['selected_ids'] as $selected_id) {
        $new_array[$i++]= $vars_array['page_id'];
        $new_array[$i++]= $selected_id;     
    }
    $stmt -> execute($new_array);

Upvotes: 0

MatsLindh
MatsLindh

Reputation: 52802

The reason is because you're using bindParam, and not bindValue. bindParam stores a reference to the variable you're binding, and uses the value of the variable at time of execution. That value is the same as the last iteration in your foreach.

Either use bindValue (which you usually should do anyway, since you're expecting it to use the value and not a reference - leading to subtle bugs like this) or send in an array to execute directly (which I usually prefer when generating a large placeholder query, since I prefer named placeholders when using bindValue).

Upvotes: 1

Related Questions