[SOLVED] mysql limit and delete

[andyd34]

I need to show the last 20 members that have visited a page. I know how to get the results but how would I limit the stored names to 20 in a mysql-db.

 

I don't mean

mysql_query("SELECT * FROM SOMEWHERE LIMIT 0,20");

 

I mean delete entries greater than 20 but keep the 20 newest

[richardw]

Hello,

 

Make sure your table has a timestamp, and then sort on the date, most recent first fot the last 20

 

mysql_query("SELECT * FROM SOMEWHERE ORDER BY yourtime_stamp DESC LIMIT 0,20");

 

I hope this helps

 

Richard

[andyd34]

Hi, Thanks for that but the issues isn't fetching the information its deleting it. I don't want to fill the database up with unwanted information.

 

for instance the db holds records 1 to 20. some visits the page and it holds records 1 to 21, i want to delete record 1. then someone else visits so its now 2 to 22, i want to delete record 2.

 

but thanx anyway

[kickstart]

Hi

 

Maybe

 

DELETE FROM somewhere WHERE id NOT IN (SELECT id FROM somewhere LIMIT 0,20)

 

Or

 

DELETE FROM somewhere WHERE id IN (SELECT id FROM somewhere LIMIT 20,2000000000)

 

All the best

 

Keith

[gevans]

MySQL is built for storing huge amounts of data very efficiently. thousands of rows arent a problem, it handles millions well also.

 

But if you do want to do that there's a couple of options, what's your table layout?

[andyd34]

I don't have the table layout as yet its just an additional feature i am adding but kickstart's suggestion looks promising.

 

I'll give that a go

 

Thanx for all your suggestions

[andyd34]

HI and thanks for all your replies. I have now sorted the problem and just thught i'd post the solution I came up with

 

$sql = mysql-query("SELECT * FRON table WHERE column = 'something' AND Usr_ID = 'myid' LIMIT 0,20 ORDER BY date DESC");
while($row=mysql_fetch_assoc($sql)) {
$found[] = $row['id'];
### RESULTS HERE ###
}
mysql_query("DELETE FROM table WHERE id NOT IN (".implode(', ', $found).") AND Usr_ID = 'myid'");

 

Well it worked for me just thought it may come in usefull to someone else