Limiting SQL Rows

[shauny17]

Hi all,
is there a way in MySQL where I can limit the database itself (Not the output) to 10 rows?

what i am aiming to do is on my website have a list of recently added products to the main database and it stores the 10 most recently added products to the database, and removes the oldest record.

any help would be greatly appreciated

Shaun

[tomhath]

You'll have to do it yourself with SQL. You can get the number of rows in a table with the count() function. If you have version 5.0 of MySQL you could do it in a stored procedure or trigger, earlier versions will need to put the logic in php. It's not hard but should be in a transaction so it all gets done in a single operation http://www.databasejournal.com/features/mysql/article.php/3382171

begin transaction
insert row into your_table
select count(*) from your_table
if the number of rows is over the limit
  delete row with lowest id
commit