Some time ago I talked about a way to “throttle” down email queues to try and prevent being blacklisted for sending hundred’s of legit emails in one batch. The process which can be seen in more depth here released emails over a period of time in batches of 50 to try and reduce the load.
It has been some time since that and I have seen a massive reduction in those accounts being blacklisted for SPAM which is a HUGE success (and relief). Since that time though I have been thinking about the ability to add email alerting into My Book It and how that would look.
What I want to avoid is having each site sending out emails all over the place, the method needs to be controlled in a manner that it is reliable and efficient.
With all this in mind I have made some changes to the way the process works. Firstly email will flow from the admin panel and not directly from the sub domains. (i.e. if a site wants to send email, it will submit it to a central queue for processing). This will be fine except that some emails need to be sent before others (i.e. an alert for an upcoming class or change needs to go before a general newsletter)
So I have updated my Database table to look like.
-- -- Table structure for table `email_queue` -- CREATE TABLE IF NOT EXISTS `email_queue` ( `id` int(5) NOT NULL AUTO_INCREMENT, `submission_date` varchar(50) NOT NULL, `sender` varchar(250) NOT NULL, `recipient` varchar(250) NOT NULL, `subject` varchar(250) NOT NULL, `message` text NOT NULL, `headers` text NOT NULL, `status` varchar(50) NOT NULL, `sent_date` varchar(50) NOT NULL, `sent_by` varchar(50) NOT NULL, `do_not_send_before` varchar(50) NOT NULL, `priority` int(5) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
Lets go through the columns and what they do.
- ID – Primary key
- Submission_date – Date the email was submitted to the queue, mostly for auditing
- Sender – The email address that the account wants attached to the sender of the email as they will be coming in from all different addresses
- Recipient – The recipient of the email
- Subject – Subject of the email
- Message – The body of the email
- Headers – This is actually no longer required
- Status – Either queued or sent
- Sent_date – The date the message was sent – Again for auditing
- Sent_by – A record of what account sent the message, for auditing
- do_not_send_before – This is so emails can be queued for future sending, this ties in with the new workflow manager
- priority – A numeric priority for the emails to allow messages to be bumped up the queue.
Now that we have all of those fields I have also added two Settings to the Global Settings
- Email Running – This is a yes or no option to stop mail from sending if required
- Email Batch – A number of how many emails will be sent in one go
The mailer.php has also been updated to use PHPMailer rather then mail as I want to send all messages out via a mail server in the network to take any load off the webserver itself. (Plus it will allow me to send out via different IP addresses in the future.
So the script now looks like
<?
/* Include database information.*/
include("inc/config.php");
/* Load PHPMailer */
require("phpmailer/PHPMailerAutoloader.php");
/*
* Mailer Script.
* This script will retrieve queued messages from the database and send them in batches.
*/
/* Variables */
$process_count = $site_settings['email_batch']; //The number of messages to process in one batch
//Check if mail enabled
if($site_settings['email_running'] == "y") {
$mail = new PHPMailer();
$mail->IsSMTP(); // telling the class to use SMTP
/* Now we get the queued emails from the database. Order by oldest first. */
$sql = "SELECT * FROM `email_queue` WHERE `status` = 'queued' AND `do_not_send_before` < '" .strtotime("now") ."'
ORDER BY `submission_date`, `priority` ASC LIMIT $process_count ";
$results = mysql_query($sql)
or die($error = "Error getting queued messages.");
/* For each result that is extracted from the database. Attempt to send the email. */
while($row = mysql_fetch_array($results)){
$mail->Host = "mailserver.local.address"; // SMTP server
$mail->setFrom($row['sender'],'$row['sender']');
$mail->AddReplyTo($row['sender'],'$row['sender']');
$mail->AddAddress($row['recipient']);
$mail->Subject = stripslashes($row['subject']);
$mail->Body = stripslashes($row['message']);
$mail->WordWrap = 50;
if(!$mail->Send()) {
$error = 'Message was not sent.<br />Mailer error: ' . $mail->ErrorInfo;
} else {
/* If successful generate the SQL to update the database. */
$sql = "UPDATE `email_queue` SET `status` = 'sent', `sent_date` = '" .strtotime("now") ."' WHERE `id` = '" .$row['id'] ."' ";
$results2 = mysql_query($sql)
or die($error = "Error updating holding table. " .mysql_error());
}
} //while getting results
/* Display progress*/
if(isset($error)) {
echo $error; //if the error variable is set, display the message
} else {
echo "OK.";
} //if else display status
} //if mail enabled
?>
And that is it, the mailer is now ready to start accepting messages from other sub domains. (more on how that happens later)