Dynamically removing records when certain columns = 0; data cleansing
- by cdburgess
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?
