mysql automatic update

[kirkh34]

how could you update a table automatically if a date of a row is a certain amount of days old? The date in my tables are displayed like this,

date("Y-m-d H:i:s")   

 

this code isn't working, is this the correct way to use operators in a query?

$twoweeks = strtotime('-14 days');
$twoweeks2 = date("Y-m-d H:i:s",$twoweeks);
$sql = mysql_query("DELETE FROM message_rec WHERE message_date < $twoweeks2")

 

 

 

 

 

 

[Adam]

What data type is your `message_date` field?

[kirkh34]

the message_date is the "timestamp"

[kirkh34]

this code kind of works, i don't know if i have an excess of code or if there is anything simpler than this,  the problems are... if a row trash_date reaches 14 days old, it deletes all the rows in the table, also... when the message_trash is marked to "1" in the row then the trash_date is updated for the current time, i have a message_date that is the date of the creation of the message, but when the trash_date is updated, the message_date is updated also... i don't understand this at all because message_date it totally left out of all this.. so my two probs

 

1. when a trash_date in a single row reaches 14 days old, all of the rows in the table are deleted instead of the single row

2. when a trash_date is updated to the current time, it also updates the message_date

//////////////////////////////
///////////////////////////////
//AUTOMATIC MESSAGE TRASH DELETE
//////////////////////////////
///////////////////////////////


$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_date = $row['trash_date'];
$trash_date = strtotime($trash_date);
$twoweeks = strtotime('-14 days');


if ($trash_date < $twoweeks) {


$sql = mysql_query("DELETE FROM message_rec WHERE trash_date > $twoweeks")
		or die (mysql_error());
	$sql = mysql_query("DELETE FROM message_sent WHERE trash_date > $twoweeks")
		or die (mysql_error());



}

   } //close while
  

//////////////////////////////
///////////////////////////////
//^^^^^^^^^^^^^^^^^^^^^^^^^^^^^//
//AUTOMATIC MESSAGE TRASH DELETE
//////////////////////////////
///////////////////////////////