Limit table to 100 rows, after that delete the last and add new one to the top (MYSQL , PHP)

Answer

Solution:

$result = mysql_query("SELECT id FROM tablename WHERE user_id=123 ORDER BY id DESC LIMIT 100,1");
$fetch = mysql_fetch_assoc($result);
mysql_query("DELETE FROM tablename WHERE id <".$fetch['id']." AND user_id=123");
Copy code

You talk about selecting the date, always try to go for the fastest column. Yourid column probally is auto_increment and has an index on it, which makes this a fast column. The date value is probally not indexed, thus slower
Small sidenote: I'm using mysql_ functions, you should use mysqli_ functions

---

I've tried these, but they dont not work (for me):

DELETE FROM tablename WHERE user_id=123 LIMIT 100,9999
Copy code

Another way I wanted to try was this:

DELETE FROM tablename WHERE user_id=123 
WHERE id NOT IN(SELECT id FROM tablename WHERE user_id=123 ORDER BY id DESC LIMIT 100)
Copy code

But subqueries dont support a limit (maybe the more recent versions do, but mine doesnt)
Marin Sagovac suggested this, but also did not work for me:

DELETE FROM tablename WHERE user_id=123 LIMIT 10 OFFSET 100
Copy code

Answer

Solution:

Try like this useful for you .....

Delete older comments from room 1 (keep last 3 left)

Step 1:

       $sql_com = "SELECT id FROM `mytable` WHERE roomid = '1'";

       $result = mysql_query ($sql_com); $num_rows = mysql_num_rows($result);   
Copy code

Step 2:

if ($num_rows > 3) {

      $sql_com = "SELECT id FROM `mytable` WHERE roomid = '1' ORDER BY id DESC LIMIT 3,1"; 

      $result = mysql_query ($sql_com);

     $row = mysql_fetch_array($result, MYSQL_NUM);
Copy code

}

Step 3:

     $sql_com = "DELETE FROM `mytable` WHERE roomid = '1' AND id < ".$row[0];

     $result = mysql_query ($sql_com);
Copy code