I have worked with M$ SQL, but I am now switching to MySQL. Something I think should work is not, but I can not figure out why...
I am trying to build a calendar with SQL / PHP that displays historic events coming up for next 30 days as well as Month long and week long celebrations (like both July 4th 1776 and Gay Pride Month).
MySQL Server version: 5.1.52
SELECT Name, URL, startDate, endDate, annual, wholemonth,
monthname(startDate) as m1, monthname(endDate) as m2,
DATE_FORMAT(startDate, '%b %e') as md1, DATE_FORMAT(endDate, '%b %e') as md2,
DATE_FORMAT(endDate, '%e') as d2, DATE_FORMAT(startDate, '%Y') as y1,
DATE_FORMAT(endDate, '%Y') as y2, dayofyear(startdate) as doy,
dayofyear(curdate()) as doyc
FROM Calendar
where
(curdate() between startDate and endDate)
OR
(annual = 1 and wholemonth = 1 and month(startDate) = month(curdate()))
OR
(annual = 1) and (( doy - doyc) % 365) < = 30 ) -- this line is causing the problem
Order by doy asc LIMIT 0, 30
My DB is set up with these cols: PID, startDate,endDate,Name,URL,annual,wholemonth
I know this is not normalized with the whole month tag, but I do not really care. There will be at most ~1000 events in here, and it will be just easier to keep that flag.
What I want to do with the line that is causing an error is include events that are in the near future next 30 days (even if it is toward the end of December).