CODEWITHSUNDEEP
phpmysqlorders
samyb8
samyb8

Reputation: 2588

Orders insertion, multiple items in a same order

I have a shopping cart that stores itemIds and quantities per item in Sessions until we get to checkout.

After the client pays, I proceed to insert the information in my ORDERS table in a database.

My question is: How should I handle one order from a client that has multiple items? Multiple quantities is simple since my ORDERS table has a 'quantity' field. But how about multiple items? What is recommended? Should I INSERT different lines per item for one same order?

Currently I was having this:

$query = "INSERT INTO orders (itemId, quantity, clientPrice, firstName, lastName, email, phoneNumber, address, city, zip) 
VALUES (:itemId, :quantity, :clientPrice, :firstName, :lastName, :email, :phoneNumber, :address, :city, :zip)";

Upvotes: 1

Views: 2691

Answers (1)

sybear
sybear

Reputation: 7784

In my opinion you should create 2 tables with at least such fields:

orders

id | user_id | date

ordered_items - you must have full table of items as well, besides this table, containing all the info about your products. Price here is needed just because prices of your items can change and you must not change 'amount of money spent' on already created orders.

item_id | order_id | quantity | price

So you could then use joins and count everything you need - table joins will do all the work.

Upvotes: 2

Related Questions