无法插入Select语句与PHP的MySQL准备语句

Question

任何人都可以帮助我的MySQL查询。我试图插入多行使用插入选择语句。

if ($insert_stmt = $mysqli->prepare("INSERT INTO order_processing_info (order_id, cart_item_id, item_id, price, quantity) VALUES (?, (SELECT s.cart_item_id, i.item_id, i.price, s.quantity FROM inventory_info AS i, cart_info AS s WHERE i.item_id=s.item_id AND s.user_id = ?))")) { 
        $insert_stmt->bind_param('ss', $order_id, $user_id); 

我认为这个问题是因为我想要在这里插入相同的order_id，虽然我不确定。

以前我试图在select语句块中运行失败。

if ($select_stmt = $mysqli->prepare("SELECT i.item_id, i.price, s.quantity, s.cart_item_id FROM inventory_info AS i, shopping_cart_info AS s WHERE i.item_id=s.item_id AND s.user_id = ?")) { 
     $select_stmt->bind_param("s", $user_id); 
     $select_stmt->execute(); 
     $select_stmt->bind_result($item_id, $price, $quantity, $cart_item_id); 

     while ($select_stmt->fetch()) { 

      if ($insert_stmt = $mysqli->prepare("INSERT INTO order_processing_info (order_id, cart_item_id, item_id, price, quantity) VALUES (?, ?, ?, ?, ?)")) { 
       $insert_stmt->bind_param('sssss', $order_id, $cart_item_id, $item_id, $price, $quantity); 
       // Execute the prepared query. 
       if (!$insert_stmt->execute()) { 
        header('Location: ../error.php?err=INSERT failure: INSERT'); 
        exit(); 
       } 
      } 
     } 
     $select_stmt->close(); 
    } 

我已经看着它在计算器很多，但我想我不会在数据库中的查询精通，所以我希望如果有人能帮助我与此有关。

Answer 1

使用“INSERT INTO SELECT”查询（http://www.w3schools.com/sql/sql_insert_into_select.asp），只需要绑定一个参数，然后是用户标识。它还将节省循环中的查询开销