How to schedule emails to send out

Question

Using PHP, I have a query that goes through my DB looking for pending tasks with reminder triggers at certain times of the day. I have a cronjob that runs every 10 mins and checks the DB for any rows that has "remind_me" field set to go off within the next 10 mins. If it does find something, what's the best way to queue an email with the task information?

I guess I'll need some sort of message queue system, but how does the email part work? Will I need another cronjob that runs every minute to check the queue system?

Answer 1

We have a similar system(this is a ASP.NET app so we have services to run this using a internal scheduler... but the details are unimportant). We have one large message queue table that contains the email data to be sent, a to address and the subject line(the actual emails are generated from templates). The way our system works is that once a row is populated we immediately trigger a send task that gathers all the pending emails and sends them. It attempts to send them 3 times and if that fails they are put into a failed resent state. We have a cron like job that runs every 5 mins to sweep up any failed tasks(thing very rarely fail, if they due it is a network problem 99% of the time).

Answer 2

Cronjob is the only way I can think of (besides putting the script into some php page and hope someone will trigger it). I wouldn't recommend to run it every 1 minute tho. If your application is big enough, it'd cause just a performance problem.

Another option is to make a queue of emails to be sent and send them only if, for example, 10 of them are in this queue. The problem would be if there weren't enough emails to trigger it for a long time.

Answer 3

PHP solution

I have thought about your problem a little bit more and this is the PHP solution which I came up.

#!/opt/lamp/bin/php is the path to my php interpreter. You should change it to your path or just run it with php -f scheduler.php

scheduler.php

#!/opt/lampp/bin/php
<?php
// For testing purpose I set this to 5 seconds. You should set it to 600.
$time = 5;

while (true) {
    /*  
    let's assume you store the emails scheduled from the database in this
    in memory array.
    */
    $array = array( 
        "Message to run last", #message which is scheduled last.
        "Message to run after first message",
        "Message to run immediately", #message which is scheduled first.
    );

    $message = array_pop($array);    
    while ($message != NULL) {
            echo $message . "\r\n";
            /* 
            For testing purpose I am just simply echoing out messages.
            In here you should sent mails using for exmample
            http://php.net/manual/en/function.mail.php
            */
            $message = array_pop($array); // Get next message.
    }

    /*
    sleep $time. This is the "cron" part of your problem.
    */
    sleep($time); 
}

You should run this script from within a bash script to see what it does. Also the code has been documented pretty well. The script should run forever(in background).

Java Solution

This also isn't a PHP only solution, but I think this will scale better than the PHP solution I came up with. I am still working this, but I like what I came up with so far.

Google App Engine Solution

I know this is not a PHP solution, but when you can not install a message queue I think Google App Engine is the best solution. When I saw this video introduction from Brett Slatkin to google app engine using the Python SDK I was sold to Google App engine. It will only take 10 minutes of your time and you will learn how to create a basic guestbook and deploy it to the cloud. Below if interested I will try to explain which pieces you need to do this on Google's App Engine.

---

I guess I'll need some sort of message queue system

Maybe you could use the google app engine's taskqueue to qeueu the task. The taskqueue even has an eta which you can set to run at a specific time. Google App engine has a generous free quota. You can add 100,000 queues to the task free of charge every day.

but how does the email part work

I would use google app engine for this. You can use google app engine's mail service. It also has a generously free quota (2000 recipients a day). I would advise you just to cal the mail api from the queue to send the message.

Answer 4

How often do you want to send an email? If it's on occurence (the minute it is discovered by your existing cronjob, i.e. every 10 minutes), you could make the script that looks for the remind-me flag do this:

1 - add a reference to each row the flag was found in to an array, e.g.

$reminders = array(1, 3, 212);

Each element in the array references a primary key of your tasks table, here I used "tasks".

2 - after the database lookup is complete, see if $reminders is not empty and then run a query like

$query = "SELECT * FROM tasks WHERE id = '".implode(',', $reminders)."'";

to get all the task-specific data

3 - send an email to youself with this data using mail.

Was this what you were looking for?

Answer 5

I just very recently wrote my own solution to a very similar problem using PHP and MySQL.
Essentially: cron job runs a script at a set interval, that script checks the database for pending jobs, runs them, and deletes the jobs.

This is simply what I needed to do, but it could be modified to work for time intervals.
I wrote a separate PHP script that runs the actual job (a newsletter system) to make this automation a bit more useful. That is, I can run whatever command I want from it, I accomplish this by storing the name of the actual command and its arguments in the automator script.

exec("~/scripts/{$row['command']} {$row['args']}");

allows me to store arguments to the database to make my newsletter script more useful. You use the array $argv to grab the arguments, $argv[0] is the script name the the arguments follow in order.

I store all the newsletter info in a seperate table in the database which sorts them in order chronologically. The other, important, fields are the subject and body of the email. I then simply pass two arguments to newsletter.php to make it do exactly what I want: newsletter number and subscriber number (or all).

Testing for the time (10 minutes before) can be done with something like this (I over simplified for the sake of making it easier to understand):

date_default_timezone_set('America/Denver');
$month=;//without leading zeros
$day=;//without leading zeros
$hour=;//24 hour without leading zeros
$min=;//needs leading zeros
if($month==date(n) and $day==date(j)){
    if($hour==date(G)){
        if(($min-date(i))<=10){
            //run command
        }
    }elseif(($hour-1)==date(g)){
        if(($min-date(i))<=(-50)){
            //run command
        }
    }
}

You will want to change the timezone to your own, and pull the date and time information from the DB in some way.
The second part of the script (to run commands scheduled in the first 9 minutes of an hour) is untested but the first part I quickly tested it just now.
Hope this helps.

Answer 6

Hi, If you want to do a mail queue system I would suggest you take a look at PEAR::Mail_Queue and the associated tutorial.

You would be able to queue up mail when your 10minute script ran, and have the mail queue emptied every 10 minutes via a cron job. You could also have the mail queue emptied every minute and implement a 'do not send before' time in the queue on the mail in question.

Alternatively you could run your checking script every minute checking for items that a reminder is due in the last ten minutes and send the mail immediately, which removes the need for a queue system.

This could be implemented by a record of when you last sent alerts (so that you don't miss any and don't send alerts twice)

Hope that helps.