finding the ID of the oldest record in the database?

Spud_Nic

I want to limit the number of records in the mysql database I have to 100, at any time - but there will be constantly new records being inserted via PHP.

So, just before it writes the new record in the PHP script, I need to get the PHP to find the oldest record (based on date/time combo, or id), delete it, and then insert the new record.

My question is, how can I detect which is the oldest record in the database? (either based on date/time combo or id)?

Any ideas welcome!
Cheers!

devinemke

DELETE FROM table WHERE date = (SELECT MIN(date) FROM table) LIMIT 1

Spud_Nic

Thanks for your reply.

I've added my table name and field names in, but I get an error saying the syntax is incorrect around the second SELECT statement?
I can't see any errors myself?

thorpe

Show us your query?

Spud_Nic

Ok, so I've changed it to a select statement instead, so I don't delete anything until I know it's working....

$sql = "SELECT * FROM tracker WHERE trID = (SELECT MIN(trID) FROM tracker) LIMIT 1";

tracker is the name of the table, and trID is the name of the field.

From experimenting, it doesn't seem to like the SELECT statement within the WHERE clause, but that should be valid shouldn't it?

So then I run it:
$sql_result = mysql_query($sql, $dbConnection) or die(mysql_error());

And it's on this line where the error is displayed!

Is there something I'm doing wrong that I can't see?

bbreslauer

You need to be using MySQL >=4.1 in order to be able to do subselects.