MySQL date repeat

Question

In one of my web application ( in PHP and MySQL ) there is a event reminder for users. User can add the next reminder date and also put the repeat interval ( ie Only Once, 3 months, 6 months and yearly ). Saving that in table like

id      |    emp_id      |    alert_date         |   repeat_interval
--------+----------------+-----------------------+----------------------
1       |      124       |  2014-05-02 00:00:00  |     3
--------+----------------+-----------------------+----------------------
2       |      123       |  2014-05-02 00:00:00  |     12
--------+----------------+-----------------------+----------------------
3       |      122       |  2014-02-02 00:00:00  |     6
--------+----------------+-----------------------+----------------------
4       |      121       |  2014-07-02 00:00:00  |     0
--------+----------------+-----------------------+----------------------
5       |      124       |  2014-05-02 00:00:00  |     6
--------+----------------+-----------------------+----------------------
6       |      125       |  2014-08-02 00:00:00  |     6
--------+----------------+-----------------------+----------------------
7       |      126       |  2014-06-02 00:00:00  |     12
--------+----------------+-----------------------+----------------------
8       |      127       |  2014-05-02 00:00:00  |     3
------------------------------------------------------------------------

The repeat_interval having the value as

0 - One time event
3 - Every 3 months
6 - Every 6 months
12 - Yearly

Now I have a "cronjob" running every morning, searching for events and send alerts to users. I have the following MySQL query

SELECT * 
FROM alerts as al 
WHERE date(al.alert_date) = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY);

This will return the tmrw's alerts and I can send mail with this. But my question is how to take these intervals also ?

ie if the alert_date is 01-05-2013 and set repeat for 3 months then need to send mail on 01-05-2013 and the intervals of 3 months

Actually is this the correct way for this purpose ? Or I need to alter the tables ?

Can any one give me an idea ? Thanks in advance

Answer 1

Yo can just update the alert_date with the next date it is supposed to be alerted on. So every time the cronjob is executed, update all alerts that have repeat_interval != 0, with alert_date + repeat_interval number of months. I guess you can use the DATE_ADD() function to just att months:

UPDATE alerts SET alert_date = DATE_ADD(alert_date, INTERVAL repeat_interval MONTHS) WHERE repeat_interval != 0

If you need to store the original date, just create an extra column, next_alert, and update that date as such.

Answer 2

I suggest you to add another column (for example next_alert) into your database which will tell you, when next event occurs.

Then in your cron script you will simply checks if next_alert date matches current date and if yes, adjust next_alert to next_alert + repeat_interval of course use specific sql function to add months to date

DATE_ADD(next_alert, INTERVAL repeat_interval MONTHS)

or use PHP methods to achieve that.