Checking Email with PHP

Russell J.T. Dyer

As a consultant I'm often away from my office and will miss email from clients. With Web access to my email server, I can read and send mail; however, I don't have time to keep checking email when I'm working. Instead, I've written a program in PHP that checks email for me. I have cron run it every 15 minutes. If there's a message, PHP sends a text message to my cellular telephone letting me know. In this article, I will review this PHP script as a learning opportunity.

Mail Log in MySQL

To keep track of client email addresses and to prevent PHP from notifying me more than once for a message, I set up a couple of tables in MySQL. The first table contains client names and email addresses. From the mysql client program, I entered the following:

   CREATE DATABASE mail_log;

   USE mail_log;

   CREATE TABLE email_addresses
   (address_id INT AUTO_INCREMENT PRIMARY KEY,
   email_address CHAR(100) UNIQUE, name CHAR(100));

The first two lines create the database mail_log in MySQL and switches the session to it. It's not necessary to create a separate database, though. You could use an existing one instead. The next set of lines creates the table containing client information. The first column is an automatically incremented key field that will be referenced in queries along with the next table, which will contain a log of email. The second column will hold email addresses. Notice that I've required data in this column to be unique; duplicates are not allowed. The last column will hold the client's name. I've set all of these columns to fixed-width columns (no VARCHAR columns) so that the rows will be fixed. This will help speed up queries (not that it matters very much for a script run by cron, but it's a good habit to maintain). I could create a PHP script to enter data in this table through a Web interface, but to keep this article focused, I'll manually enter the email addresses of my few clients like so:

   INSERT INTO email_addresses
   (email_address, name)
   VALUES('russell@dyerhouse.com', 'Russell Dyer');

With the email address table in place, look at the table for logging in email:

   CREATE TABLE log
   (log_id INT AUTO_INCREMENT PRIMARY KEY,
   address_id INT NOT NULL, notified ENUM('y','n'),
   log_date DATETIME, subject CHAR(100),
   message CHAR(100),
   UNIQUE(address_id, log_date));

In this SQL statement, I'm creating the table log with another automatically incremented record identifier (i.e., log_id). This will be useful when updating rows later. The next column will contain the address_id number from the email_addresses table. To keep track of whether PHP has already notified me about an email message, I set up the column notified. I've enumerated the only possible answers to optimize queries.

Next is a column to record the date of each email message, followed by the subject and the first 100 characters of the message. On my cellular telephone, text messages are limited to only 100 characters. I end the SQL statement above with instructions for MySQL to create an index of both address_id and log_date. This will not only speed up queries, but it will also prevent a message from being recorded twice. If PHP attempts to insert data into log for an email address in which there already exists a related record with the exact same date and time, then the query will be rejected. That's all of the tables that are needed in MySQL. The next section introduces the PHP script that will check for mail sitting on the server.

Logging Mail with PHP

There are some nice functions for PHP that make examining a POP3 mailbox pretty easy. However, on my server, which runs with Red Hat Linux, I had to download an rpm to add the functions that I needed:

php-imap-xxxx-i386.rpm

With the imap functions in PHP, it can check the mail waiting for me on my email server.

#!/usr/bin/php -q

<?php
$mail_svr = 'mail.dyerhouse.com';
$to = 'russ_cell@dyerhouse.com';
$mail_box =
imap_open("{$mail_svr:110/pop3/notls}INBOX",'russell','password');

$headers = imap_headers($mail_box);
$msg_count = count($headers);

The first two lines start the PHP script. Next, PHP sets up two variables: one contains the address of the mail server, the other contains the email address of my cell phone. If my email server were on the same server, mail.dyerhouse.com could be replaced with localhost. The email address is an alias that I entered in /etc/aliases, which translates to the lengthy numeric address for my cellular telephone.

On the next line of code, I'm using the imap_open() function to open my inbox. Because I'm using POP3, I list port 110 and the notation about pop3. For IMAP servers, use port 143 and just leave off the forward-slash along with the pop3. The notation about notls is necessary because I'm using Red Hat Linux, but may not be required for your server. With a connection to the mailbox established, the next line retrieves an array of email headers using the imap_headers() function so that the number of messages may be determined. This will be useful in a for statement that is to follow in which PHP will loop through all of the messages. Before doing that, though, PHP needs to get a list of client email addresses from MySQL:

$link = mysql_connect('localhost', 'russell', 'password')
        or die("Couldn't connect to MySQL" . mysql_error() . "\n");
mysql_select_db('mail_log', $link);

$sql_stmnt = "SELECT address_id, email_address  
              FROM email_addresses";

$results = mysql_query($sql_stmnt)
           or die("Couldn't query server" . mysql_error() . "\n");

while ($row = mysql_fetch_assoc($results)){
   $addresses[$row['email_address']] = $row['address_id'];
}

After connecting to the MySQL server, PHP switches the session to the database mail_log using the mysql_select_db() function. Using the function mysql_query(), PHP queries the database with the SQL statement contained in $sql_stmnt to select client identifiers and their email addresses. Utilizing a while statement and the mysql_fetch_assoc() function, PHP loops through each row found in the email_addresses table, per the SQL statement. With each pass through the while statement, the results from each row is added to an associative array ($addresses[]) that will be referred to later when PHP looks through each email message. The syntax to extract an element using mysql_fetch_assoc() is to simply give the field name as the key, as shown above.

for ($c = 1; $c <= $msg_count; $c++) {
   $headerinfo = imap_headerinfo($mail, $c);
   $unixtime = $headerinfo->udate;
   $sender_username = $headerinfo->from[0]->mailbox;
   $sender_host = $headerinfo->from[0]->host;
   $sender_address = "$sender_username@$sender_host";
   $subject = $headerinfo->subject;
   $message = substr(imap_body($mail, $c), 0, 100);

In the first line here, the for statement mentioned previously is started. Before discussing its components, notice that the closing curly-brace that would end this statement does not appear above. It shows up later in the script. At the start of the for statement, PHP sets a counter ($c) to keep track of which email message is being processed (1 is first). The for statement will last as long as the count does not exceed the number of messages determined earlier ($msg_count).

The first set of lines in the statement block use the imap_headerinfo() function to retrieve various bits of data (e.g., sender, time, and subject) from the mailbox. The function returns an object and therefore requires the syntax that you see here to extract the values of each attribute of the object. Notice that the email address comes in pieces: the user's name is separated from the domain name. As a result, PHP has to join them.

In the last line above, PHP uses the function imap_body() to get the body of the message. Using the substr() function, PHP saves only the first 100 characters. Incidentally, if you're not the only person who has access to your MySQL server, you might not want to save even part of your messages. At a minimum, I have PHP delete the message from MySQL after emailing it to my cellular telephone.

Now that PHP has the salient bits of information from the message, it needs to check the email address against the associative array set up previously, which contains the client addresses. This is done with an if statement as follows:

   if($address_id = $addresses["$sender_address"]) {
      $sql_stmnt = "INSERT IGNORE INTO log
                    (address_id, notified, log_date,
                     subject, message)
                    VALUES('$address_id', 'n',
                    FROM_UNIXTIME($unixtime),
                    '$subject', '$message')";
      mysql_query($sql_stmnt);
   }
}
imap_close($mail);

The condition of the if statement here attempts to extract the address_id from the associative array $addresses[] based on the sender's email address. If it's successful, if the email message being examined is from an email address found in the email_addresses table, then it attempts to make an entry in the log table. The flag IGNORE is added so that MySQL will ignore any error messages that will be generated by attempting to insert data that's not unique (i.e., with the same address_id and log_date).

Using the FROM_UNIXTIME() function, MySQL converts the Unix time to the datetime column type. The next line of code invokes the SQL statement and the second closing curly-brace ends the for statement started earlier. This allows PHP to close the connection to the mailbox with the imap_close() function.

Now that the log has been updated for the new messages, PHP needs to figure out which ones to notify me about:

$sql_stmnt = "SELECT log_id, name, subject, message,
              DATE_FORMAT(log_date, '%b %d') AS date
              FROM log, email_addresses
              WHERE log.address_id = email_addresses.address_id
              AND notified = 'n'";

$results = mysql_query($sql_stmnt);

while($row = mysql_fetch_assoc($results)) {
   $log_id = $row["log_id"];
   $name = $row["name"];
   $subject = $row["subject"];
   $message = $row["message"];
   $date = $row["date"];

   $sql_stmnt = "UPDATE log
                 SET notified = 'y', message = ''
                 WHERE log_id = '$log_id'";
   mysql_query($sql_stmnt);

   mail($to, $subject, "$name ($date)\n$message");
}

mysql_close();
?>

Looking at the SQL statement above, MySQL retrieves the data needed to construct an email message to send my cellular telephone. It will only select rows where the column notified is equal to 'n', indicating that I have not been notified yet. Using the built-in MySQL function DATE_FORMAT(), MySQL gives me a shorter, simpler date entry (e.g., "Oct 15"). The while statement that follows uses the mysql_fetch_assoc() function to extract fields from the results set and to place the values in the appropriate variables.

With each pass through the while statement, MySQL is updating the table log to set the notified column to 'y', indicating that I've been notified. MySQL also erases the contents of the message column to maintain some privacy. The message is only needed for the duration of this script. There is the possibility that the email might not be sent successfully and PHP won't know to retry. However, transaction safety is not all that important for the use made of this script.

In the third from the last line of code above, PHP uses the mail() function to send an email informing me that I have a message from a client or friend waiting. The message is sent to the address contained in the variable $to, which was set up in the beginning of the script. The subject from the original email is used for this notice, as well. For the third argument of the mail() function, PHP will put together the sender's name and the date of the message on one line. Using \n, a line-break is added before the message. With each loop through the while statement, for each new client message, PHP will send me a text message. I could combine multiple message notices into one, but I only have 100 characters and I want to conserve the space by doing separate messages.

The closing curly-brace above ends the while statement. Since the script is ending, the connection to MySQL is closed using the mysql_close() function.

Final Thoughts

I think this script is very handy. It allows me to stay focused on my work and still be responsive to client emails. If you set up a script like this on your server, you may want to configure cron to run it more often, maybe every five minutes. Don't schedule it to run too frequently or it may sometimes notify you of messages even when you're at your desk with your email client program running. Incidentally, you will circumvent the script if you leave your email client running while you're away from your office. Finally, for tidiness, you may want to write a short script that cron runs once a week to delete rows in the log table from the previous week.

Russell Dyer is a Perl programmer, a MySQL developer, and a Web designer living and working on a consulting basis in New Orleans. He can be reached at: russell@dyerhouse.com.