CREATING A SIMPLE TO-DO APPLICATION

This week in part 4 of creating our simple to-do application we’ll be learning how to send email notifications/reminders. To do this we’ll be using the PHP mail method and learning how to schedule repeatable tasks on Linux using cron. The equivalent process for Windows is the task scheduler and it is pretty self explanatory. WordPress has what it calls the wp-cron alternative, but it’s use is limited as it requires someone to actually use the site.

CRON

Cron is the Unix/Linux schedule process, it allows users to configure tasks that are executed at defined intervals. It’s not exactly considered to be particularly user friendly, so many hosting providers have a simplified UI to help set it up. In any event, it is still beneficial to know the basics.

Cron jobs are specified in the ‘crontab’ file (typically in the /etc folder) Crontab is short for cron table, and as the name suggests it is a series of columns that determine when and what is run, split up in to tasks on each new line of the file. Each column has a specific time related function, with the last being the actual file that is executed. The column order is as follows:

1. Minutes (0 – 59)
2. Hours (0 – 23)
3. Day of month (1 – 31)
4. Month (1 – 12)
5. Day of week (0 – 6)
6. File or command

You can specify any combination of the above columns, disabling columns is by way of the * character. This configuration method can lead to some confusing schedules being created. You can specify that any Monday, that is the 1st of the month at 11am execute some task. It would look something like this:

* 11 1 * 1 task.php

Overall it’s quiet a powerful system that you can configure to do almost anything. There are a few things to note, don’t schedule a task that takes 5 minutes to execute every minute. Cron will execute this and you will end up with duplicate scripts taking up resources and potentially causing issues.

PHP MAIL

PHP has a built in email function that is incredible easy to use. It uses the default SMTP server details in your php.ini configuration to send emails. Generally if you’re running in a hosted server this will be configured, if it’s not or if you want to use a different server you can use the ini_set function to update your configuration. The ini_set command would look something like this:

ini_set("SMTP", "smtpserver.yourdomain.com");

To actually send an email, you have to provide the following information:

• The “to” address
• The subject
• The message
• Additional headers
• Additional parameters

The “to” address can be a simple one line “you@domain.com” or either a csv of email addresses. You can also specify the name of the person in this field as follows:

Bob Bobberson <bobby@bobberson.com>

Another quirk about the mail function is that the message body must be split on to multiple lines of no more than 70 characters. To indicate a new line you need to use the \r\n (CRLF) characters.

There is only one header that must be sent and that is the “from” header. You can also specify the “reply-to” or CC and BCC headers. The CC and BCC values have the same format as the “to” field. A full email would look something like this:

$from = "To Do Application <to-do@domain.com>";
$to = "Bob Bobberson <bobby@bobberson.com>";
$subject = "How to send an email in PHP";
$message = "Read this! Sending an email in PHP is really easy";
$headers = "From: To Do Application ";
$headers .= "Bcc: Another attendee ";
$headers .= 'Reply-To: ' . $from;
$headers .= 'Return-Path: ' . $from;
mail($to, $subject, $message, $headers);

I should also note, that if you are planning on sending lots of emails there are better alternatives out there that will queue emails and perform better in general. The mail function is not designed to scale quickly or well, however for once off emails it is sufficient

BUILDING OUR SCRIPT

So now we know how we’re going to schedule our emails and we also know how we’re going to send them, next up is what are we going to send? To do this it’s back to our MySQL database and a simple query. Here we’re going to do a simple select from our ‘tasks’ table where the ‘task_date’ is between 15 and 20 minutes from now. To do that we use the MySQL ADDDATE function.

SELECT `task_id`, `user_firstname`, `user_surname`, `user_email`, `task_name`, 
    `task_priority`, `task_color`, `task_description`, `task_attendees`, `task_date` 
FROM `tasks` AS t 
INNER JOIN `users` AS u 
    ON u.user_id = t.user_id 
WHERE `task_date` >= ADDDATE(NOW(), INTERVAL 15 MINUTE) 
    AND `task_date` <= ADDDATE(NOW(), INTERVAL 20 MINUTE)

The above SQL combined with a cron job executing every 5 minutes should give us pretty reliable email notifications without putting load on a server. You may also notice the “INNER JOIN” syntax. This allows me to query 2 tables where both tables have the column ‘user_id’. In this way I can get the users email, firstname and surname values as well as all of their scheduled tasks in one go. The INNER JOIN also dictates that both tables must have matching rows to return, if only one has to have a matching row I could use what is called a “LEFT JOIN”.

