How do I insert an array of id into database MySQL?

Question

I'm trying to insert an array of IDs into a database table, but at the moment it only inserts 1 row when it's meant to do multiple rows. My array of IDs contains just (filmid) Does anyone know what the problem is?

$pm = "2";
$id = "2";             

if($stmt1->execute())
{
    $films=array();
    foreach($_SESSION['products'] as $key=>$product)
    {
        array_push($films, $product['filmid']);
        $f_id  = implode(",", $films);
        $stmt2 = $this->conn->prepare("INSERT INTO `filmPurchase` (`fpid`, `payid`, `filmid`, `shopid`, `custid`, `price`) VALUES (NULL, :id, :f_id, :pm, :pm, '8')");
        $stmt2->bindParam('pm',$pm);
        $stmt2->bindParam('id',$id);
        $stmt2->bindParam('f_id',$f_id);
        $stmt2->execute();
    }

}

I've tried to loop over the array with:

foreach($_SESSION['products'] as $key=>$product)
{
    var_dump($key);
    var_dump($product);
}

This is what outputted:

int(29) array(4) { ["qty"]=> int(1) ["filmtitle"]=> string(45) "The Lord of 
the Rings: The Return of the King" ["filmid"]=> string(2) "29" ["price"]=> 
float(6.99) }

You only have 1 item in your array. `$f_id` should also be bound when you insert it. — chris85, May 07 '18 at 12:47
3 issues I see immediately: you don't define `$pm` or `$id` in the code shown; if you're going to use prepare, you should bind all the variables (looking at `'$f_id'`), and you can't use the same bound parameter twice (`:pm, :pm`). You have to rename one of them. — aynber, May 07 '18 at 12:48
I've made those changes also I've added the defined ids. But still doing the same thing. — Ashley, May 07 '18 at 13:07
You may also want to rethink your query. Your loop is going to push `"The Lord of the Rings: The Return of the King"` on the first loop, `"The Lord of the Rings: The Return of the King", "The Lord of the Rings: The Two Towers",` on the second loop... — aynber, May 07 '18 at 13:10
Another thing you can do is log on each loop. Also check for [PDO errors](http://php.net/manual/en/pdo.error-handling.php) during the process. — aynber, May 07 '18 at 13:11
@Asenar [Only if `PDO::ATTR_EMULATE_PREPARES` is set to true](https://stackoverflow.com/questions/2432084/pdo-parameterized-query-reuse-named-placeholders). Since that part of the code is not shown, we can't be sure. It's better to try to not reuse parameters, though, just in case. — aynber, May 07 '18 at 13:13

Asenar · Answer 1 · 2018-05-07T13:09:39.427

0

If your placeholder is :id and :pm (as in prepare()) then you must use :id in bindParam()

See php documentation

edited May 07 '18 at 13:09

answered May 07 '18 at 12:49

Asenar

6,732
3
36
49

aren't they doing that now? I think you should go over their code again, and look at the comments under it. – Funk Forty Niner May 07 '18 at 13:03
You're right, in the mean time, someone commented about `f_id` and the author edited his code, so I removed this mention – Asenar May 07 '18 at 13:09

How do I insert an array of id into database MySQL?

1 Answers1