2

I have built an emailing script with PHPmailer after inserting into a table, however, I'm getting a bad gateway 502 coz the script times out. and sending 300+ emails in response to a web request doesn't sound like a good idea to me. So my question is how can I build a queue that will send the emails in the background?

as far as I understand I will need new table lets say email_queue_table insert the email addresses, content and then have a field called status sent or queued create a while loop something like if($status == "queued"){ //then send the email here} else{ // nothing to be sent.}

If you know of a more efficient/better way of doing this I'm all ears. Thanks for any help.

bob
  • 466
  • 1
  • 7
  • 27
  • 1
    You've already got a plan that sounds good. Try it out and see if it works. – M. Eriksson Aug 01 '17 at 15:11
  • The logic of what you've got is along the right lines. Have a script which selects a small amount (e.g. 50 at a time - `LIMIT 0, 50` in SQL). Flag the status as sent/failed to send. Repeat until you get to the end of the list. Possibly have something to try and re-send for any that failed, but restrict it to a certain number of attempts (you could have a field for resend attempts that increments each time it's tried). You can't run this script through a browser as it will time out. You must run it from the PHP CLI or trigger it with an ajax request. Could use a cron job to run it periodically. – Andy Aug 01 '17 at 15:12
  • Storing the emails in the database with a status is a good idea. You might also consider generating the content of the email when the queue is run reducing the data in the database. Another option which would add complexity but might work well is to use a Redis queue with a worker the takes and sends emails from the queue one at a time. It is a bit more complex but wanted to add that in case others find it useful. – Uberswe Aug 01 '17 at 15:20
  • For the core sending part, refer to [the mailing list example provided with PHPMailer](https://github.com/PHPMailer/PHPMailer/blob/master/examples/mailing_list.phps). – Synchro Aug 01 '17 at 15:38
  • @bob I've posted an answer. An upvote or acceptance of the answer is appreciated if it helps. – Andy Aug 01 '17 at 15:49

1 Answers1

1

The logic of how it would work - with a bit of code to help you along the way - is something like this:

Add the following fields to your email_queue_table table:

  • email_address, Email address (to:)
  • content, Message content. If it's the same message that's to be sent to every user, you'd be better storing this once somewhere else. If it's just a few things that need changing in the content such as the users name then use PHP's str_replace() to do things like str_replace('%name%', 'Andy') where %name% is in your template and will be replaced as appropriate on each loop. Ideally you would not have this column or be repeating the same data here - if your message was 50 Kb and you had 3000 users, for example, you'd be storing 150 Mb of data in a table un-necessarily.
  • sent_status, Sent status (default = "not sent", or 0)
  • retry_attempts Retry attempts (default = 0)

Create a PHP script which does the following:

  • SELECT email_address, content FROM email_queue_table LIMIT 0, 50. This gets you 50 people per loop. Repeat until the end of the list - to do this you will need to know the total number of records in the table which you can do with COUNT()
  • On each loop (each person you're sending mail to):
    • Use PHPMailer to attempt to send content to email_address
    • Read the return status from PHPMailer. If it's successfully sent flag sent_status as "sent" (1). If not, flag it as "not sent" (0).
  • Put in a delay, e.g. sleep(60) between each batch of 50. This pauses execution of the script for 1 min (60 seconds) and may help mitigate your server as being flagged for sending out large quantities of email in one attempt.

When all the messages have been sent, you could optionally go back through the table to try and resend any that didn't send. Still keep the LIMIT logic because there may be a large number where it didn't send, e.g.

SELECT email_address, content FROM email_queue_table WHERE sent_status = 0 AND retry_attempts < 5 LIMIT 0, 50

Increment the retry_attempts field. Stop if it goes beyond, say, 5 attempts.

You cannot execute the above script through a browser because it will time out.

Instead, you can manually trigger it from a command line, e.g.

php send_email.php

Or set the above up on Cron, to run every night, or at whatever frequency are needed.

Or you can trigger it from an ajax call and update the progress in the browser. See: creating background processes in php for long running process

Andy
  • 5,142
  • 11
  • 58
  • 131
  • 1
    Good answer. For the "send content to email address" part, refer to [the mailing list example provided with PHPMailer](https://github.com/PHPMailer/PHPMailer/blob/master/examples/mailing_list.phps). It provides an efficient implementation of what is described here. – Synchro Aug 01 '17 at 15:37
  • @Andy Thanks for the great answer, however slightly at a loss of how to achieve a few of the steps mentioned. So this will create a row per email address and then update the row's status if sent or not? – bob Aug 02 '17 at 11:17
  • You need to have the code that sends the email inside the `while` loop (lines 21 - 24). And that's also where you need to update the status of whether or not it was sent (using an `UPDATE` query on the `sent_status` field). This loop handles each person in the table, one at a time. I can't verify your code as I'd need the database etc. I'm not sure why you're binding an `id` parameter on line 8 – Andy Aug 02 '17 at 13:03
  • Yeah, I left it out of the while loop coz I was uncertain of how to combine it. and the binding id was not meant to be there. surely I need to insert them into the `email_queue_table` from another table. kinda of confusing myself here. lol – bob Aug 02 '17 at 13:12
  • Your `while` loop gets 1 person at a time (Person 1, Person 2, Person 3, etc). So anything that goes inside that loop (`while ... { }`) you will be able to access the details for that particular person (i.e. their email address, their name, etc). You need to send the mail to each person, read the response from PHPMailer and then store it against that persons details in the DB. All inside the loop. At the end of that loop, you can do another one to go back through the ones where it failed to send, although you have kind of got that set up anyway. – Andy Aug 02 '17 at 13:18
  • Oh ok, so it doesn't get inserted in the `email_queue_table ` db until the end of the script, either sent or not sent? – bob Aug 02 '17 at 13:20
  • 1
    I'm unsure what you mean. `email_queue_table` has to be populated (before the script runs) with all the email addresses etc. If it's not, you need to do that first. Then when it's full of those details you loop through it (the `while` loop gets 1 person (row) at a time). So all your code to both send the email, and then update (SQL `UPDATE` query) the status of whether or not it was sent goes inside the same loop. It's like saying in English "Send an email to Bob. Has the email been sent? Write the status (sent/not sent) against Bob's record in the table. Repeat for the next person..etc..." – Andy Aug 02 '17 at 13:30
  • Ok, got that. what I'm confused about is how to get the `email_queue_table ` table populated from another table, just insert into from...? btw I'll mark your answer as correct. thanks so much for helping me muddle through, apologize for the dumb questions if there were any. – bob Aug 02 '17 at 13:38
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/150832/discussion-between-andy-and-bob). – Andy Aug 02 '17 at 13:39