Notification cron

I've built an appointment manager and would like to notify clients 2 hours ahead of time by via email of their scheduled appointment time. I am storing their scheduled time in a MySQL Datetime field.

Example:


2015-11-24, 10:59:05

I currently have a cron scheduled to run every minute and collect the emails to be sent:

public function get_appointments_to_remind()
{
     //set time to two (2) hours from now
     $notify_time = date('Y-m-d, H:i:s', strtotime("+ 2 hours"));

     $this->db->select('email');

     //For those unfamiliar with codeigniter, produces WHERE  column = "value"
     $this->db->where("start_datetime", "$two_hour_time");

     $query = $this->db->get($this->_appointments_table);

     return $query->result_array();
}

Is there a better way to go about this? I'm worried about potentially missing some emails because the times may not be exact when the cron runs.

Yes, you will miss every appointment that doesn't have :00 for seconds. And if your cron errs for any reason or skips a second, you'll miss even more. You should add another DATETIME column to your table called notified_at (or something like that) and then do two conditions in your where clause:

• WHERE start_datetime

• AND notified_at IS NULL

That will capture any appointments that fall within the 2 hour time and haven't been notified yet. Then update the notified_at column to the current date once the email has been sent.

StackExchange Code Review Q#111711, answer score: 3