Search code examples
mysqldaterecurrence

mySQL generating rows based on a recurring date

I have a mySQL table which contains events, details on the event, the event date and a recurring value - which is how often the event recurs in days.

e.g. Date: 2012-12-03, Recurrance: 7 - it will recurr every 7 days.

I am now looking to search on this by date and would like the row to be returned if the search was for 2012-12-03, 2012-12-10, 2012-12-17, 2012-12-24...+7 days.

Currently, the search is for a specific day:

SELECT 
    id, 
    title, 
    venue, 
    date_format(datetime, '%I:%i%p') AS time 
from 
    bt_db_eventcal 
WHERE 
    DATE(datetime) = '" . $date . "'

Can anyone help expand this to return rows based on the recurrance?

Thanks

Solution

  • its a simple math really. you subtract the saved date from the search date and mod the value by the recurrence. if the value is 0 then it is good.