deleting things that should not

[jasondavis]

Here is my code that delete out old mail messages on my site, it works perfect except 1 problem it is deleting mail that is marked

save = Yes and it should not, any help greatly appreciated

function autodeleteemail() {
	// select the friend_mail_details auto_id for messages that are
	// - read or replied
	// - older than 7 days
	// - not saved
	$q = "SELECT d.auto_id FROM `friend_mail_details` d,`friend_mail_setting` s 
	WHERE (d.status = 'Read' or d.status = 'Replied') 
	AND TO_DAYS(NOW()) - TO_DAYS(d.date1) > 7 
	AND d.auto_id = s.mail_id 
	AND s.save = 'No';";
	$res = executequery($q);
	// loop through each id and delete the corresponding row in both tables
	while ($row = mysql_fetch_assoc($res)) {
		$q = "DELETE FROM `friend_mail_details` WHERE auto_id = '{$row['auto_id']}';";
		executequery($q);
		$q = "DELETE FROM `friend_mail_setting` WHERE mail_id = '{$row['auto_id']}';";
		executequery($q);
	}
}

[jasondavis]

when I run the code in phpmyadmin it seems to never select messages marked with Save but in real life any message that I mark saved, eventually gets delted somehow, its weird and this is the only thing that could cause it to be deleted I think

[mikell]

Can you give some more information about friend_mail_details and friend_mail_setting...sql create statements for them would be a good start...Also what is the relationship between them? 1 to 1, 1 to many? And what are they used for exactly.

But to answer your question, that function looks fine to me...I don't see how it could delete any records with s.save = 'Yes' when its obviously only selecting records with s.save = 'No'. Except maybe if auto_id is not unique...or maybe if you have a trigger thats being run when a record from friend_mail_setting is deleted or there is some flaw in your database design...

If the problem isn't in your database, its probably somewhere in your application logic. Maybe there is some code that is changing the save field from 'Yes' to something else when it shouldn't. Maybe code that does something to "old" emails perhaps...

[jasondavis]

Here is my tables basically 1 is the mail details table which has the message and the settings table determines what folder the message should be in like sent folder, inbox

CREATE TABLE IF NOT EXISTS `friend_mail_details` (
  `auto_id` int(11) NOT NULL auto_increment,
  `to_user` int(10) NOT NULL default '0',
  `from_user` int(10) NOT NULL default '0',
  `subject` varchar(255) NOT NULL default '',
  `message` text NOT NULL,
  `status` enum('Read','Unread','Replied') NOT NULL default 'Read',
  `date1` datetime NOT NULL default '0000-00-00 00:00:00',
  `alert_message` enum('yes','no') NOT NULL default 'yes',
  PRIMARY KEY  (`auto_id`),
  KEY `to_user` (`to_user`),
  FULLTEXT KEY `message` (`message`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1158044 ;

-- --------------------------------------------------------

-- 
-- Table structure for table `friend_mail_setting`
-- 

CREATE TABLE IF NOT EXISTS `friend_mail_setting` (
  `auto_id` int(11) NOT NULL auto_increment,
  `user_id` int(10) NOT NULL default '0',
  `mail_id` int(11) NOT NULL default '0',
  `sent` enum('No','Yes') NOT NULL default 'No',
  `save` enum('No','Yes') NOT NULL default 'No',
  `inbox` enum('No','Yes') NOT NULL default 'Yes',
  `trash` enum('No','Yes','Delete') NOT NULL default 'No',
  PRIMARY KEY  (`auto_id`),
  KEY `user_id` (`user_id`),
  KEY `mail_id` (`mail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2315431 ;

[mikell]

Well from what I can tell that function is not the problem. If you're sure that's the only place emails are deleted, then I suggest looking at any code that is setting the
'save' field. Maybe there is a bug that is changing it from 'Yes' to 'No'. Good luck!

[Weedpacket]

And, not incidentally, it would be much more efficient to put the criteria for deletion into the delete statement, instead of a separate select.

Delete
 friend_mail_details, friend_mail_setting
From
 friend_mail_details d, friend_mail_setting s
Where
	(d.status=='Read' OR d.status=='Replied')
AND ....etc.