ON DUPLICATE KEY UPDATE half working

Question

I have a shopping cart as a DB table called cart that stores records of items in each SaaS instances shopping cart. What I want to do, is that if a product is added to the cart for the a user and an identical record already exists, is to update the quantity column in the table to equal the old value plus the new value, only if status = 0, else, enter a new record.

Breakdown:

If the item is already in the cart, update the quantity column to set the new quantity value ONLY if status column = 0, else, add the item to the cart table as a new row.

Here's what I have so far:

    $itemList = $db->query("INSERT INTO 

                                        cart(
                                        `lmsid`, 
                                        `userid`, 
                                        `courseid`, 
                                        `assigned_by`, 
                                        `assigned_on`, 
                                        `quantity`) 

                            VALUES(

                                        :lmsid, 
                                        :userid, 
                                        :courseid, 
                                        :assigned_by, 
                                        :assigned_on, 
                                        1) 

                            ON DUPLICATE KEY UPDATE 

                                        `quantity` = `quantity` + values('quantity')
                            )", 

                            array(
                            "lmsid"=>$core['id'], 
                            "userid"=>$user,
                            "courseid"=>$_POST['courseid'], 
                            "assigned_by"=>$userInfo['id'], 
                            "assigned_on"=>time())
    );

At the moment...

1. If a record doesn't exist at all, it's inserted perfectly.
2. If a record exists where status = 0, the existing quantity is updated perfectly.
3. If a record exists where status = 2, the query fails with:

Unhandled Exception. SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '66-2' for key 'useridAndCourseid'

The key useridAndCourseid in the table is:

UNIQUE KEY `useridAndCourseid` (`userid`,`courseid`)

The status column is updated to '1' when a product is deleted from a cart, this record stays there forever for our records. The status is updated to '2' when a product has been paid for, again, for our records. When a record is just sitting in the cart waiting to be dealt with, it has a status of 0.

What I need help with

At the moment the query is half working, but I just don't know how to say 'if record exists and status = 0 update quantity, else, insert new record'. That's literally all I'm looking to achieve but I think I'm try to over complicate it or I've spent so long trying to overcomplicate it I'm forgotten what I'm after! I'm sure there is a simple solution. If only I could use ON DUPLICATE KEY and a WHERE clause...

Thanks for the help!

Answer 1

There are various ways to approach this. But, if you want a single statement for the insert, you need to create a unique index that does what you want. This index should have a column that takes on some value (say 0) when status = 0 and NULL at other times. A NULL value is not treated as a duplicated key. You might call this the IsActiveFlag.

If you have such a column, then the index:

create index idx_cart(userid, courseid, IsActiveFlag)

should allow you to do what you want.