[FIXED] Approach to sending alerts with CakePHP

Issue

This is a rather obscure question. It's more about approach than syntax.

I have a MySQL table filled with 'notifications' (id,user_id,date,etc). I have to send an alert (via email, facebook, twitter, whatever... not the issue) when each of those entries pings as 'true'. Here's the thing, how should I go about pinging them as true in the most efficient way possible when the conditions that determine true/false have to be calculated?

Sending an email of a bday is easy. Just search a date field for today's date. Suppose you have to send en email every 20th day starting from a date entered in the field? I have to calculate each row to see if it's true today.

How should I do that? I've considered the following: 1. a complex MySQL query 2. a PHP page cron job run through each row and mark them as done 1 by 1 every x seconds/min 3. pulling my hair out and running out of the room screaming like a little girl. I'm leaning toward 3 at the moment.

My main concern is that I'm on a shared server and I don't want to do anything too intensive. Thanks for spending your brain on this. I appreciate it.

Solution

You should have a look at the strtotime() examples and see if it can accomodate the types of alerts you are sending. This could allow you to represent things like annual reminders (birthdays), alerts every 20 days, monthly alerts (first Monday/last Friday of each month) in a table like so:

|   id | user_id | status  | send_on             | next_occurrence    |
|------|---------|---------|---------------------|--------------------|
| 1001 |     123 | pending | 2010-03-04 12:00:00 | Next March 4 noon  |
| 1002 |     123 | pending | 2010-02-05 00:00:00 | +20 days midnight  |
| 1003 |     123 | pending | 2010-02-01 08:00:00 | First Monday 8am   |

You then set up a CRON job (or poor man's CRON on a shared host) that fires every ten minutes or so with some fairly simple code:

# get pending alerts
$alerts = $this->Alert->find('all', array(
    'conditions' => array(
        'send_on <=' => date('Y-m-d H:i:s'),
        'status'     => 'pending',
    ),
));
# send alerts
foreach ($alerts as $alert) {
    # send alert and update status
    $status = $this->Something->send($alert);
    $status = ($status) ? 'sent' : 'failed';
    $this->Alert->id = $alert['Alert']['id'];
    $this->Alert->saveField('status', $status);
    # generate and save next pending occurrence
    $this->Alert->create();
    $this->Alert->save(array('Alert' => array(
        'user_id'         => $alert['Alert']['user_id'],
        'status'          => 'pending',
        'send_on'         => strtotime($alert['Alert']['next_occurrence']),
        'next_occurrence' => $alert['Alert']['next_occurrence'],
    )));
}

Fast forward to March 5th this year and that same table now looks like this:

|   id | user_id | status  | send_on             | next_occurrence    |
|------|---------|---------|---------------------|--------------------|
| 1001 |     123 | sent    | 2010-03-04 12:00:00 | Next March 4 noon  |
| 1002 |     123 | sent    | 2010-02-05 00:00:00 | +20 days midnight  |
| 1003 |     123 | sent    | 2010-02-01 08:00:00 | First Monday 8am   |
| 1004 |     123 | sent    | 2010-03-01 08:00:00 | First Monday 8am   |
| 1005 |     123 | sent    | 2010-02-25 00:00:00 | +20 days midnight  |
| 1006 |     123 | pending | 2010-03-17 00:00:00 | +20 days midnight  |
| 1007 |     123 | pending | 2010-04-05 08:00:00 | First Monday 8am   |
| 1008 |     123 | pending | 2011-03-04 12:00:00 | Next March 4 noon  |

Answered By - deizel