[SOLVED] Using Swiftmailer to send bulk messages with CRON

Swift Mailer is a fantastic library for sending email with php. Discuss this library or ask any questions about it here.

Moderators: Chris Corbyn, General Moderators

User avatar
fishnyc22
Forum Commoner
Posts: 38
Joined: Wed May 30, 2007 6:20 pm

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

I just wanted to say thanks for the donation :) Good luck getting the script working.
User avatar
fishnyc22
Forum Commoner
Posts: 38
Joined: Wed May 30, 2007 6:20 pm

Post 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.
User avatar
fishnyc22
Forum Commoner
Posts: 38
Joined: Wed May 30, 2007 6:20 pm

Post 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...
User avatar
fishnyc22
Forum Commoner
Posts: 38
Joined: Wed May 30, 2007 6:20 pm

Post 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();
		}
		
	}
Last edited by fishnyc22 on Tue Jun 05, 2007 5:47 pm, edited 1 time in total.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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 ;)
User avatar
fishnyc22
Forum Commoner
Posts: 38
Joined: Wed May 30, 2007 6:20 pm

Post 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!
User avatar
fishnyc22
Forum Commoner
Posts: 38
Joined: Wed May 30, 2007 6:20 pm

Post 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
><>
User avatar
fishnyc22
Forum Commoner
Posts: 38
Joined: Wed May 30, 2007 6:20 pm

Post 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;
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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)?
User avatar
fishnyc22
Forum Commoner
Posts: 38
Joined: Wed May 30, 2007 6:20 pm

Post by fishnyc22 »

Mornin... I link one message to many users. I figured that was most efficient.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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?
User avatar
fishnyc22
Forum Commoner
Posts: 38
Joined: Wed May 30, 2007 6:20 pm

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
Post Reply