Approach to sending alerts with CakePHP

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.

Answers


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  |

Need Your Help

mysql function problem

php mysql

include('includes/config.php');

How to enable s3 path style access in jclouds

java amazon-s3 jclouds

Normally, when I'm using the AWS S3 Java SDK, I can enable path style access in the following way: