keeping a limited number of log entries for each user
Posted: Fri Feb 02, 2007 7:26 pm
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:
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.
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' valueCan 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.