Limit table to 100 rows, after that delete the last and add new one to the top (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?

I think you should handle the logic at the application level (so, php) — Strawberry, Jul 26 '13 at 12:19
Try query like this, `SELECT ip_address FROM table WHERE user='username' ORDER BY id DESC LIMIT BY(0,100)` Note: Not tested — Fallen, Jul 26 '13 at 12:20
In one way yes, but making the database handle this kind of actions might be better for performance. In this case its not really needed to fetch it to php — Martijn, Jul 26 '13 at 12:20
Just curious as to how you came to decide on a limit of 100? You could just record a timestamp alongside each record + delete older records if you desired in order to clean up older data. Unless there's a good reason, arbitary fixed limits aren't always all that useful. Aside from that, either logic within PHP or an AFTER INSERT trigger in MySQL would help you — Simon at The Access Group, Jul 26 '13 at 12:21
According to your edit, you can try this one, http://stackoverflow.com/questions/8048001/how-can-i-set-a-maximum-number-of-rows-in-mysql-table — Fallen, Jul 26 '13 at 12:29

Martijn · Accepted Answer · 2013-07-26T12:40:50.910

2

$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

edited Jul 26 '13 at 12:40

answered Jul 26 '13 at 12:21

Martijn

15,791
4
36
68

can you explain what this query doing? – Jul 26 '13 at 12:23
`$page_no = 1; $limit = 100;` `$offset = ($page_no - 1) * $limit` But you can use `OFFSET` instead of `LIMIT`. – Marin Sagovac Jul 26 '13 at 12:32
thanks, this solved the problem ( i fetch the id of 100th record, then delete everything before it) , just one question, as this table is AI (auto incremental) i am deleting most of the records from the beginning, is it ok if it grows so big? lets say AI starts at 10,000,000 after a year or so? – Vladimir Jul 26 '13 at 12:44
Should not be a problem. If you expect this to grow very rapidly, make the colom a bigint instead of an int, because int maxes out on 2.140.000.000 (somthing-ish) – Martijn Jul 26 '13 at 12:46

score 1 · Answer 2 · answered Jul 26 '13 at 12:59

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

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

2 Answers2