Our full email.php script looks something like this:

<?php
require_once('database.php');

$from = "To Do Application <to-do@domain.com>";
$default_headers = "From: $from\r\n";
$default_headers .= "Reply-To: $from\r\n";
$default_headers .= "Return-Path: $from\r\n";

$query = $connection->prepare("SELECT `task_id`, `user_firstname`, `user_surname`, `user_email`, `task_name`, `task_priority`, `task_color`, `task_description`, `task_attendees`, `task_date` FROM `tasks` AS t INNER JOIN `users` AS u ON u.user_id = t.user_id WHERE `task_date` >= ADDDATE(NOW(), INTERVAL 15 MINUTE) AND `task_date` <= ADDDATE(NOW(), INTERVAL 20 MINUTE)");
$query->execute();

$query->bind_result($id, $firstname, $surname, $email, $name, $priority, $color, $description, $attendees, $date);

while ($query->fetch()) {
    $to = "$firstname $surname <$email>";
    $headers = $default_headers;
    if(!empty($attendees)){
        $headers .= $headers . "\nBcc: $attendees";
    }
    
    $subject = "Upcoming priority $priority task - $name";
    $message = "Don't forget $name is at $date!\r\n";
    
    if(!empty($description)){
        $message .= "$description\r\n";
    }
    
    mail($to, $subject, $message, $headers);
}

$query->close();

?>

It’s pretty basic at the moment, but you can easily update the above to use HTML to send much prettier email alerts. To do that you need to set 2 additional email headers:

$default_headers .= "MIME-Version: 1.0\r\n";
$default_headers .= "Content-type: text/html; charset=iso-8859-1\r\n";

Once you’ve got that done you can update the $message variable to read in a HTML template from file or just manually write the HTML as you need it.

$message = "<html>\r\n" .
"<head><title>Upcoming priority $priority task - $name</title></head>\r\n" .
"<body>\r\n" . 
"<p>Upcoming priority $priority task - $name</p>\r\n" .
"<p>Don't forget $name is at $date!<br/>\r\n" .
"$description\r\n" .
"</p>\r\n" .
"</body></html>\r\n";

PUTTING IT ALL TOGETHER

Now that we have our PHP email script and we know how to set up our cron, there is just a few more things we need to do. At the top of the we need to prep-end the following check

if( php_sapi_name() != 'cli' )
    die("You don't have permissions to run this file");

This check ensures that the file can only be run from the command line. This means that even if the file is in your web folder (it probably shouldn’t) only someone at the command line or the cron job can execute it.

<?php
if( php_sapi_name() != 'cli' )
    die("You don't have permissions to run this file");
    
require_once('database.php');

$from = "To Do Application <to-do@domain.com>";
$default_headers = "From: $from\r\n";
$default_headers .= "Reply-To: $from\r\n";
$default_headers .= "Return-Path: $from\r\n";
$default_headers .= "MIME-Version: 1.0\r\n";
$default_headers .= "Content-type: text/html; charset=iso-8859-1\r\n";

$query = $connection->prepare("SELECT `task_id`, `user_firstname`, `user_surname`, `user_email`, `task_name`, `task_priority`, `task_color`, `task_description`, `task_attendees`, `task_date` FROM `tasks` AS t INNER JOIN `users` AS u ON u.user_id = t.user_id WHERE `task_date` >= ADDDATE(NOW(), INTERVAL 15 MINUTE) AND `task_date` <= ADDDATE(NOW(), INTERVAL 20 MINUTE)");
$query->execute();

$query->bind_result($id, $firstname, $surname, $email, $name, $priority, $color, $description, $attendees, $date);

while ($query->fetch()) {
    $to = "$firstname $surname <$email>";
    $headers = $default_headers;
    if(!empty($attendees)){
        $headers .= $headers . "\nBcc: $attendees";
    }
    
    $subject = "Upcoming priority $priority task - $name";
    $message = "Don't forget $name is at $date!\r\n";
    $message = "<html>\r\n" .
        "<head><title>Upcoming priority $priority task - $name</title></head>\r\n" .
        "<body>\r\n" . 
        "<p>Upcoming priority $priority task - $name</p>\r\n" .
        "<p>Don't forget $name is at $date!<br/>\r\n" .
        "$description\r\n" .
        "</p>\r\n" .
        "</body></html>\r\n";    
    
    mail($to, $subject, $message, $headers);
}

$query->close();

?>

We also need to configure our cron to execute our email task every 5 minutes

*/5 * * * * email.php