CODEWITHSUNDEEP
mysqlsql-limit
DannySMc
DannySMc

Reputation: 27

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

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.

Upvotes: 0

Views: 2452

Answers (2)

Abdul Muheet
Abdul Muheet

Reputation: 315

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";

   }

Upvotes: 0

ahmed.hoban
ahmed.hoban

Reputation: 516

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
);

Upvotes: 1

Related Questions