[RESOLVED] delete after row expires

kirkh34

i have two tables of user messages that when put in the trash, receive a date 2 weeks ahead of the current time in the trash_expire column, i am trying to make an if statement, if now is greater than the expiration, delete the row....i cannot get this right i have tried for days

$sql = mysql_query("SELECT * FROM message_sent WHERE message_trash = 1 UNION SELECT * FROM message_rec WHERE message_trash = 1")
 or die (mysql_error());

while
       ($row = mysql_fetch_array($sql)) {


$trash_expire = $row['trash_expire'];
$trash_expire = strtotime($trash_expire);
$now = strtotime("now");

 if ($trash_expire < $now) {

	$sql = mysql_query("DELETE FROM message_rec WHERE trash_expire < '$now'")
	or die (mysql_error());
	$sql = mysql_query("DELETE FROM message_sent WHERE trash_expire < '$now'")
	or die (mysql_error());

   }





 } //close while

bradgrafelman

Why do an if() statement at all? Just run the DELETE query with the date condition. If the rows are found, great - they get deleted. If not, no big deal.

I would say you can combine all three of those queries down into one:

DELETE r, s
FROM message_rec r, message_sent s
WHERE r.trash_expire < (NOW() - INTERVAL 2 WEEK) OR s.trash_expire < (NOW() - INTERVAL 2 WEEK)

though I don't have access to a MySQL server right now to test that query.

EDIT: Hrm... now that I think about it, I don't know if you can use a single query or not.

Even if you can't, you can still do a single DELETE FROM query for both tables and get rid of the SELECT statement.

kirkh34

thanks for your response, i tried to use the single query with no luck, but i have a couple of questions about your query, the "r" and the "s", are those used to differentiate the two tables?... and also the "(NOW() - INTERVAL 2 WEEK)"... the trash_expire is set for 2 weeks in the future when it is created, wouldn't I want that date to be < now() in order for it to work?

bradgrafelman

kirkh34 wrote:
the "r" and the "s", are those used to differentiate the two tables?

Yeah - they're table aliases I created in the query ("FROM message_rec r", etc.). I'm lazy, and I'd rather type "r" than "message_rec" in the query :p

kirkh34 wrote:
the trash_expire is set for 2 weeks in the future when it is created, wouldn't I want that date to be < now() in order for it to work?

I didn't think about what that field might contain... why not just have a "trash_date" column that holds the datetime that the user deleted the message? Unless you have varying expiration lengths, of course (e.g. admins/moderators get 3 weeks instead of 2), in which case you're right - you'd just compare your trash_expire field to NOW().

kirkh34

okay awesome, i did that but i had to make 2 queries for each table and it works great, thanks so much!