Page 2 of 4
Posted: Tue Jun 05, 2007 10:58 am
by fishnyc22
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.
Posted: Tue Jun 05, 2007 11:01 am
by Chris Corbyn
AntiFlood works with send() and with batchSend(), I forgot to mention that.
You have the right idea by pulling out 100 emails and only starting a new batch when the message changes too yes

No need for anti-flood if you're sending 100, although any more than that and I'd say you need it.
Posted: Tue Jun 05, 2007 1:43 pm
by Chris Corbyn
I just wanted to say thanks for the donation

Good luck getting the script working.
Posted: Tue Jun 05, 2007 1:54 pm
by fishnyc22
d11wtq wrote:I just wanted to say thanks for the donation

Good luck getting the script working.
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...
Sounds like an awesome trip. Have a great time.
Posted: Tue Jun 05, 2007 3:23 pm
by fishnyc22
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
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...
Posted: Tue Jun 05, 2007 5:03 pm
by fishnyc22
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
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();
}
}
Posted: Tue Jun 05, 2007 5:32 pm
by Chris Corbyn
Here's how I'd do it (untested). This assumes one run of the script will process maximum 100 emails.
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 | Added reference operators for PHP4.
EDIT 2| On hindisght my table names are crap. "message" and "message_recipient" would be far more self-explanatory

Posted: Tue Jun 05, 2007 5:56 pm
by fishnyc22
Wow, this looks awesome. I'm gonna go eat dinner and then try to figure out whats going on there

Thanks for the HUGE tip!
Posted: Tue Jun 05, 2007 8:55 pm
by fishnyc22
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
><>
Posted: Tue Jun 05, 2007 10:12 pm
by fishnyc22
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.
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;
Posted: Wed Jun 06, 2007 1:22 am
by Chris Corbyn
Do you save a single row in the messages table for each single user (one-to-one), or do you link one message to several users (many-to-one)?
Posted: Wed Jun 06, 2007 1:27 am
by fishnyc22
Mornin... I link one message to many users. I figured that was most efficient.
Posted: Wed Jun 06, 2007 1:32 am
by Chris Corbyn
OK, I have to leave for work now but I'll have another look when I get to the office

Did you say it's just pulling one row out, or is it doing something else?
Posted: Wed Jun 06, 2007 1:39 am
by fishnyc22
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.
Posted: Wed Jun 06, 2007 2:54 am
by Chris Corbyn
Doh! You're right, that's what the SQL would do... I'm an idiot
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());
}
}
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.