Create Duplicate Records on SELECT for Calendar Date Range
- by peterallcdn
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
