php - 使用一个表单插入多个数据库行

Question

我正在尝试将多行插入数据库，每行都具有相同的 user_id 和 order_id，但具有不同的product_id，我收到的错误是:

SQLSTATE[23000]:违反完整性约束:1048 列“product_id”不能为空

按钮功能:

if(isset($_POST['confirm_order']))
{
        $product_id = array($_POST['productid']);
        $user_id    = $_POST['userid'];
        $user->confirm_order($product_id,$user_id);
}

表格:

<?php 
    $stmt = $DB_con->prepare("SELECT * FROM products as p INNER JOIN basket as b ON p.product_id=b.product_id WHERE user_id = :user_id"); 
    $stmt->bindParam(":user_id",$user_id);   
    $stmt->execute();
    $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach($row as $p)
        {
                echo '<form method="POST" enctype="multipart/form-data">
                <tr>
                <td><img id="img" class="img-fluid" src="images/',$p['product_image'],'" style="height:100px;width:100px;"></td>
                <td>',$p['product_name'],'</td>
                <td>£',$p['product_price'],'</td>
                <td>
                <input type="hidden" id="productid" name="productid[]" value="',array($p['product_id']),'">
                <input type="hidden" id="userid" name="userid" value="',$user_id,'">
                </td>
                </tr>';
             }  
            ?>
                <button type="submit" class="btn btn-primary" name="confirm_order"> Confirm Order</button>
                </form>

功能:

    public function confirm_order($product_id,$user_id)
{
   try
   {
                $order_id = substr(str_shuffle(MD5(microtime())), 0, 10);
                $stmt1 = $this->db->prepare("INSERT INTO orders (order_id, product_id, user_id) VALUES (:order_id, :product_id, :user_id)");
                $stmt1->bindparam(":order_id", $order_id);
                $stmt1->bindparam(":product_id", $product_id[]);
                $stmt1->bindparam(":user_id", $user_id);        
                $stmt1->execute();

                $stmt2 = $this->db->prepare("DELETE * FROM basket WHERE product_id = :product_id AND user_id = :user_id");
                $stmt2->bindparam(":product_id", $product_id[]);
                $stmt2->bindparam(":user_id", $user_id);        
                $stmt2->execute();
   }
   catch(PDOException $e)
   {
       echo $e->getMessage();
   }    
}

Answer 1

您需要在 confirm_order() 函数中循环遍历 $product_id 数组，或者更改查询以接受可变数量的产品 ID。

就目前情况而言，您没有在 bindparam() 调用中提供有效的数组索引或元素，因此出现 mysql 错误。