Only keep x ammount of rows in a table

[muddy9494]

Ok, well i have a chat script. And every 20 or so seconds it refreshes. When it does this, i also want it to removed all the rows except for the 15 latest ones.

 

How would I do this?

 

 

 

 

[Asheeown]

Where is it pulling the chat from?

[muddy9494]

A database. I just want it to remove all but the 15 latest entries from the database. All the posts are sorted by id's too so yeah

 

[Asheeown]

SELECT * FROM Messages ORDER BY Id DESC LIMIT 15

 

Replace "Messages" with your messages table, add any 'WHERE' statements you may have before the order by command.  The key is the LIMIT 15 at the end, tells it to only pull 15 records

[muddy9494]

Yes i know that. That is how it is working now. But I want it to delete all of the ones not showing =p

[aggrav8d]

So you never have more than 15 records in the table?  What if you start the table with 15 blank rows and then you

 

UPDATE `Messages` SET text='$whatever', sent=NOW() WHERE id=(SELECT id FROM `Messages` ORDER BY sent ASC LIMIT 1);

 

that way the system would just keep removing the oldest and replacing it with the newest.

When you need to display the 15 in order use

 

SELECT * FROM `Messages` ORDER BY sent ASC;

 

and you know you'll only get 15.  If you need to make the buffer longer add some rows and if you need to make it shorter remove a few rows.  This also avoids the problem of a really busy chat system overflowing your index count.

[Asheeown]

If you're still looking for an answer, you can use a NOT IN statement in a delete where clause.  Like so...

 

DELETE * FROM Messages WHERE Id NOT IN(SELECT * FROM Messages ORDER BY Id DESC LIMIT 15);

 

Now, I don't know if yours is DESC or ASC, could never distinguish without actually coding it and trying both.