Dynamically removing records when certain columns = 0; data cleansing
Posted
by
cdburgess
on Stack Overflow
See other posts from Stack Overflow
or by cdburgess
Published on 2011-01-14T02:34:14Z
Indexed on
2011/01/14
2:53 UTC
Read the original article
Hit count: 290
I have a simple card table:
CREATE TABLE `users_individual_cards` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` char(36) NOT NULL,
`individual_card_id` int(11) NOT NULL,
`own` int(10) unsigned NOT NULL,
`want` int(10) unsigned NOT NULL,
`trade` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`individual_card_id`),
KEY `user_id_2` (`user_id`),
KEY `individual_card_id` (`individual_card_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
I have ajax to add and remove the records based on OWN, WANT, and TRADE. However, if the user removes all of the OWN, WANT, and TRADE cards, they go to zero but it will leave the record in the database. I would prefer to have the record removed. Is checking after each "update" to see if all the columns = 0 the only way to do this? Or can I set a conditional trigger with something like:
//psuedo sql
AFTER update IF (OWN = 0, WANT = 0, TRADE = 0) DELETE
What is the best way to do this? Can you help with the syntax?
© Stack Overflow or respective owner