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

I have a table in database which records ip addresses

i need to have a maximum o 100 records for each user then delete the last record if the user reaches 100 records and add the new record to the top (newest)

what is the best method to do this with PHP or MYSQL QUERY, should i use num_rows with SELECT * ?

EDIT: I need to limit recording IPs for each user in database, not limit showing them (each user can't have more than 100 IPs in database)

EDIT 2: I was thinking of reading the date of 100th query and then delete each record with date < 100th query, is this a good practice?

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

You talk about selecting the date, always try to go for the fastest column. Your id 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

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)

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

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

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

}

Step 3:

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

     $result = mysql_query ($sql_com);