php - Approach to sending alerts with CakePHP -

this rather obscure question please bare me. it's more approach syntax.

i have mysql table filled 'notifications' (id,user_id,date,etc). have send alert (via email, facebook, twitter, whatever... not issue) when each of entries pings 'true'. here's thing, how should go pinging them true in efficient way possible when conditions determine true/false have calculated?

sending email of bday easy. search date field today's date. suppose have send en email every 20th day starting date entered in field? have calculate each row see if it's true today.

how should that? i've considered following: 1. complex mysql query 2. php page cron job run through each row , mark them done 1 1 every x seconds/min 3. pulling hair out , running out of room screaming little girl. i'm leaning toward 3 @ moment.

my main concern i'm on shared server , don't want intensive. spending brain on this. appreciate it.

you should have @ strtotime() examples , see if can accomodate types of alerts sending. allow represent things annual reminders (birthdays), alerts every 20 days, monthly alerts (first monday/last friday of each month) in table 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 set cron job (or poor man's cron on shared host) fires every ten minutes or simple code:

# 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 $alert) {     # send alert , update status     $status = $this->something->send($alert);     $status = ($status) ? 'sent' : 'failed';     $this->alert->id = $alert['alert']['id'];     $this->alert->savefield('status', $status);     # generate , 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 march 5th year , same table looks 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  |