SQL insert multiple rows, foreach

Question

I'm trying to insert multiple rows into my DB dependening how many iterations of an array are returned.

The insert is working, but doenst insert more than 1 row, regardless of whats in the array.

function createOrder(){

  $CustomerID = $_SESSION['CustomerID'];
  $BasketID = $_SESSION['BasketID'];

  // create a new entry with an OrderID
  $orders = new Basket;
  $orders->storeFormValues( $_POST );
  // Collect the OrderID returned from insertOrder(); and insert into 'Orders'
  $OrderID = $orders->insertOrder($CustomerID);

  // Populate OrderDetails with items in users Basket.
  $data = Basket::getBasket($BasketID);
  $results['basket'] = $data['results'];

  // Insert the order details into the orderDetails DB.
  $orders->insertOrderDetails($OrderID, $BasketID, $CustomerID, $results); 
};

and the loop:

public static function insertOrderDetails($OrderID, $BasketID, $CustomerID, $results){
   $conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );

    // for each row insert into the DB
    foreach ( $results['basket'] as $row ) {
      $sql = "INSERT INTO OrderProducts (OrderID, ProductName, Price, Quantity) 
              VALUES (:OrderID, :ProductName, :Price, :Quantity)";

      $st = $conn->prepare( $sql );
      $st->bindValue( ":OrderID", $OrderID, PDO::PARAM_INT );
      $st->bindValue( ":ProductName", $row->ProductName, PDO::PARAM_STR );
      $st->bindValue( ":Price", $row->Price, PDO::PARAM_INT );
      $st->bindValue( ":Quantity", $row->Quantity, PDO::PARAM_STR );
      $st->execute();

   }
    $conn = null;
}

And the array, $results looks like;

array(1) {
  ["basket"]=>
  array(2) {
    [0]=>
    object(Basket)#3 (10) {
      ["OrderID"]=>
      NULL
      ["CustomerID"]=>
      NULL
      ["OrderItemID"]=>
      NULL
      ["ProductID"]=>
      string(1) "9"
      ["Quantity"]=>
      string(1) "4"
      ["ProductName"]=>
      string(12) "Cheese Bagel"
      ["Price"]=>
      string(1) "1"
      ["NameType"]=>
      string(5) "Bagel"
      ["BasketProductID"]=>
      string(2) "25"
      ["BasketID"]=>
      string(1) "3"
    }
    [1]=>
    object(Basket)#5 (10) {
      ["OrderID"]=>
      NULL
      ["CustomerID"]=>
      NULL
      ["OrderItemID"]=>
      NULL
      ["ProductID"]=>
      string(1) "2"
      ["Quantity"]=>
      string(1) "1"
      ["ProductName"]=>
      string(15) "The British BLT"
      ["Price"]=>
      string(1) "3"
      ["NameType"]=>
      string(5) "Bagel"
      ["BasketProductID"]=>
      string(2) "26"
      ["BasketID"]=>
      string(1) "3"
    }
  }
}

Any suggestions greatly apprecaited!

PDO by default uses "return boolean false" to signify failure. You're not checking for failure in any of your DB code, which means you're simply assuming nothing could ever fail. — Marc B, Feb 09 '15 at 14:45
@Legionar, In `OrderProducts`, OrderProductID is primary and Unique, `Orders`, OrderID is primary. @MarcB thanks, I plan to create some conidtions to check once the main part of the script is working. — atoms, Feb 09 '15 at 14:47

Dmitry Kortelev Flip · Answer 1 · 2015-02-09T15:15:04.940

1

May be just try this variant for INSERT query:

insert into tablename (id,blabla) values(1,'werwer'),(2,'wqewqe'),(3,'qwewe');

For example:

$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );

foreach ( $results['basket'] as $key => $row ) {
    $sql = "INSERT INTO OrderProducts (OrderID, ProductName, Price, Quantity) VALUES ";
    $sql .= "(:OrderID" . $key . ", :ProductName" . $key . ", :Price" . $key . ", :Quantity" . $key . "),";
}

$sql = substr($sql, 0, -1);
$st = $conn->prepare( $sql );

foreach ( $results['basket'] as $key => $row ) {
    $st->bindValue( ":OrderID" . $key, $OrderID, PDO::PARAM_INT );
    $st->bindValue( ":ProductName" . $key, $row->ProductName, PDO::PARAM_STR );
    $st->bindValue( ":Price" . $key, $row->Price, PDO::PARAM_INT );
    $st->bindValue( ":Quantity" . $key, $row->Quantity, PDO::PARAM_STR );
}
$st->execute();

Two foreach but one insert query to database.

edited Feb 09 '15 at 15:15

answered Feb 09 '15 at 14:51

Dmitry Kortelev Flip

11
2

thanks Dmitry, I'm unsure how I would populate each set of brackets individually? – atoms Feb 09 '15 at 14:53
I'm not tested it, but I think then should work this variant (edited my answer). – Dmitry Kortelev Flip Feb 09 '15 at 15:11
Thanks Dmitry, once I added AI to the DB your soloution works. Would you mind explaining if there are any benefits over my script? Thanks :) – atoms Feb 11 '15 at 09:46
1

Sure. I think my variant will be insert faster, because single query to database vs multiple query. But if the number of records is small then you will not see the difference. Some question about this: http://stackoverflow.com/questions/26456219/what-is-the-best-way-to-insert-multiple-rows-in-php-pdo-mysql – Dmitry Kortelev Flip Feb 11 '15 at 10:36
makes sense, if I have time will update to include as the prefered method. Thank you! – atoms Feb 11 '15 at 10:42

score 1 · Accepted Answer · answered Feb 09 '15 at 14:58

1

My primary key in the DB wasnt set to Auto Increment. Changing this solved the problem. Will remove once allowed. Thanks for your help

answered Feb 09 '15 at 14:58

atoms

2,993
2
22
43

1

yes, I wrote you comment to you question, whats yours primary and unique keys? :-) – Legionar Feb 18 '15 at 14:28

Daniel Persson · Answer 3 · 2015-02-09T14:49:15.773

0

Read http://php.net/manual/en/pdostatement.execute.php and it seems you might need to close the cursor before executing next statement.

Note: Note: Some drivers require to close cursor before executing next statement.

Another note you probably don't have to create the statement in each iteration.

edited Feb 09 '15 at 14:49

answered Feb 09 '15 at 14:42

Daniel Persson

602
7
17

Uh, where is mysqli in any of the OP's code? It's using PDO – Marc B Feb 09 '15 at 14:45
Sorry Marc, I've missed that detail :) – Daniel Persson Feb 09 '15 at 14:49

SQL insert multiple rows, foreach

3 Answers3