Slight Problem With Procedure

[Jonathon]

Hi Larry,

 

I've adapted slightly the procedure from the book, but I find that if I have 2 users each with their own cart and user 1's cart tries to put an item that is already in user 2's cart I get an error, in that the cart doesnt update, so I plugged the query inside PHPMyAdmin and I get this actual error:

 

#1062 - Duplicate entry '125234' for key 'product_id'

 

 

PROCEDURE `add_to_cart`(uid INT(3), cat INT(1), pid VARCHAR(15), qty INT(3))
BEGIN
DECLARE cid INT;
SELECT id INTO cid FROM carts WHERE user_id=uid AND category=cat AND product_id=CONVERT(pid USING utf8) COLLATE utf8_general_ci;
IF cid > 0 THEN
UPDATE carts SET quantity=quantity+qty, date_modified=NOW() WHERE id=cid;
ELSE 
INSERT INTO carts (user_id, category, product_id, quantity) VALUES (uid, cat, pid, qty);
END IF;
END

 

I'm a little confused as to why right now - do you have any ideas at all

[Larry]

Well, there should be a UNIQUE index that prevents the same item from being put in the same cart ID twice. Perhaps your UNIQUE index just prevents the same item from being put into the table twice?

[Jonathon]

Hi Larry,

 

Thanks for getting back to me, looking at my exported table my SQL is:

CREATE TABLE IF NOT EXISTS `carts` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `quantity` tinyint(3) unsigned NOT NULL,
 `user_id` int(4) unsigned NOT NULL,
 `category` tinyint(3) unsigned NOT NULL,
 `product_id` varchar(15) NOT NULL,
 `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `date_modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`),
 UNIQUE KEY `product_id` (`product_id`),
 KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=86 ;

 

Looking at my SQL it is due to my index stopping the same item being in there twice, to overcome this how do I go about it? I'm sorry if this sounds stupid, I'm not sure how to get the UNIQUE index to work in tandem?

[Larry]

Drop the UNIQUE index on the product_id and then add a UNIQUE index on (product_id, user_id). That way it’s the combination of the two column values that must be unique. I better go make sure I wrote it in the book that way!

[Jonathon]

I was going to suggest that, but I started to think that it would just produce separate indexes as opposed to a tandem one, so just thought ASK. I did flick through the book, I didn't notice it in the SQL section, but to be honest that means absolutely nothing! But thanks Larry