Today I added a new datum for the Users table for our mail server: Last Message Received
\nWhat 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.
\nSo 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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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:
\nCREATE TEMPORARY TABLE most_recent_email
\nSELECT recipient_id,MAX(recent_msgs.msg_when) as last_msg_when
\nFROM recent_msgs
\nWHERE (msg_when >= ‘$oneWeekAgo’) AND (recent_msgs.recipient_id > 0)
\nGROUP BY recent_msgs.recipient_id;<\/p>\n
UPDATE site_users,most_recent_mail
\nSET last_msg_rcvd = last_msg_when
\nWHERE site_users.user_id = most_recent_email.recipient_id;<\/p>\n
DROP TABLE most_recent_email;<\/p>\n
Nice and simple and I let the database do all the work for me. I like it.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n