Only the last row of shoping cart content is inserting to the database instead of all the contents o-CodePudding

I am developing the checkout system of a shopping cart and I wanted to insert the details of the shopping cart in an order_details table in the database, using prepared statement, it is however inserting only the last row instead of inserting all the rows into the database.

//I used the block of code below to extract the cart contents, I left the curly brackets open so that I can accommodate the next block of code

```php
<?php
 //initialize total
 $total = 0;
 if(!empty($_SESSION['cart_contents'])){
                        
 //create array of initail qty which is 1
 $index = 0;
 if(!isset($_SESSION['qty_array'])){
   $_SESSION['qty_array'] = array_fill(0, count($_SESSION['cart_contents']), 1);
 }
   $sql = "SELECT * FROM products WHERE id IN (".implode(',',$_SESSION['cart_contents']).")";
   $query = $conn->query($sql);
    while($row = $query->fetch_assoc()){
 $product_id = $row['Product_id']; 
$product_name = $row['product_name'];  
$product_price = $row['discount_price'];
$qty = $_SESSION['qty_array'][$index];                                                                                                     
?>

/*this is the code that is inserting only the last data into database and I am seeking assistance on how to make it insert all the contents of the cart*
    <?php
    if(!empty($_SESSION['cart_contents']) && 
   count($_SESSION['cart_contents']) > 0){
      
      $subtotal = $product_price * $qty;
      $order_id  = $_SESSION['orderID'];
      for($i=0;$i<count($row['id']);$i  ){
foreach ($_SESSION["cart_contents"] as $i){
        $order_id = $order_id[$i];
        $product_id = $product_id[$i];
        $product_name = $product_name[$i];
        $product_price = $product_price[$i];
        $qty = $qty[$i];
        $subtotal = $subtotal[$i];}}}
        if($order_id!=='' && $product_id!=='' && $product_name!=='' && $product_price!=='' && $qty!=='' && $subtotal!=='' ){
            $stmt = $conn -> prepare('INSERT INTO order_details(order_id,product_id,product_name,product_price,qty,subtotal) VALUES (?,?,?,?,?,?)');
            
            $stmt -> bind_param('issiii', $order_id, $product_id, $product_name, $product_price, $qty, $subtotal);
$stmt -> execute();
        $stmt->close(); 
         //echo '<div  role="alert">Submitted Successfully</div>';
}
?>     

// this closes the opened curly bracket
<?php }?>

Any help on inserting more than the last row of the cart content will be greatly appreciated.

CodePudding user response：

You are using variables inside while loop here:

while($row = $query->fetch_assoc()){
  $product_id = $row['Product_id']; 
  $product_name = $row['product_name'];  
  $product_price = $row['discount_price'];
  $qty = $_SESSION['qty_array'][$index];

In every iteration the variables are getting updated and in the end they contain values of only the last row.

Try this instead:

$result = array();
while($row = $query->fetch_assoc()){
 $result['product_id'] = $row['Product_id']; 
 $result['product_name'] = $row['product_name'];  
 $result['product_price'] = $row['discount_price'];

and then use $result in the upcoming foreach loop.

CodePudding user response：

I was able to make all the rows to insert by posting the insert statement into another copy of the select statement like below

<?php
$sql = "SELECT * FROM products WHERE id IN (".implode(',',$_SESSION['cart_contents']).")";
$query = $conn->query($sql);
while($row = $query->fetch_assoc()){
$product_id = $row['Product_id']; 
$product_name = $row['product_name'];  
$product_price = $row['discount_price'];
$qty = $_SESSION['qty_array'][$index];    
$subtotal = $product_price * $qty;
$order_id  = $_SESSION['orderID'];
      
$stmt = $conn -> prepare('INSERT INTO order_details(order_id,product_id,product_name,product_price,qty,subtotal) VALUES (?,?,?,?,?,?)');
         if($order_id!=='' && $product_id!=='' && $product_name!=='' && $product_price!=='' && $qty!=='' && $subtotal!=='' ){   
            $stmt -> bind_param('issiii', $order_id, $product_id, $product_name, $product_price, $qty, $subtotal);
$stmt -> execute();
      for($i=0;$i<count($qty);$i  ){
foreach ($_SESSION["cart_contents"] as $i){
        $order_id = $order_id[$i];
        $product_id = $product_id[$i];
        $product_name = $product_name[$i];
        $product_price = $product_price[$i];
        $qty = $qty[$i];
        $subtotal = $subtotal[$i];}}}
   ?>