Limit table to 100 rows, after that delete the last and add new one to the top (MYSQL , PHP)
Get the solution ↓↓↓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 usenum_rows
withSELECT *
?
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
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. 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
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
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);
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);
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: undefined array key php
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.