Jump to content

Delete all but the # newest Data


Cultureshock

Recommended Posts

Basically, I have a fb/twitter status-like system on my site. However I don't want every member to be clogging my databases with 1000 statuses each.

 

Say I only want each member to have 150 statuses at a time.

 

How would I go about deleting the oldest row every time someone posts a 151st status?

(ie delete the last status to allow room for a new one.)

 

I'm afraid if my novice hands attempt something I'll end up getting everything deleted or something.

 

Table:

userid, statustext

 

Link to comment
https://forums.phpfreaks.com/topic/183632-delete-all-but-the-newest-data/
Share on other sites

You will definitely need to add a column or two to the table to accomplish this. Here is what I would do:

 

Add an ID column that is an auto-increment field. Then when someone adds status(es) run a subsequent query to delete any that are more than the max you want available. Something like this should work:

 

DELETE FROM table
WHERE userid = '$userID'
  AND id NOT IN (SELECT id
                 FROM table
                 WHERE userid = '$userID'
                 ORDER BY id
                 LIMIT 0, 150)

thanks :D that's what I was thinking, but I wanted a second opinion before I attempted it nd deleted everything like an idiot.

 

That's why you should always test your code. You should have a separate development environment where you can back-up and restore the database.

 

Besides, you can also test any DELETE as a SELECT first to validate it is selecting the correct records before you do a DELETE

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.