mysql delete rows

[Anon]

Hello,

I have a problem I thought would be easy to solve, but apparently not 🙂 I have a script that allows people to add their url to a page. I want to keep a maximum of 100 url at any time. So I did this using php/myql

connect to database...
count rows
assign number of rows to $counter
if ($counter > 100) {
connect to database
DELETE FROM table_name WHERE ID=min();
etc...

Unfortunately, that does not work.
Does anyone have any advice on getting this done? Thnx.
Richie.

[[deleted]]

ID=min(); can never work, because it's not proper SQL.

I suppose you'll want to delete the oldest records and leave the 100 newest?

You could do:
SELECT date_entered FROM table ORDER BY date_entered desc LIMIT 100;
Then , fetch only the last record in that set using
mysql_data_seek and mysql_fetch_xxx
That will give you the oldest acceptable date.
Then you can just delete the rows where date_entered is earlier than the date you just found.

[Anon]

Thanks vincent. I want the script to be able to do this automatically without my intervention. I'll try your suggestion and see if it'll work.