keeping a limited number of log entries for each user

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
titaniumdoughnut
Forum Commoner
Posts: 33
Joined: Wed Jul 13, 2005 2:02 pm
Location: Manhattan

keeping a limited number of log entries for each user

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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);
  }
}
User avatar
titaniumdoughnut
Forum Commoner
Posts: 33
Joined: Wed Jul 13, 2005 2:02 pm
Location: Manhattan

Post by titaniumdoughnut »

yeahhh.... that's kind of what I suspected. I think you're right.
Post Reply