[RESOLVED] Delete all rows except for newest....

sebthib55

What query would I run to delete all rows except for the latest 10?

I've tried this but it didn't seem to work.

run_query("DELETE * FROM phpqa_latest_scores LIMIT 10,100000");

I also stored the time in a field that I named phpdate (Its a time stamp in seconds)

For the time stamp I used

$time=time();

Thanks,
Seb

Roger_Ramjet

LIMIT is for SELECT queries.

You need a WHERE clause to define which rows to keep and which rows to delete.

nnichols

Your first query wouldn't work for two reasons - firstly there should not be an asterix between DELETE and FROM, secondly the LIMIT construct only takes a single parameter when used in a DELETE statement.

I would suggest that you use a MySQL DATETIME column to store the time of the INSERT and then DELETE based on that column -

DELETE FROM phpqa_latest_scores WHERE datetime_column < DATE_SUB(NOW(), INTERVAL 5 HOURS);

This would delete all records older than 5 hours. Check out the DATE_SUB() function.

sebthib55

Is there a way to make it work so that it always keep 10 or so inside the database... Because if there arn't any entries in the next 5 hours then it will be empty?

bradgrafelman

I would say use two queries:

$query = 'SELECT COUNT(*) FROM `myTable`';
$exec = mysql_query($query);
$num_rows = mysql_result($exec, 0);

$query = 'DELETE FROM `myTable` ORDER BY `datetime_column` DESC LIMIT ' . ($num_rows - 5);
$exec = mysql_query($query);

NOTE: Code is untested... might want to use this in a test environment first 😉

EDIT: Also, to explain - the $num_rows - 5 bit is where you set how many rows you want to keep in the database... it's basically saying "Delete all rows except for 5 of them" so you'll want to adjust this number to your liking.

sebthib55

That worked like a charm!
Thank you very much!
The only thing I modified in the script was DESC to ASC 😃

bradgrafelman

Don't forget to mark this thread resolved (if it is).

sebthib55

O, I did it. Thanks... Im new to this forum 🙂