Cultureshock Posted December 1, 2009 Share Posted December 1, 2009 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 More sharing options...
Psycho Posted December 1, 2009 Share Posted December 1, 2009 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) Link to comment https://forums.phpfreaks.com/topic/183632-delete-all-but-the-newest-data/#findComment-969243 Share on other sites More sharing options...
Cultureshock Posted December 1, 2009 Author Share Posted December 1, 2009 thanks that's what I was thinking, but I wanted a second opinion before I attempted it nd deleted everything like an idiot. Link to comment https://forums.phpfreaks.com/topic/183632-delete-all-but-the-newest-data/#findComment-969317 Share on other sites More sharing options...
Psycho Posted December 1, 2009 Share Posted December 1, 2009 thanks 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 Link to comment https://forums.phpfreaks.com/topic/183632-delete-all-but-the-newest-data/#findComment-969380 Share on other sites More sharing options...
Cultureshock Posted December 2, 2009 Author Share Posted December 2, 2009 Oh, it's a seperate test site, but I don't want to remake 1000 fake statuses, yaknow? And yeah, the Select test thing is something I've never thought of. Thanks! Link to comment https://forums.phpfreaks.com/topic/183632-delete-all-but-the-newest-data/#findComment-969559 Share on other sites More sharing options...
Psycho Posted December 2, 2009 Share Posted December 2, 2009 Oh, it's a seperate test site, but I don't want to remake 1000 fake statuses, yaknow? So, back up your database before you run your tests. If you run into problems restore your database. Basic development/testing processes. Link to comment https://forums.phpfreaks.com/topic/183632-delete-all-but-the-newest-data/#findComment-969746 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.