Delete records greater than X

Anon

Hopefully somebody can clear this up for me...

I've got a news table setup, adds and edits, yadda yadda. Now I wanted this thing to have itself automatically prune...so that when it displays the news, it will first delete any records in the table that are numbered higher than x. So, thinking I had it all figured out, I created the table with auto-increment (id int unsigned auto_increment), and then I am running

mysql_query("DELETE FROM news WHERE id>=$newsCount") or die("Unable to DELETE record from News Database : ".mysql_error());

(and I am using a test record number of 4)

However, it deletes everything, since the recordset numbering does not restart at 1, but goes on up after a deletion...15, 16, 17, instead of deleting to 4, and then recounting from 4 to 5, 6, 7...see what I mean?

Can anyone explain to me what I need to do our should be doing?

Anon

Actually, say I have 3 records and I add a fourth, the fourth one should become 1 in the record, the third one become 2, etc...

Basically, the newest records should be first, so that when it prunes to the number, it prunes the older records and not the newest ones...

If this is confusing, I can try and explain it a bit better...

Anon

how about deleting by date/time?

Anon

if you wish to retain the first four records independent of what the id is use a loop with a counter when the counter is greater than a certain number the SQL delete statement can then be executed

i dont like having a site run with user privileges that allow deletes to be carried out rather i prefer to use a flag assignment to take it out and then a private script on the server running with delete privileges on a crontab can look for the flag and delete the record

paul

Anon

Yeah, I could, and it certainly would be easier, but there could be days that go by without an addition/update to this table, and I wouldn't want all the records to end up deleted...which is kind of why I was going for deleting by recordnumber... :\

Anon

Hey, I hadn't even thought of that, Paul, and I got it to work! Thanks for the help 🙂