[SOLVED] Using Swiftmailer to send bulk messages with CRON
Moderators: Chris Corbyn, General Moderators
Hey,
So you're saying every minute, to pull the 100 messages from user_message and if their message_id's are the same to send out those in a batch, and if the message_id changes, start a second batch? (since the body's of the messages in each batch are different)?
Would I throw in AntiFlood in there as well? Or is that not needed for <100?
Thanks again.
So you're saying every minute, to pull the 100 messages from user_message and if their message_id's are the same to send out those in a batch, and if the message_id changes, start a second batch? (since the body's of the messages in each batch are different)?
Would I throw in AntiFlood in there as well? Or is that not needed for <100?
Thanks again.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
My pleasure... Wish it was more...You've really been a huge help... I was just reading your itinerary. Looks awesome. Its reminding me I gotta get of my ass and so something besides sit here all day... You'd think I'd be 300lbs with how inactive I am lately...d11wtq wrote:I just wanted to say thanks for the donationGood luck getting the script working.
Sounds like an awesome trip. Have a great time.
OK a couple of quick questions.. sorry...
1. How do I mark each message in the queue as sent if I only get a response at the end of the batch about failed attempts? do I re-loop through them afterwards and check against the failed array?
2. Should I do anything special when switching batches? Clear something or reset something? Right now I have this: Untested and ugly... not the best programmer as you'll see
Thanks again...
1. How do I mark each message in the queue as sent if I only get a response at the end of the batch about failed attempts? do I re-loop through them afterwards and check against the failed array?
2. Should I do anything special when switching batches? Clear something or reset something? Right now I have this: Untested and ugly... not the best programmer as you'll see
Code: Select all
// make SMTP connection
$swift =& new Swift(new Swift_Connection_SMTP("localhost"));
for ($i = 0; $i < $num; $i++) {
$id = mysql_result($result, $i, "id");
$msg_id = mysql_result($result, $i, "msg_id");
$to_id = mysql_result($result, $i, "to_id");
$to_email = mysql_result($result, $i, "to_email");
$to_name = mysql_result($result, $i, "to_name");
$from_id = mysql_result($result, $i, "from_id");
$from_email = mysql_result($result, $i, "from_email");
$from_name = mysql_result($result, $i, "from_name");
$alert = mysql_result($result, $i, "m_message");
if ($i<$num-1) { // there are more after this
$next_id = mysql_result($result, $i+1, "msg_id");
} else { // last one
$next_id = false;
}
// build message
$message =& new Swift_Message("", $alert);
if($i==0) { // first message, create recipient list
$recipients =& new Swift_RecipientList();
}
if ($msg_id != $next_id) {
// must be the end of a batch, add this one, send it out and create new batch if more
$recipients->addTo($to_email, $to_name);
$batch =& new Swift_BatchMailer($swift);
$batch->send($message, $recipients, new Swift_Address($from_email, $from_name));
$failures = $batch->getFailedRecipients(); //an array of addresses that *didn't* send
if($i != num-1) { // not the last one, start new batch
$recipients =& new Swift_RecipientList();
}
} else {
// we're still wtihin a batch... add it and continue
$recipients->addTo($to_email, $to_name);
}
}Thanks again...
Ok I made a bit more progress, I'm not sure how to test failedrecipients however. I tried turning off my smtp server but that just did nothing. While I'm sure its not, is this an efficient way to do this? I mark each message status in the DB to 1 as I go though, then if failure is found at the end of the batch I cycle through the array $result up to what was already sent to find the message id and set it to -1. What might cause a failure to add the message to the queue? Load limit or crash of some sort?
THANKS
THANKS
Code: Select all
$endBatch = true;
// make SMTP connection
$swift =& new Swift(new Swift_Connection_SMTP("localhost"));
for ($i = 0; $i < $num; $i++) {
$id = mysql_result($result, $i, "id");
$msg_id = mysql_result($result, $i, "msg_id");
$to_id = mysql_result($result, $i, "to_id");
$to_email = mysql_result($result, $i, "to_email");
$to_name = mysql_result($result, $i, "to_name");
$from_id = mysql_result($result, $i, "from_id");
$from_email = mysql_result($result, $i, "from_email");
$from_name = mysql_result($result, $i, "from_name");
$alert = mysql_result($result, $i, "m_message");
if ($i<$num-1) { // there are more after this
$next_id = mysql_result($result, $i+1, "msg_id");
} else { // last one
$next_id = false;
}
if ($endBatch == true)
$message =& new Swift_Message("", $alert);
if($i==0) { // first message, create recipient list
$recipients =& new Swift_RecipientList();
}
// update queued message status to 1
$queryUpdate = sprintf("UPDATE myQueue SET status = 1 WHERE id = %d",
quote_smart($id));
$resultUpdate = mysql_query($queryUpdate, $connection) or die(mysql_error());
if ($msg_id != $next_id) {
// must be the end of a batch, add this one, send it out and create new batch if more
$recipients->addTo($to_email, $to_name);
$batch =& new Swift_BatchMailer($swift);
$batch->send($message, $recipients, new Swift_Address($from_email, $from_name));
$failures = $batch->getFailedRecipients(); //an array of addresses that *didn't* send
$endBatch = true;
if($i != $num-1) { // not the last one, start new batch
$recipients =& new Swift_RecipientList();
}
} else {
// we're still within a batch... add it and continue
$recipients->addTo($to_email, $to_name);
$endBatch = false;
}
if ($endBatch == true) {
foreach($failures as $failed) {
for ($j = 0; $j < $i; $j++) { // hasn't gotten past $i so don't go through all of them
$testID = mysql_result($result, $j, "id");
$testTo = mysql_result($result, $j, "to_email");
if($failed == $toTest) {
$query2 = sprintf("UPDATE myQueue SET status = -1 WHERE id = %d",
quote_smart($testID));
$resultUpdate = mysql_query($query2, $connection) or die(mysql_error());
}
}
}
$batch->flushFailedRecipients();
}
}
Last edited by fishnyc22 on Tue Jun 05, 2007 5:47 pm, edited 1 time in total.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Here's how I'd do it (untested). This assumes one run of the script will process maximum 100 emails.
EDIT | Added reference operators for PHP4.
EDIT 2| On hindisght my table names are crap. "message" and "message_recipient" would be far more self-explanatory
Code: Select all
CREATE TABLE message (
id int auto_increment PRIMARY KEY,
subject varchar(255),
from_email varchar(255),
from_name varchar(255),
body text
)
CREATE TABLE user_message (
id int auto_increment PRIMARY KEY,
message_id int not null,
to_email varchar(255),
to_name varchar(255),
#Unset, Sent, Failed
status enum('U', 'S', 'F'),
FOREIGN KEY(message_id) REFERENCES message(id)
)Code: Select all
$swift =& new Swift(new Swift_Connection_SMTP("localhost"));
$max_size = 100;
while ($max_size > 0)
{
$sql = "
SELECT
user_message.id,
message.subject,
message.body,
message.from_email,
message.from_name,
user_message.to_email,
user_message.to_name
FROM
message,
user_message
WHERE
user_message.message_id = message.id
AND user_message.status = 'U'
GROUP BY
message.id
LIMIT " . $max_size;
$result = mysql_query($sql, $conn) or die(mysql_error());
//No messages left to send, so stop looping
if (!$total = mysql_num_rows($result)) break;
$max_size -= $total; //Maybe we've still not got to 100 yet
//Build up a list of recipients
$list =& new Swift_RecipientList();
$message = null;
$sender = null;
$ids = array();
while ($row = mysql_fetch_assoc($result))
{
//No need to repeatedly create $message or $sender in the loop
if ($message === null) $message =& new Swift_Message($row["subject"], $row["body"]);
if ($sender === null) $sender =& new Swift_Address($row["from_email"], $row["from_name"]);
$list->addTo($row["to_email"], $row["to_name"]);
//Collect recipient IDs for use later
$ids[] = $row["id"];
}
//Send this batch
$batch =& new Swift_BatchMailer($swift);
$batch->send($message, $list, $sender);
//Some may have failed, some may not
$failures = $batch->getFailedRecipients();
foreach ($failures as $key => $value)
{
//Filter for use in SQL (and add single quotes)
$failures[$key] = "'" . mysql_real_escape_string($value) . "'";
}
//Update all that have NOT failed to 'S'
$sql = "
UPDATE user_message
SET status = 'S'
WHERE
id IN (" . implode(",", $ids) . ")";
if (!empty($failures))
{
$sql .= "
AND to_email NOT IN (" . implode(",", $failures) . ")";
}
mysql_query($sql, $conn) or die(mysql_error());
//Update all that have failed to 'F'
if (!empty($failures))
{
$sql = "
UPDATE user_message
SET status = 'F'
WHERE
id IN (" . implode(",", $ids) . "
AND to_email IN (" . implode(",", $failures) . ")";
mysql_query($sql, $conn) or die(mysql_error());
}
}EDIT 2| On hindisght my table names are crap. "message" and "message_recipient" would be far more self-explanatory
Ok. so I pretty much get all of that, but kinda get lost towards the bottom. I'm gonna try and implement this and see if I don't accidentally erase the internet in the process 
It's amazing how seeing someones methods of solving things can open up a new world. I think I picked up on like 10 things I didnt know (or think to try) in your code.
Thank You.
Fish
><>
It's amazing how seeing someones methods of solving things can open up a new world. I think I picked up on like 10 things I didnt know (or think to try) in your code.
Thank You.
Fish
><>
Everything makes complete sense accept I think the initial "GROUP BY" may be messing something up.
I'm finding when testing in MYSQL it is grouping the messages and returning only one recipient of each message in the queue. I believe each sql call should return all recipients for a particuar message. I'm not sure how to pull that off.
EDIT: here is my sql. I use an additional table to get the senders email and name.
I'm finding when testing in MYSQL it is grouping the messages and returning only one recipient of each message in the queue. I believe each sql call should return all recipients for a particuar message. I'm not sure how to pull that off.
EDIT: here is my sql. I use an additional table to get the senders email and name.
Code: Select all
$sql = "SELECT
Q.id,
M.m_subject,
M.m_message,
S.from_email,
S.from_name,
Q.to_email,
Q.to_name
FROM
`queue` AS Q,
`messages` AS M,
`senders` AS S
WHERE
S.member_id = M.m_agid
AND Q.msg_id = M.mid
AND Q.status = 0
GROUP BY
M.mid
LIMIT " . $max_size;- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Thanks for taking the time to look at this... No rush... I'm heading to bed not. its like 2:30 here.
I assume you wanted to give me all recipients for a specific message, send those out and pull all recipients for another message.
What its giving me a list of recipients but for 1 message each. so for example instead of getting
recipient 1, message 1
recipient 2, message 1
recipient 3, message 1
I'm getting
recipient 1, message 1
recipient 2, message 2
recipient 3, message 3
Hope thats clear.
I assume you wanted to give me all recipients for a specific message, send those out and pull all recipients for another message.
What its giving me a list of recipients but for 1 message each. so for example instead of getting
recipient 1, message 1
recipient 2, message 1
recipient 3, message 1
I'm getting
recipient 1, message 1
recipient 2, message 2
recipient 3, message 3
Hope thats clear.
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
Doh! You're right, that's what the SQL would do... I'm an idiot 
That's not the best way... Given more time I could have either come up with a single query to get a single message (batch)... or played with the loop since the existing resultset is ordered anyway.
Code: Select all
$max_size = 100;
while ($max_size > 0)
{
$sql = "
SELECT
user_message.id,
message.subject,
message.body,
message.from_email,
message.from_name,
user_message.to_email,
user_message.to_name,
user_message.message_id
FROM
message,
user_message
WHERE
user_message.message_id = message.id
AND user_message.status = 'U'
AND user_message.message_id = first_message
GROUP BY
user_message.id
ORDER BY
user_message.message_id
LIMIT " . $max_size;
$result = mysql_query($sql, $conn) or die(mysql_error());
//No messages left to send, so stop looping
if (!mysql_num_rows($result)) break;
//Build up a list of recipients
$list =& new Swift_RecipientList();
$message = null;
$sender = null;
$message_id = -1; //For tracking in the loop
$ids = array();
$total = 0;
while ($row = mysql_fetch_assoc($result))
{
//Not the same message anymore -- different batch
if ($message_id > -1 && $row["message_id"] != $message_id)
{
break; //Just this loop
}
$total++;
//No need to repeatedly create $message or $sender in the loop
if ($message === null) $message =& new Swift_Message($row["subject"], $row["body"]);
if ($sender === null) $sender =& new Swift_Address($row["from_email"], $row["from_name"]);
$list->addTo($row["to_email"], $row["to_name"]);
//Collect recipient IDs for use later
$ids[] = $row["id"];
}
$max_size -= $total; //Maybe we've still not got to 100 yet
//Send this batch
$batch =& new Swift_BatchMailer($swift);
$batch->send($message, $list, $sender);
//Some may have failed, some may not
$failures = $batch->getFailedRecipients();
foreach ($failures as $key => $value)
{
//Filter for use in SQL (and add single quotes)
$failures[$key] = "'" . mysql_real_escape_string($value) . "'";
}
//Update all that have NOT failed to 'S'
$sql = "
UPDATE user_message
SET status = 'S'
WHERE
id IN (" . implode(",", $ids) . ")";
if (!empty($failures))
{
$sql .= "
AND to_email NOT IN (" . implode(",", $failures) . ")";
}
mysql_query($sql, $conn) or die(mysql_error());
//Update all that have failed to 'F'
if (!empty($failures))
{
$sql = "
UPDATE user_message
SET status = 'F'
WHERE
id IN (" . implode(",", $ids) . "
AND to_email IN (" . implode(",", $failures) . ")";
mysql_query($sql, $conn) or die(mysql_error());
}
}