The diary is listed on the site month by month - you can click a month and events for that month will show up. Events have a from date and to date and can span across multiple months. This has all been working fine until I added an event running from december 06 to january 07 - it no longer showed up. Here is how my query looked:
(using december 2006 as the selected page as an example)
SELECT * FROM diary WHERE (MONTH(from_date) <= MONTH('2006-12-01') AND MONTH(to_date) >= MONTH('2006-12-01') AND YEAR(from_date) = YEAR('2006-12-01')) OR (MONTH(from_date) <= MONTH('2006-12-01') AND MONTH(to_date) >= MONTH('2006-12-01') AND YEAR(to_date) = YEAR('2006-12-01')) ORDER BY from_date, to_date
Now looking back it is obvious that this will not work as it is looking for a match in the years. After some research and trial and error I have come up with the following query:
SELECT * FROM diary WHERE (UNIX_TIMESTAMP('2006-12-01') BETWEEN UNIX_TIMESTAMP(from_date) AND UNIX_TIMESTAMP(to_date)) ORDER BY from_date, to_date
However this is still not working.
This is really bugging me and any help will be greatly appreciated.