Delete Old Entries

[joshl_1995]

Hello Community,
I was wondering if there is a way to limit users on how many database entries they can insert then once they have reached 100 (Max entried) entries it will delete old entries. So if the user has reached 100 entried and they try to insert another entry it will delete and old entry to make room for there entry.

[mmcdonald]

If this was my project I would store their username and a unix time stamp in a queries table. I would then execute a cronjob every few minutes to count the number of times the users username appears in the queries table. If the cronjob count exceeded 100 I would take 100 off of the result.

Lets say there are 120 rows meaning this user has executed 120 queries. I would take 100 off of the result ( The allowed total ) leaving me with 20.

I would then use a delete query to find the oldest 20 and delete them. Lets say we define that 20 in a variable called $overflow.

DELETE FROM table_name WHERE username=$username ORDER BY timestamp ASC LIMIT 0, $overflow

I hope this makes sense, and I hope it's along the right lines :)
M.

[joshl_1995]

I was thinking I would have to use someone kind of system other then just plane MySql so thanks I guess that is going to be the only way I'll get the best result, but if I do it this way at least I have a little more control seen I'll be using PHP.

So thanks, I'll go ahead and use CronJobs.

[mmcdonald]

I was thinking I would have to use someone kind of system

I don't understand what you mean here? PHP + MySQLi + CronJobs = A system... What kind of solution are you looking for?

[joshl_1995]

When I say "system" I mean some other way of doing what I wanted to accomplish.
I thought I could find a way of doing this with just plain MySql but I see I will have to implement other scripts such as PHP (To check the limit). Do you get what I'm talking about now?

[mmcdonald]

Right I see and no; I don't think there is a way. And even if there is, a cronjob would still be required to execute the sql queries :)

I'm not aware of any tools that automate sql query executions based on arguments that arn't scripts :)

Good luck!

[cereal]

If you want something that runs on background you can consider a MySQL trigger and Gearman, check the example about URL Processing in these slides. Bye.

[joshl_1995]

It's ok I'll just go with using PHP and cronjobs but thanks.