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

Filed under:
|
|

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

Related posts about sql

Related posts about mysql