Creating an Mass Email Queue using SQL, PHP, Shell and Cron

I recently ran across an issue where emails were being rejected from one server as it did not resolve reverse DNS. It was sending email alerts for certain events and we had no way to tell if mails were failing as spam filters were dumping them into a black hole somewhere. The solution was to modify our system to publish alert emails to a database table instead, then have a separate application check for unsent mail in the database and send it using SMTP authentication from our standard mail server. This is likely a common need for various systems so I thought I’d share the design/source to help others.
Download PHP5 from here

1. Creating the database table to store emails included a handler for max attempts, attempts count, success [1|0] and timestamps. See SQL code below to create the table


CREATE TABLE email_queue ( 
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY , from_name VARCHAR( 64 ) NULL ,        
from_emailVARCHAR( 128 ) NOT NULL , to_email VARCHAR( 128 ) NOT NULL , subject  
VARCHAR( 255 ) NOT NULL , message TEXT NOT NULL , max_attempts INT NOT NULL DEFAULT ‘3′,  attempts INT NOT NULL DEFAULT ‘0′, success TINYINT( 1 ) NOT NULL DEFAULT ‘0′,    date_published DATETIME NULL , last_attemptDATETIME NULL , date_sent DATETIME NULL , INDEX ( to_email) );

2. Publishing mails to the database is on a per-case basis. Given our systems all called a helper class method (they are Java) sendEmail(params), we simply modified that class to write to the database instead - no major tweaks to code in various areas, just one method - benefit of well-designed OO code. As such, we’ll anticipate that however you are sending email now, you will know where to create the INSERT query to publish the to, from, subject, body, date_published into the database.
3. Writing a Mail Queue processor is fairly simple and it should retrieve unsent emails, try to send them, and update the record based on success/failure. As you can see from the database design, I also included max_attempts, attempts, success which allow us to control how many times the mail delivery should be attempted before flagging it as failed (success=0 and max_attempts = attempts). See the PHP script below that is run by a shell script and cron job at regular intervals to process the queue.


Note: I have used here is PEAR MAIL which is already installed with PHP. If not Please install it first.
Typically, in particular with PHP 4 or later, this will have already been done for you. Just give it a try. Adapt the example below for your needs. 
Make sure you change the following variables at least:
 

from: the email address from which you want the message to be sent.
to: the recipient's email address and name.
host: your outgoing SMTP server name.
username: the SMTP user name (typically the same as the user name used to retrieve mail).
password : the password for SMTP authentication. 


Lastly, after I already built this I see that PEAR has a MailQueue feature. I didn’t research it as this meets our needs but the design is most important as this can translate into any language. Enjoy!

<?php
/* PHP email queue processing script */
require_once "Mail.php";

// defile variables
$db_url = "localhost";
$db_name = "DB_NAME";
$db_user = "DB_USER";
$db_pass = "DB_PASS";

$host = "MAIL_SERVER";
$username = "MAIL_USER";
$password = "MAIL_PASS";

$max_emails_per_batch = 50;

// connect to database
$link = mysql_connect($db_url, $db_user, $db_pass);
if (!$link) {
die('Could not receive connection: ' . mysql_error());
}
if (!mysql_select_db($db_name, $link)) {
die('Could not connect to db: ' . mysql_error());
}

// query email_queue for records where success = 0
$sql    = "SELECT * FROM email_queue 

           WHERE success = 0 

AND max_attempts != attempts 

LIMIT " . $max_emails_per_batch;
$result = mysql_query($sql, $link);

if (!$result) {
echo "DB Error, could not query the databasen";
echo 'MySQL Error: ' . mysql_error();
exit;
}

// check if records found
if (mysql_num_rows( $result )) {

// prepare mailer
$smtp = Mail::factory('smtp',
array ('host' => $host,
‘auth’ => true,
‘username’ => $username,
‘password’ => $password));

// loop through records to send emails
while ($queued_mail = mysql_fetch_array($result)) {
// send email

$to =              $queued_mail[’to_email’];
$subject =   $queued_mail[’subject’];
$body =      $queued_mail[’message’];
$from =      $queued_mail[’from_name’] . ‘ < ' 

. $queued_mail['from_email'] . '>‘;

$headers = array (’From’ => $from,
‘To’ => $to,
‘Subject’ => $subject);

$mail = $smtp->send($to, $headers, $body);

if (PEAR::isError($mail)) {
// else update attempts, last attempt
$sql = “UPDATE email_queue SET ” .
“attempts = attempts+1, ” .
“last_attempt = now() ” .
“WHERE id = ‘” . $queued_mail[’id’] . “‘”;
mysql_query($sql, $link);

echo( $mail->getMessage() );
} else {
// if successful, update attempts, success, last attempt, date_sent
$sql = “UPDATE email_queue SET ” .
“attempts = attempts+1, ” .
“success = ‘1′, ” .
“last_attempt = now(), ” .
“date_sent = now() ” .
“WHERE id = ‘” . $queued_mail[’id’] . “‘”;
mysql_query($sql, $link);

echo(”Message successfully sent!”);
}
} // end while (loop through records and sending emails)
} // no rows so quit

// release resources
mysql_free_result($result);
mysql_close($link);
?>

4. Writing shell scripts can vary depending on your environment. I chose a very simple approach but some people like to include loops and system checks. This script simply executes the PHP script every 8 seconds for approximately a minute. The reason I chose a minute is because it was the smallest interval I could use to schedule cron. I chose 8 seconds instead of 10 given CPU time and delays when processing the script. It’s not exact but we didn’t need precision, just reliability.




#process PHP script approximately every 10 seconds (including allowance for CPU delay)
/usr/bin/php is path where your php is installed on linux.

/usr/bin/php -f /path/to/script/process_queue.php
sleep 8
/usr/bin/php -f /path/to/script/process_queue.php
sleep 8
/usr/bin/php -f /path/to/script/process_queue.php
sleep 8
/usr/bin/php -f /path/to/script/process_queue.php
sleep 8
/usr/bin/php -f /path/to/script/process_queue.php
sleep 8
/usr/bin/php -f /path/to/script/process_queue.php
5. Creating cron job to execute the shell script every minute is fairly painless. When logged in using a shell client to your server, simply add a cronjob. I use VI editor although a lot of people write instructions using Pico. The commands for VI are as follows:


crontab -e (opens vi editor window)
i (changes to insert mode - assuming no other lines, other wise move to last line and hit o for new line)
*/1 * * * * /path/to/script/process_queue.sh
:wq (colon, w, q, enter will write then quit and update your crontable)

Summary
The above will allow you to build a useful email queue that can handle a variety of needs. If you have troubles sending email from a particular server, you can run your program on another box that does have reverse DNS PTR records and hopefully avoid spam filters/firewall issues.