Return multiple copies slightly modified

Question

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?

Answer 1

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