RemindMe: Programming with PHP and Mysql

Web Development with PHP and Mysql

Development of the RemindMe Service

Sending Reminders

The interesting problem that is left is how to send an email out at the necessary times for the necessary events. We also want to make sure that only one reminder is sent per day per event, to prevent spamming our users in case the script gets executed multiple times.

To handle the email event notification, I first created a php script (/batch/send_notices.php) that performs three actions for each of the reminder periods (week before, day before, and day of).

Find the events that we need to send reminders for. This is handled by the following code: $l_sql = "SELECT r.*, u.user_email FROM bday_users u, bday_reminders r " . "WHERE u.user_id = r.user_id AND " . " remind_week_before = 'Y' AND " . " TO_DAYS(IFNULL(last_reminded,'1900-01-01')) <> TO_DAYS(now()) AND " . " TO_DAYS(now())+7 = TO_DAYS(concat('" . date('Y') . "-',event_date_month,'-',event_date_day))"; $l_db->query($l_sql); This SQL statement retrieves all necessary information from the database for events where the user wants to be reminded a week before, the last_reminded date is not today (making sure we don't send more than one notice a day), and the event date is one week in the future.
I make use of the Mysql TO_DAYS function in several places. I've found it to be very useful for comparing dates. It functions by converting any date into the number of days since year 0, so date comparisons become integer comparisons.

For each event, send an email. For now, this function sends a very basic message.

function sendMail($p_user_email, $p_event_description, $p_event_date) {
		$l_subject = "Reminder: $p_event_description";
		$l_headers = "From: RemindMe (Omnilogic.net) <jason@omnilogic.net>\n";
		$l_body = "You requested a reminder for:\n   $p_event_description ($p_event_date)";
		$l_body .= "\n\nThanks for using the RemindMe service.\n\nhttp://remindme.omnilogic.net/\n";
		mail ($p_user_email, $l_subject, $l_body, $l_headers);
}

Update the last_reminded field with today's date, so we don't send another message if the script gets run more than once.

$l_sql = "UPDATE bday_reminders " .
			"SET last_reminded = now() " .
			"WHERE " .
			" remind_week_before = 'Y' AND " .
			" TO_DAYS(now())+7 = TO_DAYS(concat('" . date('Y') . "-',event_date_month,'-',event_date_day))";
$l_db->query($l_sql);

These steps are then repeated for the day before and day of reminders.

To execute this script I use cron to schedule it to run at 1:00AM. I add the following lines to the bottom of my crontab file:

# run the remindme script at 1:00am every night
00 01 * * * /user/local/bin/php /home/www/remindme.omnilogic.net/batch/send_notices.php

The only problem with this is that I have left the send_notices.php script accessible through the web, so anyone could load up http://remindme.omnilogic.net/batch/send_notices.php in their browser and cause the reminders to be sent. As the script checks to make sure it only sends one notice per day, this doesn't pose a great problem. If I wanted to, I could easily move it outside the web server document root to remove this possibility.

Previous | Next