php Shopping Cart and Quantity Help

[smallc28]

Hello this may sound a little confusing.

 

I recently made a fake website with a quantity stock what I mean by this is

let's I have 1 pair of jeans but I have the quantity or stock of 10 of the same pair of jeans.

 

Okay now when the customer goes to select this item and buy and (*they make up their mind whether to buy it or not*) <!-- shopper joke 

 

Anyways they buy themselves a pair or 2 or 3 or 4 or 10! I'd like to know how would I be able to set the quantity to subject itself from the shopping cart when each customer makes a purchase and lock as soon as the last pair of jeans are sold.

 

2 Image below just incase someone gets confused. 

 

Thanks a mill! 

 

 

 

 

 

[mac_gyver]

you need to first define the conditions you want to implement.

do you want to include the items that have been added into carts as reducing the available
quantity, even though someone may never actually complete the process to purchase the items in a cart, i.e. an abondandoned cart? this could lead to temporarily telling someone they cannot add an item/quantity to their cart, even though there could be enough actual quantity available.

or do you only include the items that have actually been ordered as reducing the available quantity? this
would allow the total amount of any item added into carts to exceed the actual available quantity. when
someone actually completes a purchase you might end up telling them that the item is not available, would
be back-ordered or that they need to remove or reduce the quantity of the item to complete the order process.
 
or do you code for both of these. use method one if you cannot obtain any more stock of an item (it was a one-time/one-off purchase) and use method two if you can obtain more stock of an item and will allow partial shipments/back-orders.
 

---

 
now to the actual determination of the quantity available.
 
first, you need an inventory table to keep track of things like inventory added, adjustments in inventory for things like store losses, fire, damage... this will have, as a minimum, columns for - id, item_id, quantity (signed int)
 
next, you need an order_details table (you also need an orders table, but that is not directly related to determining available quantity.) this table will hold the lists of items that have actually been ordered, one row for each item in each order. this will have, as a minimum, columns for - id, order_id, item_id, quantity, status. the status column indicates things like ordered, shipped, returned, canceled, lost, ...
 
lastly, to use method one, where the items in carts are counted against the available quantity, the carts need to be stored in a database table so that you can use a query to easily, quickly, and directly determine the available quantity.
 
 
to determine the available quantity for method two above -

select item_id, sum(quantity) as qty from (
    select item_id, quantity from inventory WHERE item_id IN(123,456)
 union all
    select item_id, -quantity from view_order_details_allocated WHERE item_id IN(123,456)
) x group by item_id

 
the IN(123,456) term is a list of the item id's for the cart you are displaying/processing. in this example, the query will return the available quantity for item id's 123 and 456. the view_order_details_allocated is a view for the order_details table where only the status values that make that item allocated/unavailable are matched, i.e. ordered, shipped, lost, ...
 
to determine the available quantity for method one above, you would just extend this to factor in the carts -

select item_id, sum(quantity) as qty from (
    select item_id, quantity from inventory WHERE item_id IN(123,456)
 union all
    select item_id, -quantity from view_order_details_allocated WHERE item_id IN(123,456)
 union all
    select item_id, -quantity from carts WHERE item_id IN(123,456)
) x group by item_id

Edited February 9, 2014 by mac_gyver

[smallc28]

I've never done this before but I'm trying my best to master shopping carts 

 

But I'm trying to go for both so I can cover my ass incase a client wants both.

 

May I add you as a friend for future help on this project? I'm going to give this a go hopefully I do it the right way lol.

Edited February 11, 2014 by smallc28