MySQL: transport scheduler with recurring tasks

Question

I am working on a transport planner. Knowledge of MySQL (i) / PHP is (very) basic.

I want to display an HTML table with all the tasks for a specific day. But the problem is that some tasks need to be repeated (until I delete the entire task), so the tasks have such a frequency as: once, daily, weekly, twice a week and monthly.

So, when I add a new task on Monday with a weekly frequency, this task should be returned in my table every Monday.

Should I combine something like:

SELECT * FROM planner WHERE frequency = `once` AND date = CURDATE() SELECT * FROM planner WHERE frequency = `daily/weekly/two-weekly/monthly/yearly` AND day = DAYOFWEEK(CURDATE()) AND date + INTERVAL 1 DAY/1 WEEK/2 WEEK/1 MONTH 

And if so, then how?

Hope someone can help me.

EDIT:

To complete daily and weekly tasks, such a request (perhaps still awful) would do:

 (SELECT * FROM planner WHERE frequency = 'once' AND date = NOW()) UNION (SELECT * FROM planner WHERE frequency = 'daily' AND date + INTERVAL 1 DAY) UNION (SELECT * FROM planner WHERE frequency = 'weekly' AND day = DAYOFWEEK(CURDATE()) AND date + INTERVAL 1 WEEK) 

Although for the "two-week" and "monthly" INTERVAL + 2 WEEK / 1 MONTH, the result will return every week. How to fix it?

EDIT:

I do not use valid codes in my table because I did not know the expired / invalid previous tasks. I changed my laptop for the year 2040, and there really was a mysql error. I changed my plans, and I think it’s better to repeat the task for a year, with the possibility of deleting or renewing for another year. Then I forgot to mention that when I create a task on Monday with a monthly frequency, the task returns to my table on the first Monday of the next month. In addition, tasks with a frequency of "once" appear every day in my table when trying to solve your problem.

EDIT

@SparKot, usual with a monthly frequency, the task is considered, for example, 2013-03-05, and the next time will be 2013-04-05, 2013-05-05 and so on. Although in this scheduler the monthly task set, for example, on Tuesday 2013-03-05, should appear on the first Tuesday of the next month (April: 2013-04-02, May: 2013-05-07, June: 2013-06-04 )

Answer 1

Assuming the presence of confidence columns in the table:

 SELECT * FROM planner WHERE now() >= valid_from AND now() <= valid_to AND (frequency = 'daily' OR (frequency = 'once' AND date(now()) = date) OR (frequency = 'weekly' AND 0 = (abs(datediff(now(), date)) % 7)) OR (frequency = 'two-weekly' AND 0 = (abs(datediff(now(), date)) % 14)) OR (frequency = 'monthly' AND ceil(dayofmonth(now())/7) = ceil(dayofmonth(date)/7) AND dayofweek(now()) = dayofweek(date)) ); 

No confidence columns: you can get expired / invalid previous tasks that have not yet been deleted:

 SELECT * FROM planner WHERE (frequency = 'daily' OR (frequency = 'once' AND date(now()) = date) OR (frequency = 'weekly' AND 0 = (abs(datediff(now(), date)) % 7)) OR (frequency = 'two-weekly' AND 0 = (abs(datediff(now(), date)) % 14)) OR (frequency = 'monthly' AND ceil(dayofmonth(now())/7) = ceil(dayofmonth(date)/7) AND dayofweek(now()) = dayofweek(date)) );