limit 20 records in mysql table

[MDanz]

how do i limit 20 records in a mysql table?

 

so say i add 20 records, then i add another, it deletes the oldest record..

 

 

how do i accomplish this?

[ILMV]

Can't you just let them all stack up (say 500 records) and when it comes to querying the database use the LIMIT in SQL to achieve your solution:

 

SELECT * FROM table LIMIT 20

 

 

ILMV

[MDanz]

that doesn't delete records from mysql.. that only limits 20 to being displayed..  I want only 20 in mysql at all times...

[MadTechie]

assuming you have less than 100020  records, this will work,

DELETE FROM `table`  LIMIT 20 , 100000

[ILMV]

Would you want to enforce an order by as well MacTechie?

[MadTechie]

True, order by timestamp would be idea, but autonumber maybe fine

So add

ORDER BY `timestamp` DESC
ORDER BY `ID` DESC

before the limit

[robert_gsfame]

or just do it with OFFSET which will tell the number of records

[MadTechie]

or just do it with OFFSET which will tell the number of records

I am using an Offset!

When you pass LIMIT two arguments the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return, using LIMIT row_count OFFSET offset is purely for compatibility with PostgreSQL.