Approach to sending alerts with CakePHP

Question

This is a rather obscure question so please bare with me. 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.

Answer 1

Below is somewhat simplified explanation of how I approached a similar situation where I needed to periodically send sms and emails based on varying complex conditions:

I created 2 new tables:

• scenarios (id; name; frequency)
• processes (id; scenario_id; process_type; execution_type; process)

with:

• scenario.frequency: hourly, daily, weekly or monthly
• processes.process_type: filter or action
• processes.execution_type: sql or function

Then I set up a cron according to these frequencies to go trough the scenarios table and take the scenarios of the appropriate frequency and collect the associated filter (which can be an sql statement or a php function). If the filter returns any results then perform the associated actions with the results. I also extended this system to perform setup, test and teardown so I could safely test my scenarios before activating them.

Hope this helps - Cheers

Answer 2

You might want to look into a queue service ala beanstalk etc. I know with some of them you can post actions / events into your queue and set them to be periodic, conditional etc.

Queue servers / services is a big topic, but maybe just having thrown this out there will give you some more options and some alternative trains of thought.

Answer 3

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  |