insert multiple rows using PDO

Question

What's wrong with this code? i want to insert multiple rows with same movie id and different actor id. when i run this with $actors = array(1,2,3,4); (or any value) and $movid = 1, all the four rows are inserted as values (1, 4).

$create_query = "INSERT INTO `tableName`(`movId`, `actId`) VALUES";
$comma = "";
foreach ($actors as $key=>$value) {
    $create_query .= $comma . "(:movie" . $key . ", :actor" . $key . ")";
    $comma = ", ";
}
$query = $db->prepare($create_query);
foreach ($actors as $key=>$value) {
    echo $movid . ', ' . $value //correct output (1, 1), (1, 2) ...
    $query->bindParam(":movie" . $key, $movid, PDO::PARAM_INT);
    $query->bindParam(":actor" . $key, $value, PDO::PARAM_INT);
}
print_r($query);
//output is
/*INSERT INTO `tableName`(`movId`, `actId`) VALUES(:movie0, :actor0), (:movie1, :actor1), (:movie2, :actor2), (:movie3, :actor3)*/
if ($query->execute()) {
    $return = $query->rowCount();
    $query->closeCursor();
}

when i use separate queries for inserting, it works correctly.

Community · Accepted Answer · 2023-11-17 20:42:18Z

1

Use PDOStatement::bindValue() instead of PDOStatement::bindParam(). The latter will bind by reference, and since you're using a variable that changes with each loop iteration, you end up trying to bind everything to the same thing.

From the documentation for PDOStatement::bindParam():

Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

edited Nov 17, 2023 at 20:42

CommunityBot

11 silver badge

answered Aug 26, 2012 at 14:39

FtDRbwLXw6

29.1k16 gold badges74 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

unnikuttan Over a year ago

that's not me... anyway, thanks. but i could not figure out the error

FtDRbwLXw6 Over a year ago

@user1257030: I explained the error. Use bindValue() instead of bindParam(). Unless you have some other problem, this will fix it.

unnikuttan Over a year ago

Thank you very much... Your answer was right. The conclusion i reached is use $actors[$key] instead of $value in the second foeach This runs correctly...

FtDRbwLXw6 Over a year ago

@user1257030: You can use $value just fine if you do what I suggested. For example: $query->bindValue(':actor' . $key, $value, PDO::PARAM_INT). There is no need to use $actors[$key].

unnikuttan Over a year ago

In that way, i have to use the execute() within foreach. Then it will be executed many times

|

Collectives™ on Stack Overflow

insert multiple rows using PDO

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related