PHP & MySQL: Delete all old rows keeping 25 of the newest rows

Question

Let's make this simple: I have a table called chat that has many rows in, every user can add a new message and this means the table can get quite large.

(I have looked online and it's not very 'clear' on some examples.)

I need to delete all older messages, keeping only say 25 of the newest ones. Now I wish to do it via id, as my id is set to auto-increment.

This is what I have but it doesn't work:

DELETE FROM `chat` ORDER BY `id` DESC LIMIT 0,50

I found it and changed it, but no luck!

I just not sure what to try. I am not very experienced, I know the basics, any help is very appreciated.

thats not working. id is not previous id + 1. It could be: 1...2...5...6...Happens when you delete some rows. — ahmed.hoban, Mar 19 '15 at 12:23

score 1 · Answer 1 · answered Mar 19 '15 at 12:14

1

to keep 50 records:

DELETE FROM `chat`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `chat`
    ORDER BY id DESC
    LIMIT 50
  ) foo
);

answered Mar 19 '15 at 12:14

ahmed.hoban

516
3
10

$sql = "SELECT * FROM `chat` "; $result = $conn->query($sql); $count = $result->num_rows; $count = $count - 25; $sql = "DELETE FROM `chat` ORDER BY `id` DESC LIMIT $count"; if ($conn->query($sql) === TRUE) { echo "true"; } else { echo "false"; } – DannySMc Mar 19 '15 at 12:17
I used something else, but I shall have a try with that thanks! – DannySMc Mar 19 '15 at 12:17
you did it with php and 2 db queries, which could be a bit slower but possible! – ahmed.hoban Mar 19 '15 at 12:21

Abdul Muheet · Accepted Answer · 2019-12-30T07:36:51.153

0

I saw your php code in the comment and editing my answer which is working if you are looking for this... For deleting all old rows keeping 25 of the newest rows just first select all the record from your chat table, then count the same, estimate your limit i.e. deducting 25 from the count, thereafter, run a delete on the chat table but in DESC order and give the limit which was estimated by deducting 25 from the count.

   $sqli = "SELECT * FROM chat";
  $result = $conn->query($sqli); 
  $count = count($result);
  $limit = $count - 25;
  //echo $limit;
   $sql = "DELETE FROM chat ORDER BY id DESC LIMIT $limit";
   if ($conn->query($sql) === TRUE)
   { 
   echo "true"; 
   } 
   else
    {
        echo "false";

   }

edited Dec 30 '19 at 07:36

answered Mar 19 '15 at 12:19

Abdul Muheet

315
3
25

1

because its a pure code answer do some explanation(im not the downvoter but that is probably the reason) – Freddy789 Aug 09 '19 at 08:56
Danny who has asked the question had pasted his entire code in the comment below, I debug the same and paste the code above after running the same! I think if I have edited a pasted code then there need no explanation; the down vote is out of prudence. – Abdul Muheet Dec 30 '19 at 07:14

PHP & MySQL: Delete all old rows keeping 25 of the newest rows

2 Answers2