keeping track of online orders, session vs database ?

[tmyonline]

Guys: Recently I created an online order form to accept users' orders and the quantities of each.  I have done all of that.  Now, I'm asked to create a page to keep track of the quantity of each article ordered.  I was thinking about using session to store the values (quantities) of each article ordered.  However, as I remember, session can only memorize the stored values up until the browser closes.  If so, it will be useless.  Should I create a database for this purpose, with each field being an ordering variable ?  If so, the ordering quantities will be stored in the database and everytime someone places a new order, the quantities in the new order will add to the existing quantities in the database?  Will this be a solution you suggest ?  Thanks.

[php_tom]

You're right, sessions die when the browser is closed, besides you probably want to keep order info server-side not client-side.

 

I would make a table 'ordered_items' with fields like order number, item number, quantity.

Then say I am a customer and I buy (3 pairs of shoes), (1 toothbrush), (12 rubber duckies).

You could insert into the database the following:

| Order Number | Item Number | Quantity |

  2973684          12348793          3            <-- The shoes

  2973684          49378984          1            <-- The toothbrush

  2973684          29837023        12            <-- The rubber duckies

 

Then you can figure out how many rubber duckies have been ordered by doing SELECT SUM(Quantity) FROM ordered-items WHERE item_num=12348793

 

Hope that helps.

[Psycho]

Assuming people can order multiple products in a single order, then I would suggest using two tables. One for the order details: user, shipping info, etc. And one for the itemized order products - one record for each product in the order.

 

Table: orders

- orderID

- name

- address

- city

- state

- zip

 

Table: orders_products

- id

- orderID

- qty

- price

 

[roopurt18]

mjdamato has the right idea, although the choice to store the shipping information really depends on the rest of your database structure.  I just wanted to clear up one point of confusion you might be having.

 

Now, I'm asked to create a page to keep track of the quantity of each article ordered.

You don't need a table with any sort of structure like this:

ordered_product_quantities

- productID

- quantity

With that table, you'd store the product ID and how many times it had been ordered.  Every time it is ordered, you'd simply update the quantity if it was present in the table or insert a new entry if it wasn't.  But like I said, you don't need this table.

 

Using mjdamato's orders_products table, I don't see a productID column (it might be the equivalent of the id column he gave).  But let's assume the orders_products table had a productID column.  Even though you'd have many entries of orders for that product, you can still get a total of quantities ordered:

SELECT product_id, COUNT(qty) AS qtyOrdered FROM orders_products WHERE 1 GROUP BY product_id ORDER BY qtyOrdered

[tmyonline]

Thanks a lot.  You guys are very kind and helpful.  Thanks again!

[Psycho]

Using mjdamato's orders_products table, I don't see a productID column (it might be the equivalent of the id column he gave).  But let's assume the orders_products table had a productID column.  Even though you'd have many entries of orders for that product, you can still get a total of quantities ordered:

SELECT product_id, COUNT(qty) AS qtyOrdered FROM orders_products WHERE 1 GROUP BY product_id ORDER BY qtyOrdered

 

I don't think that's quite right. COUNT merely counts the number of records that were agregated via the GROUP BY parameter. So, if you had two orders for the same product with quantities of 20 and 30, the COUNT for that product would be 2.

 

This will give you the total quantity ordered by product (i.e. 50 in the example above)

SELECT product_id, SUM(qty) AS qtyOrdered FROM orders_products WHERE 1 GROUP BY product_id ORDER BY qtyOrdered

[roopurt18]

Oops, mjdamato is right.  I had a brain fart.