php - 使用 foreach 2 级数组循环进行多次插入

Question

我使用foreach进行多次插入(有两级循环，因为每个产品可能有很多属性)。建议使用 stmt，但不确定如何执行这些操作。

我知道从表单中检索数据的方法。并且我需要帮助将数据放入数据库。

Array ( [1] => Array ( 
[category] => 1 
[code] => NFK50889922
[price] => 15.00 [name] => Pendants 
[description] => Gold pendants covered with 400k diamond 
[thumbnail] => 131120091585.jpg 

//second level array for attribute
[attcode] => Array ( [0] => [1] => [2] => ) 
[color] => Array ( [0] => [1] => [2] => ) 
[size] => Array ( [0] => [1] => [2] => ) 
[stock] => Array ( [0] => [1] => [2] => ) ) )

代码:

    // Check for a form submiss
    if ($_SERVER['REQUEST_METHOD'] == 'POST') { 

    $product=$_POST['product'];


    foreach($product as $productcount){

    $q = 'INSERT INTO product(id,code,name,description,category_id,price,icon) VALUES (NULL,'.$productcount['code'].',"'.$productcount['name'].'",'.$productcount['description'].',"'.$productcount['category'].',"'.$productcount['price'].',"'.$productcount['thumbnail'].')';

    mysqli_query($dbc, $q);//insertion of general information of current product 


    //insertion of many attribute of current product
    $sql = 'INSERT INTO product_attribute (product_id,code,c_value,s_value,stock) VALUES (LAST_INSERT_ID(), ?, ?, ?, ?)';

            // Prepare the statement:
            $stmt = mysqli_prepare($dbc, $sql);



    // For debugging purposes:
        // if (!$stmt) echo mysqli_stmt_error($stmt);

        mysqli_stmt_bind_param($stmt,'sssi',$attribute_code,$color_value,$size_value,$stock_unit);

         foreach($productcount['code'] as $attcode){
            $attribute_code=$attcode;
            }

         foreach($productcount['color'] as $attcolor){
            $color_value=$attcolor;
            }

         foreach($productcount['size'] as $attsize){
            $size_value=$attsize;
            }

         foreach($productcount['stock'] as $attstock){
            $stock_unit=$attstock;
            }

         foreach($productcount['attcode'] as $attcode){ 
            $attcode;
            }

        // Execute the query:
        mysqli_stmt_execute($stmt);
        $stmt->close();
}

产品表:

id---code---name---description---categori_id---price

产品属性表:

id---product_id---code---color---size---stock

Answer 1

在 mysql 中，您可以一次插入多行:

INSERT INTO TableName( 
   foo_field, 
   bar_field 
) 
VALUES 
   ( foo1, bar1 ), 
   ( foo2, bar2 ),
   ( foo3, bar3 ),
   ( foo4, bar4 ) 

此方法的缺点是您无法使用准备好的语句，从而获得内置的注入(inject)保护的额外好处。

或者，您可以创建一个准备好的语句，然后在循环中使用参数执行它。这将是一种较慢的方法，但您不需要在插入数据之前手动清理数据。