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

563

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?

369

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
225

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

People are also looking for solutions to the problem: php - How I can insert comments into my new SocialEngine Module

Source

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.

Ask a Question

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.

Similar questions

Find the answer in similar questions on our website.