Limiting records

[dannon]

How can I make it so that if there are more than 5 foreign ID's exist in the table, and every time a new foreign ID is inserted, the 6th oldest row gets removed, so I can limit how many rows of the same foreign ID can exist in the database?

[Jessica]

What?

[mac_gyver]

while it might be possible to do this in one delete/subquery query after you insert the new row, a straight forward method using php would be to -

 

1) run a select query that gets a row COUNT(*) and the MIN(id) row id for the foreign id value you just inserted.

 

2) if the row count is greater than 5 (the row you just inserted made six rows), delete the row who's row id is the minimum row id from step #1.

[mac_gyver]

as a continuation of the above, using a strictly query method, you would do this using a trigger that is ran after each insert.

[dannon]

Thank you!

 

I have created this query: 

 

SELECT @count := COUNT(*) AS "count", @min := MIN(id) AS "min" FROM `items` WHERE item_id = 5;
DELETE FROM `items` WHERE @count > 5 AND id = @min;

Is there a way to make it better?