Delete all but the # newest Data

[Cultureshock]

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

 

[Psycho]

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)

[Cultureshock]

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

[Psycho]

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

[Cultureshock]

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!

[Psycho]

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.