I have a db-table that holds holidays i.e. when Cristmas, Easter different fasts UN-days and also celebrations from the Hijri (Muslim), Hebrew (Jewish) and Chineese calendars.
All holidays are saved on the date(s) they occur in their own calendar. But not all days are celebrated on a particular date. An example is the four Advent sundays which for the fourth one is between the dates of 18:th and 24:th of december and on a sunday.
There are also other examles as in the Netherlands where some days of celebration are moved when on a Sunday.
When it comes to fasts they can hav varied length and at least in the Swedish protestant church there are also Sunday masses wich are excluded if a more important one occurs.
So my table is set up as follows:
The PHP for the actual SQL, note that it itterates over all calendars.
foreach ($kal as $kalender => $arr_date) {
$go_Y = (int)$arr_date['go']['Y'];
$go_m = (int)$arr_date['go']['m'];
$go_d = (int)$arr_date['go']['d'];
$end_Y = (int)$arr_date['end']['Y'];
$end_m = (int)$arr_date['end']['m'];
$end_d = (int)$arr_date['end']['d'];
$sql = "SELECT
id,
name,
IF (picture_link = '', type_image, picture_link) AS logo,
start_year,
start_month,
start_day,
end_year,
end_month,
end_day,
year_interval,
start_weekday,
shift,
period,
ends_with_holiday,
has_php,
is_type,
in_calendar,
begins_year,
first_year,
bank_holiday,
has_note,
source_link,
COUNT(flagday_id) AS flagdays,
flagday_for_contry, /* if for only one contry, othervise an extra SQL is needed */
flagday_type,
flagday_information,
flagday_source
FROM holiday
LEFT JOIN type ON (type.id = holiday.is_type)
LEFT JOIN flagday ON (holiday.id = flagday_for_holiday)
WHERE is_calendar = $kalender
AND (start_year IS NULL OR start_year <= $end_Y)
AND (end_year IS NULL OR end_year >= $go_Y)
AND IF($end_m < $go_m OR ($end_m = $go_m AND $end_d < $go_d), /* If over new years */
(start_month < $end_m OR start_month = $end_m AND start_day <= $end_d)
OR (end_month > $go_m OR end_month = $go_m AND end_day >= $go_d)
, /* ELSE, if not over new years */
(start_month < $end_m OR start_month = $end_m AND start_day <= $end_d)
AND (end_month > $go_m OR end_month = $go_m AND end_day >= $go_d)
)
GROUP BY id
ORDER BY start_weekday IS NULL DESC,
start_weekday DESC";
$result = mysqli_query($con, $sql) or die(mysqli_error($con));
while ($result && mysqli_num_rows($result) && $row = mysqli_fetch_array($result)) {
if($kalender == 8) { // om den cyclic jewish calendar, one more argument is needed
$out_arr = get_holiday ($ut_arr, $row, $kalender, $go_Y, $go_m, $go_d, $end_Y, $end_m, $end_d, $con, $country, array($kal[4]['go']['Y']));
} else {
$out_arr = get_holiday ($ut_arr, $row, $kalender, $go_Y, $go_m, $go_d, $end_Y, $end_m, $end_d, $con, $country);
}
}
The get_holday() function does the PHP part if 'has_php' = 1 and also adds roman numerals to the end if a holiday is more than one day. But in essence it just puts the row in an multidimensional array: The first part is the gregorian date when it occurs (there are two helperfunctions to convert dates), the second is just an irreation on that Gregorian date and the third is the different columns from the db.
$out_arr['Y-m-d'][$i][$column] = $column_value;
So now the question
This as you can see is cuite time consuming an for now it's only been for personal use so it hasn't been a real problem. If you want an examle (in Swedish) the top left part with the picture icons on my links page
I was instead thinking of outputting all this into an other db table that just holds the id and (Gregorian) date (for the next 10 years) and each year or when a new holiday is added just update the new table. Is this a good idea or does it just create the same data in multiple places and a risk that they don't match in the end?