kirkh34 Posted May 11, 2010 Share Posted May 11, 2010 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") Link to comment https://forums.phpfreaks.com/topic/201336-mysql-automatic-update/ Share on other sites More sharing options...
Adam Posted May 11, 2010 Share Posted May 11, 2010 What data type is your `message_date` field? Link to comment https://forums.phpfreaks.com/topic/201336-mysql-automatic-update/#findComment-1056266 Share on other sites More sharing options...
kirkh34 Posted May 12, 2010 Author Share Posted May 12, 2010 the message_date is the "timestamp" Link to comment https://forums.phpfreaks.com/topic/201336-mysql-automatic-update/#findComment-1056860 Share on other sites More sharing options...
kirkh34 Posted May 12, 2010 Author Share Posted May 12, 2010 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 ////////////////////////////// /////////////////////////////// Link to comment https://forums.phpfreaks.com/topic/201336-mysql-automatic-update/#findComment-1056949 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.