mySQL generating rows based on a recurring date

Question

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

RisingSun · Accepted Answer · 2012-12-03 15:45:43Z

1

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.

answered Dec 3, 2012 at 15:45

RisingSun

1,73127 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chrismcb Over a year ago

Excellent - thanks. The modified query is

SELECT id, title, venue, date_format(datetime, '%I:%i%p') AS time from bt_db_eventcal WHERE DATEDIFF(DATE(datetime),'2012-12-10' % recurring=0 AND '2012-12-10'<=recur_until

Forgot to mention there was a "recur_until" field too

Collectives™ on Stack Overflow

mySQL generating rows based on a recurring date

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related