Record not more that 10 entries per user id

[levani]

I have a simple shout box script on my site and only registered users can add messages. The text of the message together with the user id and date is stored in database. The shout box displays only 10 newest messages but I found that the old entries aren't deleted from database. How can I left only 10 newest messages per user id and delete other records?

Can anyone please help?

[Fumigator]

Off the cuff I'd use LIMIT 10,9999 sorting by that datetime. But you can only do one user at a time with this kind of query. If you're using PHP or some other language anyway, this is not an issue-- just select all your user IDs, run them through a loop to apply the delete query to each user one at a time.

The delete query looks like this:


DELETE FROM table1
WHERE user_id = 12345
ORDER BY posting_datetime DESC
LIMIT 10,9999


(edit: I said LIMIT 11,9999 but it starts at 0 so it's really LIMIT 10,9999)

[Fumigator]

Well I just tried my query and it doesn't work, so... back to the drawing board :D

[SKDevelopment]

Maybe something like this then:

DELETE FROM table1
WHERE user_id = 12345
AND rec_id NOT IN (SELECT rec_id FROM table1 WHERE user_id = 12345 ORDER BY posting_datetime DESC LIMIT 10)

user_id - user ID
rec_id - unique record ID - Primary Key of table1. It is important rec_id to be a Primary Key or to be unique and not null. "NOT IN" will work absolutely wrong if rec_id can contain NULL values.

[levani]

Maybe something like this then:

DELETE FROM table1
WHERE user_id = 12345
AND rec_id NOT IN (SELECT rec_id FROM table1 WHERE user_id = 12345 ORDER BY posting_datetime DESC LIMIT 10)

user_id - user ID
rec_id - unique record ID - Primary Key of table1. It is important rec_id to be a Primary Key or to be unique and not null. "NOT IN" will work absolutely wrong if rec_id can contain NULL values.

Well, this may be useful to delete already recorded entries, but how can I prevent it in future as well?

[SKDevelopment]

Just run the DELETE statement each time the user submits a new post (after INSERT query). Or you could do it with some probability - in this case it would work as randomly run garbage collector.

[levani]

Ok. And one more question, this code would work only for one user id, won't it? There are too many users registered on my site to run the code manually for each. How can I delete the entries for all the users at once?

[Fumigator]

Your script is coded in PHP? Select all user IDs, loop through them, plugging the user ID into the DELETE query each time through the loop.

[levani]

Yes, my script is coded in php. Can anyone please provide an example of the code?

[Fumigator]

Select all users...


$query = "SELECT user_id FROM user_table";
$result = mysql_query($query);
if (!$result) {
die("Query error: $query<br />".mysql_error());
}


Loop through them...


while ($user = mysql_fetch_assoc($result)) {
//this code is in the next step
}


plug user ID into DELETE query.


$delquery = "DELETE blah blah WHERE user_id = {$user['user_id']} blah blah";
$delresult = mysql_query($delquery);
if (!$delresult) {
die("Query error: $query<br />".mysql_error());
}

[PHP6]

Just the way how I would do deletion:
DELETE FROM posts USING posts, (SELECT id FROM posts WHERE user_id=12345 ORDER BY posting_date DESC LIMIT 10,9999) as tmp1 WHERE posts.id=tmp1.id

You need to select only those users who have more than 10 posts:
SELECT user_id FROM (SELECT user_id, count(*) as amount FROM posts GROUP BY user_id) as tmp1 WHERE tmp1.amount > 10

p.s. and there should be a way how to do all these operations in one query, of cause it will be long enough and hard to understand but still, it exists :)