将表限制为100行，之后删除最后一行并将新的一行添加到顶部（MYSQL，PHP）

Question

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?

Answer 1

$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