Create Duplicate Records on SELECT for Calendar Date Range

Question

Hey all,

I've built a pretty shnazzy calendar system but there is one tweak that I need to make so that I'm completely happy with it.

My calendar has three tables:

calevents - The calendared event.

caldates - The occurrences and date-range of each occurrence for each event.

calcats - The categories that can be applied to an event.

The short:

For each calevent, there can be many caldates, one for each occurrence of calevent. So a calevent that repeats weekly and spans 3 days might have caldates like this:

date_id   date_eid   date_start   date_end
2         37         2010-06-21   2010-06-23
3         37         2010-06-28   2010-06-30
7         37         2010-07-05   2010-07-07
9         37         2010-07-12   2010-07-14

What I want to do, is when selecting all the caldates for a specified month such as 2010-06, to return not just the two records above, but instead a record for each date in the range of date_start and date_end for each caldate.

So if I searched for 2010-06, I would get:

date_id   date_eid   date_start   date_end     date_day
2         37         2010-06-21   2010-06-23   2010-06-21
2         37         2010-06-21   2010-06-23   2010-06-22
2         37         2010-06-21   2010-06-23   2010-06-23
3         37         2010-06-28   2010-06-30   2010-06-28
3         37         2010-06-28   2010-06-30   2010-06-29
3         37         2010-06-28   2010-06-30   2010-06-30

The Long:

The reason I want to do this, is so when displaying a list of events(calevents) for a specified month, an occurrence(caldates) of that event will be displayed for EACH of the days it spans.

I could do this with php by looping through each day of the current month and displaying a copy of each caldate if the month day falls between date_start and date_end. But doing it this way will prevent me from using record pagination if needed.

For example, if for a specified month the following caldates were returned:

date_id   date_eid   date_start   date_end
2         37         2010-06-21   2010-06-27
94        53         2010-06-09   2010-07-08

Doing record pagination would see this as only 2 records("rows"). But looping through them with PHP would generate 29 "rows".

So, I figure if I use mysql to create each row instead of PHP, I can achieve the same thing AND still be able to use pagination if a month has a lot of events/dates.

As far as performance goes, I'm not sure which option is more efficient. Both would send the same amount of info to the browser, so it's really only the work required to generate the info that matters.

My current query which fetches all the occurrences for a specified month, and to make things just a little more complicated... joins them with their event and category, looks like this:

$sql_to_execute = "
SELECT
  date_id,
  date_eid,
  date_start,
  date_end,
  event_id,
  event_title,
  event_category,
  event_private,
  event_location,
  SUBSTRING_INDEX(event_detailsstripped, ' ', 40) AS event_detailsstripped,
  event_time,
  event_starttime,
  event_endtime,
  event_active,
  cat_colour
FROM
  (
  caldates
LEFT JOIN
  calevents
ON
  caldates.date_eid = calevents.event_id
  )
LEFT JOIN
  calcats
ON
  calevents.event_category = calcats.cat_id
WHERE
  date_start <= '".mysql_real_escape_string($dbi_list_end_date)."'
  AND date_end >= '".mysql_real_escape_string($dbi_list_start_date)."'
  ".$dbi_category."
ORDER BY
  date_start ASC
"; 

Any help or advice would be greatly appreciated!

Thanks,

Peter

Answer 1

The easiest thing to do what you want would be to:

• Generate a table of ALL dates (within a reasonable range)

• Join caldates table to all_dates table:

  SELECT * FROM caldates, all_dates
  WHERE all_dates.date BETWEEN "2010-06-01" AND "2010-06-30"
  AND   all_dates.date BETWEEN caldates.date_start AND caldates.date_end
  

  Add a join to calevents as you wish