Multi-insertion with foreach 2 level array loop

Question

Am doing a multiple insertion using foreach (there are two level of looping because each product may have many attribute). Have suggested to use stmt, but not sure how to do these.

I know the way of retrieving data from form.And i need help on putting the data into database.

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] => ) ) )

Code:

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

table for prodcut :

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

Table for product attribute:

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

Answer 1

In mysql you can insert multiple rows at once:

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

Downside to this method is that you are unable to use prepared statements and thus gain the added benefit of built-in protection against injections.

Alternatively, you could create a prepared statement and then execute it with parameters in a loop. This will be a slower way, but you wont need to manually sanitize the data before inserting it.