mySQL/PHP to update if the row exists or else insert when column to check is not unique

Question

I have read about REPLACE and INSERT ON DUPLICATE key queries, and while I can see how useful they are, they don't appear to work for my purposes. Perhaps my database design is off from how it should be, but either way I hope I can get this working.

I am trying to mock up an online store to teach myself mysql and php, and I am recording a session id for use in pulling an individual's cart back for an individual visit and attempting to write inventory to the cart where I'm having an issue is in trying to check if the item is in the cart or not. I want each item to be unique per user, however it should not be unique for the entire table, or else this would prevent the multiple users from buying the same item, wouldn't it?

My table so far looks like this:

 | Cart ID(key) | USER_ID(fk) | ItemNum | ItemQTY |

and I essentially want to see if a USER_ID already has > 0 ItemQTY of ItemNum in their cart. If they do I would like itemQTY to update to itemQTY + however many they add, and if not, then insert a new line.

Will I have to query before I write in order to do this?

Answer 1

You could write a stored procedure and use the stored procedure to handle the inserts.

You will need to learn about writing stored procedures and executing them from PHP.

Also, inside the stored procedure you will need to execute more than one query, but to your application it will look like a single call to the procedure.

HTH.

Answer 2

For a beginner ... I think it's probably okay to just run the query to check if it's there. Then when you're generally more familiar with php and mysql, start building your knowledge.

Others may disagree, but while learning new languages I like to make things work before I make them "perfect."

Answer 3

I think you need a multi-column unique key on CartID and ItemNum:

alter table my_table add unique( CartID, ItemNumber );

Then you can do:

insert into my_table values( 1, 'joe', 12345, 1 ) on duplicate key update ItemQTY = ItemQTY + 1;

Answer 4

Traditional shopping cart software has one cart per user, and that cart is restored each time the user returns. Because you have both a CartID and a UserID, this gives the impression that a user is able to have multiple carts.

Whereas, it may be better to have three tables; Users, Carts & CartItems

Users will need a UserID, Carts will need CartID and something like OwnerID (referencing Users.UserID) , and CartItems will have CartID (referencing Carts.CartID), ItemID and Quantity.

You can even get simpler, since a User will only ever have one Cart, you can ignore the Cart table altogether and replace CartItems.CartID with CartItems.OwnerID (referencing Users.UserID.

I hope this is helpful.