Today I added a new datum for the Users table for our mail server: Last Message Received
What prompted me to add this was I was trying to prune down the over 150 accounts we have in the macserve.net domain and I had no idea which email addresses were actually in use or when they last received an email. I needed a quick reference that would not necessitate a trip to the Recent Mail table.
So I added two new columns to the Users table: last msg recvd and last msg sent. For now I’m only dealing with the former as I haven’t implemented tracking of sent mail yet.
The big thing was figuring out a quick and easy way of getting the most recent datetime for a user from the Recent Mail table and updating that in the Users table.
I started writing a separate script for this, but realized it would be just fine to drop this process into our daily database maintenance script. I had started to write some PHP code that would loop through the Recent Mail table for entries at most a week old and figure out the most recent message and then make a call to update that record in the Users table.
In the course of creating a temporary table to go back to the archive of recent mail we keep, I realized I could simply use an SQL temporary table to hold that data (duh) and then simply run a joined update from that temp table into the Users table. It turned out to be a simple 3 statement SQL process like so:
CREATE TEMPORARY TABLE most_recent_email
SELECT recipient_id,MAX(recent_msgs.msg_when) as last_msg_when
FROM recent_msgs
WHERE (msg_when >= ‘$oneWeekAgo’) AND (recent_msgs.recipient_id > 0)
GROUP BY recent_msgs.recipient_id;
UPDATE site_users,most_recent_mail
SET last_msg_rcvd = last_msg_when
WHERE site_users.user_id = most_recent_email.recipient_id;
DROP TABLE most_recent_email;
Nice and simple and I let the database do all the work for me. I like it.