Page 1 of 1

keeping a limited number of log entries for each user

Posted: Fri Feb 02, 2007 7:26 pm
by titaniumdoughnut
I'm developing a massive project right now, and I've got a MySQL table for log entries. Basically, anything that "happens" to a user, like being tagged, uploading content, etc, is stored in this log. (Think of the Facebook news feed, but only for stuff related to you)

The log is then displayed on the user's home page to give them easy access (and remind them) of things that happened with their accounts recently.

I'm going to limit the log to the most recent 20 events. Each event is added to the log as a row in the table.

Of course, I could just let the table grow larger and larger, and use a LIMIT 20 in the MySQL query, but I'd like to somehow delete the oldest entry for a particular user at the same time as a new one is added. User's are identified in the row by the "who" column and ID is an auto-incremented unique id for each row, so pseudo-code would look like this:

Code: Select all

INSERT INTO `usernotes` ( blah blah who=$id)
IF there are > 20 rows where 'who'==$id then
DELETE FROM `usernotes` WHERE 'who'=$id AND 'ID'=the lowest 'ID' value for this 'who' value
Am I making any sense?

Can someone walk me through how this might be done in a MySQL query? I'd be fine just doing a couple MySQL calls and some PHP code, but I suspect there's a magic way to do this in one go.

Posted: Sat Feb 03, 2007 1:07 pm
by califdon
I'm not aware of any way to do everything in one SQL statement (btw, good explanation of what you are trying to do).

I'd probably try something like (I'm not 100% sure this will work):

Code: Select all

. . .
INSERT INTO `usernotes` . . . .
$SQL1 = "SELECT id FROM `usernotes` WHERE who = $user";
$result = mysql_query($SQL1);
if (mysql_num_rows($result) > 20) {
  for ($i=0; $i<=20; $i++) $row = mysql_fetch_assoc($result);
  while ($row) {
    extract($row);
    $SQL2 = "DELETE `usernotes` WHERE id = $id LIMIT 1";
    mysql_query($SQL2) or die("Delete failed");
    $row = mysql_fetch_assoc($result);
  }
}

Posted: Sat Feb 03, 2007 8:29 pm
by titaniumdoughnut
yeahhh.... that's kind of what I suspected. I think you're right.