How to create (or insert into MySQL) single order ID from cart function using foreach

Question

I am trying to develop a store and am stuck due to my poor knowledge in PHP, I read online and try to implement with minimal understanding.

When I insert the cart items into MySQL so that I can generate an order number, each insert creates multiple ids by way of auto increment. What I want is one ID for all the products passed in that query so that I can take it to checkout page where I take user details. I have a foreach loop, which helps me display products perfectly, but when I try using insert, it creates separate ids.

What I need is single order ID for each query, where temporary cart items can be passed onto checkout page and into MySQL

Following is my code:

<?php
session_start();
require_once("inc/connect.php");


?>


     <?php
if(isset($_SESSION["cart_item"])){
    $item_total = 0;
?>  

<?php


    $data = ''; 
 foreach ($_SESSION["cart_item"] as $item){


                $product_id = $item["id"];
                $quantity_per_product = $item["quantity"];
$data .= "(NULL, '$product_id', '$quantity_per_product', NULL),"; 

} 
$data = rtrim($data, ','); 
$ad="INSERT INTO `orders`(`order_id` ,`product_id` ,`quantity_per_product` ,`modified`)
VALUES $data;";


$q2 = mysqli_query($connection, $ad);

if (!$q2)
  {
  echo ('Q2 failed: ' . mysql_error());
  }
 else {echo 'success Q2';

echo "query: "; print_r($ad);}




$order_id=mysqli_insert_id($connection);   
echo "<script language='javascript'>
window.location = 'checkout.php?checkout=$order_id';
</script>";

?>

halfer · Answer 1 · 2017-10-01T18:18:23.567

MySQL is behaving correctly here - you want each of these items to have a separately generated primary key. However, I think the problem is your database design.

You're inserting shopping cart items into an order table, but shopping cart items are not orders. They are order items.

Thus, you need another table. I would rename your current table order to order_item, and continue using that structure as you do now. However, prior to this, create a single row in an order table, which will auto-generate a primary key for your whole order.

Finally, when you insert into order_item, you can insert the primary key order.id as a foreign key, to link items to the order they are part of. This models the real-world situation of one order having zero to many order items, and one order item having exactly one order.

Aside: your INSERT query is being built using concatenation, which may expose you to SQL injection vulnerabilities. Use parameter binding instead. Luckily, you are using the MySQLi database connector, which supports input parameter binding out of the box.

Thanks for your answer, am trying to absorb as much as I can and try to figure a way out. I understood what you explained, but I dont really have an idea about the HOW factor, how will the php code change. — Kedar Patwa, Oct 05 '17 at 18:25
@KedarPatwa: break the problem down into pieces - some of this is in my answer already. Rename your `order` table to `order_item`, then check it still works as it does now. Then create a new `order` table, and check that code works. Then do an insert into that when you checkout (regardless of the number of items in your basket). Obtain the PK and insert/update that in all of your `order_item` rows in this basket. One step at a time `:-)`. — halfer, Oct 05 '17 at 18:28

How to create (or insert into MySQL) single order ID from cart function using foreach

1 Answers1