Insert multiple values via PHP PDO into MySQL database

Question

I want to insert 20 values at once via PHP Data Objects. At the moment I'm doing it this way, it seems very ugly:

    $stmt = $this->pdo->prepare("INSERT INTO <tablename> (`uid`, `mid`, `status`) 
                                VALUES  (:uid, :mid1, :status), (:uid, :mid2, :status), 
                                        (:uid, :mid3, :status), (:uid, :mid4, :status),
                                        (:uid, :mid5, :status), ... and so on ...");
    $stmt->bindParam(':uid', $this->uid);

    $random_mid = array_rand(range(1,20), 5);

    $ref = new Mooney($random_mid[0]);
    $myMid1 = $ref->getMid();
    $stmt->bindParam(':mid1', $myMid1);

    $ref = new Mooney($random_mid[1]);
    $myMid2 = $ref->getMid();
    $stmt->bindParam(':mid2', $myMid2);

    $ref = new Mooney($random_mid[2]);
    $myMid3 = $ref->getMid();
    $stmt->bindParam(':mid3', $myMid3);

    ... and so on ...

    $status = 'open'; // default starting value of status
    $stmt->bindParam(':status', $status);
    $stmt->execute();

Is there a better solution by using a for-loop or so? Hope you can provide a better solution / more beautiful solution. Thanks for your time and help!

EDIT 1: Now thanks to @Jurik the code works and look like this:

private function insertValuesAtOnce() {
    $stmt = $this->pdo->prepare('INSERT INTO `<tablename>` (`uid`, `mid`, `status`) VALUES  (:UID, :MID, :STATUS)');
    $random_mid = $this->UniqueRandomNumbersWithinRange(1, 20, 7);
    require_once('<some path>');
    $status = 'open';
    foreach($random_mid AS $val){
        $ref = new Mooney($val);
        $myMid = $ref->getMid();

        $stmt->execute(array(
            ':UID' => $this->uid,
            ':MID' => $myMid,
            ':STATUS' => $status
        ));
    }
}

But it seems to me, that this is not a "insert many rows at once" but more "insert a single row multiple times"

EDIT 2: Because it does not seem to be possible to insert all values at once, I will use the solution to insert them row by row, but wrapping it into a transaction! This improved the runtime from 0,3915548324585 sec/serialize to 0,074591159820557 sec/serialize.

private function insertValuesAtOnce() {
    $stmt = $this->pdo->prepare('INSERT INTO `<tablename>` (`uid`, `mid`, `status`) VALUES  (:UID, :MID, :STATUS)');
    $random_mid = $this->UniqueRandomNumbersWithinRange(1, 20, 7);
    require_once('<some path>');
    $status = 'open';

    // Beginn Transaction (ACID)     
    $this->pdo->beginTransaction();

    foreach($random_mid AS $val){
        $ref = new Mooney($val);
        $myMid = $ref->getMid();

        $stmt->execute(array(
            ':UID' => $this->uid,
            ':MID' => $myMid,
            ':STATUS' => $status
        ));
    }
    // End Transaction (ACID)
    $this->pdo->commit();
}

Question is why do you want insert them at once? Because your getMid() gets data from database that needs a data base before anything else inserts into db? — Jurik
– Jurik, Commented Dec 11, 2013 at 13:11
Multiple inserets at once would still be nice - stackoverflow.com/questions/10219980/… — m33x
– m33x, Commented Dec 11, 2013 at 14:30
Ack, sorry - copy&paste mistake, I forgott beginTransaction. Otherwise commit doesn't make sense. Don't forgett rollback on db error. So you won't have inconsistent data. Good job :) — Jurik
– Jurik, Commented Dec 11, 2013 at 15:48

Jurik · Accepted Answer · 2013-12-11 15:50:56Z

2

<?php  
  $db = new PDO('mysql:host=localhost;dbname=<SOMEDB>', '<USERNAME>', 'PASSWORD');
  $query = $db->prepare(
    'INSERT INTO `user_mooney` (`uid`, `mid`, `status`)
    VALUES  (:UID, :MID, :STATUS)'
  );

  foreach($random_mid AS $val){
    $ref = new Mooney($val);
    $myMid = $ref->getMid();

    $query->execute(array(
      ':UID' => $uid,
      ':MID' => $myMid,
      ':STATUS' => $status
    ));
  }

  $query->commit();

If commit throws an error you can call $query->rollback(); So you've a consist database.

Edit: took correct answer from question edit 2

<?php  
    private function insertValuesAtOnce() {
        $stmt = $this->pdo->prepare('INSERT INTO `<tablename>` (`uid`, `mid`, `status`) VALUES  (:UID, :MID, :STATUS)');
        $random_mid = $this->UniqueRandomNumbersWithinRange(1, 20, 7);
        require_once('<some path>');
        $status = 'open';

        // Beginn Transaction (ACID)     
        $this->pdo->beginTransaction();

        foreach($random_mid AS $val){
            $ref = new Mooney($val);
            $myMid = $ref->getMid();

            $stmt->execute(array(
                ':UID' => $this->uid,
                ':MID' => $myMid,
                ':STATUS' => $status
            ));
        }
        // End Transaction (ACID)
        $this->pdo->commit();
    }

edited Dec 11, 2013 at 15:50

answered Dec 11, 2013 at 10:13

Jurik

3,2641 gold badge34 silver badges52 bronze badges

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

4 Comments

m33x Over a year ago

That did the trick, thank you very much. As I understand the code, it does insert the values one after the other and therefore not at once as I asked for? Am I right? Thank you anyway very much!

Jurik Over a year ago

Well, it prepares the insert one by one and if you want, you could lock the database before you start this insert. Or you create first an array of all those arrays in $query->execute and then put this array into execute. So you've all values prepared, before you do the first insert.

Jurik Over a year ago

Updated answer - but I'm not sure if this is possible, try it and it will fit to your question.

m33x Over a year ago

Not working, we got a PHP Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

Collectives™ on Stack Overflow

Insert multiple values via PHP PDO into MySQL database

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related