Reputation: 3093

SQL insert multiple rows, foreach

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!

Upvotes: 0

Answers (3)

Dmitry Kortelev Flip

Reputation: 11

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.

Upvotes: 1

atoms

Reputation: 3093

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

Upvotes: 1