Basic question, sorry. Basically, I have a script that creates a MySQL entry each time someone visits the page. A logging script. However, I want to make it autoprune after, say, 100 visits.
For example in pseudo code:
if amount of rows > 100 {
delete rows 1+ until amount of rows == 100 }
So in a nutshell, each time a new row is added after 100, it needs to automatically remove the row with the smallest id (My primary key).
Another possible option would be to use database TRIGGERS (http://forge.mysql.com/wiki/Triggers)
It may be overkill in this scenario but would check the table every time a row is inserted. An example (which I haven't tested!) :
Create Trigger prune_MyTable AFTER INSERT ON MyTable FOR EACH ROW BEGIN DECLARE num_rows INTEGER; DECLARE lowest_id INTEGER;
SELECT COUNT(*) INTO num_rows FROM MyTable;
IF num_rows > 100 THEN SELECT MIN(id) INTO lowest_id FROM MyTable LIMIT 100;
UPDATE MyTable DELETE WHERE id < lowest_id;
END IF; END$$