It's working so far but if you see any glaring problems with it, do me the favor of posting here!
<?php
// This script runs like a service on the server, sending emails and marking them as sent in the database.
require_once "vendors/Swift.php";
require_once "vendors/Swift/Connection/SMTP.php";
require_once "config/database.php";
$db = new DATABASE_CONFIG;
$dbhost = $db->default['host'];
$dbuser = $db->default['login'];
$dbpass = $db->default['password'];
$dbdb = $db->default['database'];
if(strpos($_SERVER['OS'], 'Windows') !== FALSE) {
define('SERVER_SOFTWARE', 'windows');
define('SMTP_HOST', 'smtp.comcast.net');
} else if(strpos($_SERVER['OS'], 'Debian') !== FALSE) {
define('SERVER_SOFTWARE', 'linux');
define('SMTP_HOST', 'smtp.live.net');
}
// End settings //
// Set up an infinite loop.
while(1) {
// While there is no connection, show this message.
do {
sleep(10);
echo 'Trying to connect...' . "\n";
$mysql = mysql_connect($dbhost, $dbuser, $dbpass);
} while (!$mysql);
mysql_select_db($dbdb);
// If any part of the following loop fails, echo an error and use a 'break' statement.
// A 'break' statement causes a MySQL disconnect and reconnect... not sure whether this
// is necessary.
while(1) {
// SELECT the next 20 emails.
$results = mysql_query('
SELECT emails.*, email_recipients.recipient AS recipient, email_recipients.recipient_name AS recipient_name, email_recipients.id AS recipient_id
FROM emails, email_recipients
WHERE emails.id = email_recipients.email_id
AND email_recipients.sent = 0
ORDER BY created ASC LIMIT 20'
);
if(!$results) {
echo date("F j, Y, g:i a") . " SELECT query failed.\n";
break;
}
if(mysql_num_rows($results) == 0) {
echo date("F j, Y, g:i a") . " No emails to send right now.\n";
break;
}
// Loop through the emails, attempting to send each one.
// After each send, mark the email as sent with an SQL statement.
while($email = mysql_fetch_assoc($results)) {
try {
// Start Swift
$swift =& new Swift(new Swift_Connection_SMTP(SMTP_HOST));
} catch(Exception $e) {
echo date("F j, Y, g:i a") . " Exception thrown creating SMTP Connection for email #$email[id]\n";
continue;
}
// Create the message
$message =& new Swift_Message($email['subject'], $email['body']);
// Now check if Swift actually sends it
try {
$send = $swift->send($message, new Swift_Address($email['recipient'], $email['recipient_name']), new Swift_Address($email['sender'], $email['sender_name']));
if($send) {
echo "Sent email #" . $email['id'] . "\n";
// Mark email as sent. This could be optimized for performance by creating an array of sent emails and doing
// a bunch in a single query.
$mark_as_sent = mysql_query("
UPDATE email_recipients
SET email_recipients.sent = 1, email_recipients.date_sent = NOW()
WHERE email_recipients.id = $email[recipient_id]"
);
if($mark_as_sent == false) {
echo date("F j, Y, g:i a") . " Problem marking email #" . $email['id'] . " as sent!\n";
}
} else {
echo date("F j, Y, g:i a") . " Failed to send email #" . $email['id'] . "\n";
}
$swift->disconnect();
} catch(Exception $e) {
echo date("F j, Y, g:i a") . " Exception thrown for email #$email[id]\n";
$swift->disconnect();
continue;
}
}
mysql_free_result($results);
}
mysql_close($mysql);
}
?>
// This script runs like a service on the server, sending emails and marking them as sent in the database.
require_once "vendors/Swift.php";
require_once "vendors/Swift/Connection/SMTP.php";
require_once "config/database.php";
$db = new DATABASE_CONFIG;
$dbhost = $db->default['host'];
$dbuser = $db->default['login'];
$dbpass = $db->default['password'];
$dbdb = $db->default['database'];
if(strpos($_SERVER['OS'], 'Windows') !== FALSE) {
define('SERVER_SOFTWARE', 'windows');
define('SMTP_HOST', 'smtp.comcast.net');
} else if(strpos($_SERVER['OS'], 'Debian') !== FALSE) {
define('SERVER_SOFTWARE', 'linux');
define('SMTP_HOST', 'smtp.live.net');
}
// End settings //
// Set up an infinite loop.
while(1) {
// While there is no connection, show this message.
do {
sleep(10);
echo 'Trying to connect...' . "\n";
$mysql = mysql_connect($dbhost, $dbuser, $dbpass);
} while (!$mysql);
mysql_select_db($dbdb);
// If any part of the following loop fails, echo an error and use a 'break' statement.
// A 'break' statement causes a MySQL disconnect and reconnect... not sure whether this
// is necessary.
while(1) {
// SELECT the next 20 emails.
$results = mysql_query('
SELECT emails.*, email_recipients.recipient AS recipient, email_recipients.recipient_name AS recipient_name, email_recipients.id AS recipient_id
FROM emails, email_recipients
WHERE emails.id = email_recipients.email_id
AND email_recipients.sent = 0
ORDER BY created ASC LIMIT 20'
);
if(!$results) {
echo date("F j, Y, g:i a") . " SELECT query failed.\n";
break;
}
if(mysql_num_rows($results) == 0) {
echo date("F j, Y, g:i a") . " No emails to send right now.\n";
break;
}
// Loop through the emails, attempting to send each one.
// After each send, mark the email as sent with an SQL statement.
while($email = mysql_fetch_assoc($results)) {
try {
// Start Swift
$swift =& new Swift(new Swift_Connection_SMTP(SMTP_HOST));
} catch(Exception $e) {
echo date("F j, Y, g:i a") . " Exception thrown creating SMTP Connection for email #$email[id]\n";
continue;
}
// Create the message
$message =& new Swift_Message($email['subject'], $email['body']);
// Now check if Swift actually sends it
try {
$send = $swift->send($message, new Swift_Address($email['recipient'], $email['recipient_name']), new Swift_Address($email['sender'], $email['sender_name']));
if($send) {
echo "Sent email #" . $email['id'] . "\n";
// Mark email as sent. This could be optimized for performance by creating an array of sent emails and doing
// a bunch in a single query.
$mark_as_sent = mysql_query("
UPDATE email_recipients
SET email_recipients.sent = 1, email_recipients.date_sent = NOW()
WHERE email_recipients.id = $email[recipient_id]"
);
if($mark_as_sent == false) {
echo date("F j, Y, g:i a") . " Problem marking email #" . $email['id'] . " as sent!\n";
}
} else {
echo date("F j, Y, g:i a") . " Failed to send email #" . $email['id'] . "\n";
}
$swift->disconnect();
} catch(Exception $e) {
echo date("F j, Y, g:i a") . " Exception thrown for email #$email[id]\n";
$swift->disconnect();
continue;
}
}
mysql_free_result($results);
}
mysql_close($mysql);
}
?>
Table DDL's:
CREATE TABLE `emails` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`sender` varchar(255) NOT NULL,
`sender_name` varchar(255) NOT NULL,
`subject` varchar(255) NOT NULL,
`body` text NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`)
CREATE TABLE `email_recipients` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`email_id` int(10) UNSIGNED NOT NULL,
`recipient` varchar(255) NOT NULL,
`recipient_name` varchar(255) NOT NULL,
`sent` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`date_sent` datetime NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`)
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`sender` varchar(255) NOT NULL,
`sender_name` varchar(255) NOT NULL,
`subject` varchar(255) NOT NULL,
`body` text NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`)
CREATE TABLE `email_recipients` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`email_id` int(10) UNSIGNED NOT NULL,
`recipient` varchar(255) NOT NULL,
`recipient_name` varchar(255) NOT NULL,
`sent` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',
`date_sent` datetime NOT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`id`)
I never actually posted my version. We both approach it with the same logic, except I think I run less queries and mine works when you run it on a farm of servers simultaneously (each server "locks" the emails it's dealing with so no two servers cause duplicates). We needed that because we run a server farm.
