shopping basket duplicate entries

[chriso20]

Hey folks!

 

I'm working on a shopping basket.

 

I have a table called 'baskets' where i store the member id, product id and quantity of products. So when a member adds something to their basket, they're adding it to this table.

 

 

I cracked the "well what if someone adds an item that's already in their basket?" problem with this code:

 

INSERT INTO baskets(memberId, productId, quantity)
VALUES 
('".$_SESSION['memberId']."', '".$_POST['productId']."', '".$_POST['qty']."')
on duplicate key update quantity = quantity+".$_POST['qty'];

(ignore the possible SQL insertion)

 

The problem is that this only checks the primary key - the member's id. Not the product id as well.

 

So adding 50 of product "A", then adding 100 of product "B" simply updates the database to be 150 of product "A", not adding a new entry for the new product.

 

 

 

Any help with this would be really appreciated!

 

Chris

[fenway]

Why not have a unique key that spans both columns?

 

Alternatively, what's wrogn with having multiple rows for each addition?

[chriso20]

i can't make the productId unique, otherwise only 1 member could have that product in their basket at a time.

 

Multiple rows would be a pain in the arse, you could potentially have 100 rows for the same user buying the same item... not very efficient!

 

Any other ideas?

 

Or should i just do a quick SQL query for that member and product and if it exists, increase the quantity - if not create a new row?

[chriso20]

Ok i've got it functioning as expected but the SQL i've used is not as efficient as it should be;

 

I query the table for that member's id and the product id.

 

If the returned rows is 0, i insert the data, if it's not 0, i update the data.

 

In this same script i'm also going to have to do a multi-table query to find the prices of the products and produce an updated grand total for the products in the basket.

 

 

Is this script a little too database intensive or am i just worrying for no good reason?

[fenway]

i can't make the productId unique, otherwise only 1 member could have that product in their basket at a time.

You're not reading what I wrote -- a multi-column index.

 

Multiple rows would be a pain in the arse, you could potentially have 100 rows for the same user buying the same item... not very efficient!

Yes, but you won't have 100 all of the time, and then you can keep track of when this happens.