Page 1 of 1

Remove records after x amount of time

Posted: Tue Apr 07, 2009 4:13 pm
by ninethousandfeet
hello,

my users have conversations they are involved in listed on their profile page. i am using mysql and php. the conversations are grouped by the conversation title. i would like to remove the conversations completely from the db when 0 comments have been entered into a conversation in the last 10 days for example. any suggestions?
here is the SQL that retrieves the conversations. how do i delete all comment_title where comment_title = older than 10 days?

Code: Select all

 
 
$var5_getComment = "-1";
if (isset($_GET['post_id'])) {
  $var5_getComment = $_GET['post_id'];
}
mysql_select_db($database_connUser, $connUser);
$query_getComment = sprintf("SELECT testComment.user_id, testComment.comment_id, testComment.comment_username, testComment.comment_title, testComment.`comment`, testComment.comment_date, DATE_FORMAT(comment_date, '%%c/%%e/%%y\r %%h:%%i %%p') AS us_format, postingTable.post_id, postingTable.user_id FROM testComment INNER JOIN postingTable ON testComment.user_id = postingTable.user_id OR testComment.comment_id = postingTable.user_id WHERE postingTable.post_id = %s GROUP BY testComment.comment_title", GetSQLValueString($var5_getComment, "int"));
$getComment = mysql_query($query_getComment, $connUser) or die(mysql_error());
$row_getComment = mysql_fetch_assoc($getComment);
$totalRows_getComment = mysql_num_rows($getComment);
 
thank you!

Re: Remove records after x amount of time

Posted: Tue Apr 07, 2009 4:44 pm
by Christopher
Please do not double post.

You want to "DELETE FROM testComment WHERE comment_date column < ". You can either use PHP functions to get a date in the past or preferably use an SQL function to do the same thing. You may need to delete records from multiple tables.

Re: Remove records after x amount of time

Posted: Thu Apr 09, 2009 6:07 pm
by ninethousandfeet
sorry about that.

so, let's say for example that i have conversations on a php page: userprofile.php. i could just run this DELETE on that page above the doctype?