I believe in one of my posts above (in my personal mess of code) I had just one sql query that was ordered by message_id. Is 1 query and cycling though the batches based on message_id more efficient (prob not the way I wrote it) than multiple queries? Just curious.
No sorry, take that out. I was experimenting with the query. As you can see, the code I'm posting has not been tested. That line was leftover from something else I was trying
Is that what you intended? Or did you just want to pull all recipients of one message. If you're busy..I understand.... I don't want to keep buggin you about this. Worse comes to worse I get it going how I had it before. One query, parsing through the data and creating batches based on message_id.
OK, so we are abandoning the idea of getting the first batch then looping through and getting the second batch. The above SQL will pull the first 100 even if the message_id is different. I'm not sure how that will work with the php code on page 2. Since that is not sorting the message_ids. I think we've both been staring at our machines too long.
The PHP code was updated when I posted earlier today It now loops through the resulstset, then breaks out of the loop once it hits a different message ID. If that's less than 100 recipients, it goes around again to do another batch The code looks the same, bit there are subtle differences.
Sorry. I was just about to repost that you had changed that. I didnt realize that. I TOTALLY GET whats going on here. I thought we were still shooting for the other method. I'm gonna do my best not to bother you anymore tonight
Thanks a bunch! Very cool of you to walk me though this.
fishnyc22 wrote:Is there any concern with calling the SQL call over and over again? Rather than calling it once and using that one record set to do whats necessary?
I'd rather not do that... I'm aware of a better way, I just didn't have much time to play with it in the office today. Lemme post an update in a few mins.
Any need to "flushFailedRecipients" here?
Not in my example since I recreate the $batch object when I start a new batch. If you're re-using the same object then yes you would need to flush it
<?php
$max_size = 100;
$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'
ORDER BY
user_message.message_id
LIMIT " . $max_size;
$result = mysql_query($sql, $conn) or die(mysql_error());
$num_rows = mysql_num_rows($result);
$data_pos = -1; //Current position in the resultset
$ids = array();
//Nothing to do if no data
if ($num_rows > 0)
{
$swift =& new Swift(new Swift_Connection_SMTP("localhost"));
$batch =& new Swift_BatchMailer($swift);
do
{
//Build up a list of recipients
$list =& new Swift_RecipientList();
$message = null;
$sender = null;
$message_id = -1; //For tracking in the loop
while ($row = mysql_fetch_assoc($result))
{
$data_pos++;
//Not the same message anymore -- different batch
if ($message_id > -1 && $row["message_id"] != $message_id)
{
mysql_data_seek($result, --$data_pos); //Backtrack ready for next batch
break; //Just this loop
}
$message_id = $row["message_id"];
//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->send($message, $list, $sender);
} while ($data_pos < $num_rows); //Only while we're not at the end of the resultset
//If we have any records to update
if (!empty($ids))
{
//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());
}
}
}
wow. never used mysql_data_seek. cool stuff. However its only sending the last message in the record set. It is updating the DB to S for all messages though. I'm trying to debug now. Thanks..very cool stuff.