CODEWITHSUNDEEP
phpmysql
Marc Howard
Marc Howard

Reputation: 425

how to structure tables to have multiple items for one order number

I have a cart_array to hold items in the shopping cart, it holds the item_id, size and quantity. At the moment i am writing these into the table transactions with an order id which is auto increment, but it's creating a new row for each item, meaning the order id for one order may be 1,2,3 for 3 items. I'm looking for a way of having order no. 129 then having those items linked to that order somehow. Here is my php and sql statement

foreach ($_SESSION["cart_array"] as $each_item) {
    $item_id = $each_item["item_id"];
    $sql = mysql_query ("SELECT * FROM products WHERE id='$item_id' LIMIT 1");
    while ($row = mysql_fetch_array($sql)) {
        $price = $row["price"];
    }
    $sql = "INSERT INTO transactions (pid, item_id, quantity, price, size, date) VALUES ('$userID', '$each_item[item_id]', '$each_item[quantity]', '$price', '$each_item[size]', now())";
    mysql_query($sql);
}
?>

Upvotes: 0

Views: 3071

Answers (2)

Mark
Mark

Reputation: 2704

You should create two tables.

table 1: a table with your orders with a unique key like orderId (autoincrement)

table 2: a table with the items linked to an orderId

In this way you can create a new order by inserting a row in table 1. Get the orderId from the row you just created, then loop over your cart_array and insert rows in the second table with one row for each item. Reference each item to the orderId.

Upvotes: 5

m02ph3u5
m02ph3u5

Reputation: 3161

Record assignments in a separate table (use foreign keys if you want).

New table stores a reference to the actual item (its id) and a reference to the transaction (its id) (and quantity).

Upvotes: 1

Related Questions