Inserting values in mysql from an array using PHP

Question

I am having a bit of struggle with this. Hope some can guide me a little.

I am trying to take the shopping cart basket products which are in mysql(and not sessions in this case) and move them to an "order-details" table at the checkout.

The array of product is created ok and output the keys and values properly but my problem is when I try to insert the array values into the other table.

Here is the code:

$cart_products = array(); 
$stmt = $conn->prepare("SELECT 
SUM(co.cart_quantity) AS quantity, 
p.product_id, 
p.product_name, 
SUM(p.product_price) AS price, 
p.short_description, 
SUM(p.product_weight) AS weight, 
p.vat 

FROM cart_orders AS co 
LEFT JOIN products AS p 
ON co.cart_product_id = p.product_id 
LEFT JOIN vat_rates AS vr 
ON p.vat = vr.vat_id 
WHERE cart_user_id = ? 
GROUP BY co.cart_product_id 
ORDER BY co.cart_product_id"); 


$stmt->bind_param('i', $user_id); 
$stmt->bind_result($quantity,$p_product_id,$p_product_name,$price,$p_short_description,$weight,$p_vat); 
$stmt->execute(); 
$stmt->store_result(); 
if($stmt->num_rows() > 0){ 
while ($stmt->fetch()) { 

$cart_products[] = array( 
    "product_id" => $p_product_id, 
    "product_name" => $p_product_name, 
    "product_quantity" => $quantity, 
    "product_price" => $price, 
    "vat_id" => $p_vat 
); 
}} 
$stmt->free_result(); 
$stmt->close(); 


$stmt = $conn->prepare("INSERT INTO order_details(product_id,product_name,product_quantity,product_price,vat_id)
VALUES (?,?,?,?,?)");
$stmt->bind_param('isiii', $cart_products['product_id'],$cart_products['product_name'],$cart_products['product_quantity'],$cart_products['product_price'],$cart_products['vat_id']);
$stmt->execute();
$stmt->close();

This is the output of my array $cart_products:

Array 
( 
    [0] => Array 
        ( 
            [product_id] => 5 
            [product_name] => Product A 
            [product_quantity] => 20 
            [product_price] => 2.50 
            [vat_id] => 2 
        ) 

    [1] => Array 
        ( 
            [product_id] => 7 
            [product_name] => Product A 
            [product_quantity] => 10 
            [product_price] => 2.50 
            [vat_id] => 1 
        ) 

    [2] => Array 
        ( 
            [product_id] => 9 
            [product_name] => Product A44544 
            [product_quantity] => 3 
            [product_price] => 2.50 
            [vat_id] => 2 
        ) 

)

Thank you in advance for your help!

perhaps this issue can help you http://stackoverflow.com/questions/10054633/insert-array-into-mysql-database-with-php — Deniz Aktürk, Oct 27 '16 at 09:08
BennyBoy has error in his code. In last `bind_param`. I can't see any thing like this in issue added by Deniz Aktürk or in duplicate. — Arnial, Oct 27 '16 at 09:21
@BennyBoy you can directly insert your values into the other table by using `INSERT INTO... SELECT`. http://stackoverflow.com/a/37741763/3595565 If you don't have to validate your product data you are selecting in php this would be a good way to handle your case — Philipp, Oct 27 '16 at 09:22
Could you please explain where am I mixing syntax please? I am using mysqli prepared statements — , Oct 27 '16 at 09:34
I have edited my first post, I believe this would be better. — , Oct 27 '16 at 09:37
@Philipp, Thanks Phillip for the tip, I am trying to understand that synthax of the example you showed me. Question for you, could a transaction still be reversed if the select insert failed at some point? As I have multiple queries and would like to place them all into a single transaction. thank you. — , Oct 27 '16 at 09:41
I am not sure if there is a rollback when the insert fails for some dataset. You could start the transaction before the insert and check afterwards, then rollback the whole operation. But if you want to handle each dataset in a transaction the way you are trying is right — Philipp, Oct 27 '16 at 09:50

Philipp · Accepted Answer · 2016-10-27T09:58:01.850

You have to iterate over your $cart_products to access each product.

$cart_products = array();
if ($stmt->num_rows() > 0) {
    while ($stmt->fetch()) {
        $cart_products[] = array(
            "product_id" => $p_product_id,
            "product_name" => $p_product_name,
            "product_quantity" => $quantity,
            "product_price" => $price,
            "vat_id" => $p_vat
        );
    }
}
$stmt->free_result();
$stmt->close();


$stmt = $conn->prepare("INSERT INTO order_details(product_id,product_name,product_quantity,product_price,vat_id)
VALUES (?,?,?,?,?)");

foreach ($cart_products as $cart_product) {
    $stmt->bind_param('isiii', $cart_product['product_id'], $cart_product['product_name'], $cart_product['product_quantity'], $cart_product['product_price'], $cart_product['vat_id']);
    $stmt->execute();
}

Thank you so much, yes! It makes sense.......got it now, I just tried it and it works great! I really appreciate the help of everyone here. — , Oct 27 '16 at 10:26

Inserting values in mysql from an array using PHP

1 Answers1