In my events table there are records that have a daily flag that indicate that this event must be repeated each day:
CREATE TABLE IF NOT EXISTS `events` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`start` datetime DEFAULT NULL,
`title` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`description` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`daily` tinyint(1) NOT NULL,
) ENGINE=InnoDB;
When I try to fill a calendar with this data I need a row for each item, it is, if the calendar ask for all events this week, daily flaged events must return 7 events (one for each week day) with the same data (title, description, etc), but with a different start day.
Is it possible to do from MySQL?
Usually I would use a table that contains each of the single date.
Example
create table daily
(
/* FYI, date is not a reserved keyword */
date date
);
insert into daily values ('2011-09-11'), ('2011-09-12'),
('2011-09-13'), ('2011-09-14'), ('2011-09-15'),
('2011-09-16'), ('2011-09-17');
alter table daily add index (date);
select daily.date as daily_date,
weekly.start, weekly.id, weekly.title, weekly.description
from daily
inner join
(
select events.id, events.title,
events.description, events.start, events.daily
from events
where events.start between '2011-09-11' and '2011-09-17'
) as weekly
on date(weekly.start)=daily.date
union
/* query for recurr */
select daily.date as daily_date,
recurr.start, recurr.id, recurr.title, recurr.description
from daily
inner join
(
select events.id, events.title,
events.description, events.start, events.daily
from events
where events.daily=1 AND events.start between '2011-09-11' and '2011-09-17'
)as recurr
on date(recurr.start)<=daily.date
order by daily_date;
It would be more efficient if you break the start column to two column (date, time).
The drawback of this method is on the pre-create of table daily and loaded it with lots of day value.
This should be one time cost and can easily inserted via a for.
Alternatively, you can do a repeat of (from a week start to week end) on the query for recurr
