SQL 插入多行，foreach - SQL insert multiple rows, foreach

我正在尝试将多个rows insert到我的数据库中，具体取决于返回数组的迭代次数。

插入

工作正常，但无论数组中的内容如何，都不会插入超过 1 行。

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); 
};

和循环：

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;
}

而阵列，$results看起来像;

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"
    }
  }
}

任何建议都非常感谢！

我在数据库中的主键未设置为自动增量。改变这一点解决了问题。一旦允许，将删除。感谢您的帮助

可能只是尝试这个变体进行插入查询：

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

例如：

$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();

两个，但一个将查询插入数据库。

http://php.net/manual/en/pdostatement.execute.php 读取，似乎您可能需要在执行下一条语句之前关闭光标。

注意：注意：某些驱动程序需要在执行 next 语句之前关闭游标。

另一个注意事项是，您可能不必在每次迭代中创建语句。