Delete outdated mysql records

Hi,

I have a php query:

mysql_query("SELECT * FROM comments WHERE id = $id ORDER BY date ASC")

Code (markup):

Lets say that the row count for this is 20. What code should I use to delete the oldest posts so that the row count is always 5.

This is hard to explain, but I hope this makes sence

Its like using LIMIT 5 but i want to delete the records that are not shown.

Cheers.

 

You want to delete the other 15 rows yes?

 

Yes that is correct.

 

So you want to delete all records after the 5th record? You could do something like..

$res = mysql_query("SELECT * FROM comments WHERE id = $id ORDER BY date ASC");
$numRows = mysql_num_rows($result);
$rowsToDelete = $numRows - 5;
mysql_query("DELETE FROM comments WHERE id = $id ORDER BY date ASC LIMIT 5,$rowsToDelete")

PHP:

It might be possible in one query but can't think of it right now.

 

Excelent, thank you so much. I have just changed it up a bit to fit the solution.

$comment_query = mysql_query("SELECT * FROM comments WHERE id = $id ORDER BY date ASC");

// if there is more than 5 rows delete the rest.
$numrows = mysql_num_rows($comment_query);
if($numrows > 5){
$delete = $numrows - 5;
mysql_query("DELETE FROM game_comments WHERE id = $id ORDER BY date ASC LIMIT $delete");
}

PHP:

What would I do without this forum
Thanks again.

 

No problemo again i'm not sure if it's possible to do this in 1 query, but at least you've got a working solution.

Also I think order by is ASC by default so you don't need to specify this.

 

A faster way (ie: instead of a loop) is to make your first query (keep in mind date is a reserved word so you want to use `` around it).

"SELECT `date` FROM comments WHERE id = ".$id." ORDER BY `date` ASC LIMIT 1 OFFSET 5";

Then pull the date from the row you just grabbed, the query above grabs a single row , offset at the 5th row at that order.

Then you can do something like this in a single query statement

"DELETE FROM comments WHERE `date` < '".$oldest_date."'";

That way processing time is cut down to a single record for the date, and a single query to delete all the rows older than that date.


Edit:
This may or may not work (I know the Where clause for date definitely works) , but this might be a single way to remove all records older than 5 days, but only if there are more than 5 records in the database.

"DELETE FROM comments WHERE (`date` < (CURDATE() - INTERVAL 5 DAY)) AND (SUM(*) > 5)"

But I don't think you can use the SUM() function anywhere other than in a Select clause.

 

First instead of ASC you should use DESC. Otherwise unless your `date` field is reverse-valued you are deleting the newest rows.