PHP日历以及来自MySQL数据库的重复事件 [英] PHP calendar with recurring events from MySQL database
问题描述
更新: 以下是我的原始问题.查看我的答案，看看如何解决.
UPDATE: Below is my original question. See my answer to see how I solved it.
我正在尝试使用MySQL数据库表'churchcal_events'中的事件填充日历.我可能有一个针对特定日期的一次性事件，并且可以为每个星期一，每个其他星期四或每个月的第二个星期五设置重复发生的事件.
I am trying to populate my calendar with events from MySQL database table 'churchcal_events'. I might have one-time events for specific dates and recurring events that can be set for Every Monday, Every Other Thursday, or Every Month on Second Friday.
一次性事件没有问题.每周都有活动(每隔一周，每隔一个).但是每月事件仅显示在第一个月，而不显示在随后的几个月.
One-time events are no problem. And weekly events work (every week, every other). But an every-month-event shows only on the first month, not on the months following.
churchcal_events表-排除对该问题不重要的字段
churchcal_events table - excluding fields that are not important to this question
+----+----------------+------------+-----------+------------+------------+
| id | name | recurring | frequency | recur_type | recur_day |
+----+----------------+------------+-----------+------------+------------+
| 1 | Test Weekly | 1 | 1 | W | Sunday |
| 2 | Test Bi-Weekly | 1 | 2 | W | Monday |
| 3 | Test Monthly | 1 | 1 | M | Friday |
+----+----------------+------------+-----------+------------+------------+
php代码-每个月的每一天都在循环中
php code - inside of a loop for each day of the month
//query all events
$get_events = db_query("SELECT * FROM {churchcal_events}
WHERE (MONTH(date) = :month AND YEAR(date) = :year AND DAY(date) = :day) OR
(recurring = :recur AND recur_day LIKE :calendar_day)
ORDER BY starttime",
array(
':month' => $month,
':year' => $year,
':day' => $list_day,
':recur' => '1',
':calendar_day' => '%' . date('l', strtotime($month . '/' . $list_day . '/' . $year)) . '%',
));
foreach($get_events as $event) {
//see if events belong to this calendar
$calendar_assign = db_query("SELECT * FROM {churchcal_assign} WHERE event_id = :event_id AND calendar_id = :cal_id",
array(
':event_id' => $event->id,
':cal_id' => $cal_id,
));
if($calendar_assign->rowCount() > 0) {
//if recurring, see if event should be on this day
if($event->recurring == '1') {
$recur_day = $event->recur_day;
$recur_freq = $event->frequency;
$recur_type = $event->recur_type;
$recur_start = new DateTime(date('Y-m-d', strtotime($event->recur_start)));
$recur_end = new DateTime(date('Y-m-d', strtotime($event->recur_end)));
$recur_start->modify($recur_day);
$recur_interval = new DateInterval("P{$recur_freq}{$recur_type}");
$recur_period = new DatePeriod($recur_start, $recur_interval, $recur_end);
foreach($recur_period as $recur_date) {
if($recur_date->format('Ymd') == date('Ymd', strtotime($month . '/' . $list_day . '/' . $year))) {
$calendar .= calendar_event($event-id, $event->name, $event->starttime);
}
}
}
如何使示例churchcal_events表中的ID"3"显示在每月的第一个星期五?
推荐答案
万一有人想做类似的事情，我将发布我在最近五个小时内编写并测试过的代码.
In case anyone would like to do something similar, I will post the code that I wrote and tested within the last five hours.
我决定将我的两个表转换为三个表，将所有重复出现的选项移至第三个表:
I decided to convert my two tables into three, moving all of the recurring options to the third table:
'churchcal_events'-这里没有我未包含的事件信息的其他字段
'churchcal_events' - there are additional fields for event info that I haven't included here
+----+-----------+------------+------------+------------+-----------+
| id | name | date | starttime | endtime | recurring |
+----+-----------+------------+------------+------------+-----------+
| 1 | Event 1 | 2013-04-01 | 10:30:00 | 12:00:00 | 0 |
| 2 | Event 2 | | 14:00:00 | 15:00:00 | 1 |
| 3 | Event 3 | | 09:00:00 | | 1 |
| 4 | Event 4 | | 19:00:00 | 21:00:00 | 1 |
+----+-----------+------------+------------+------------+-----------+
"churchcal_assign" -将事件路由到适当的日历(因为整个网站上会有不同部门的日历)
'churchcal_assign' - routes events to appropriate calendars (because there will be calendars for different departments throughout the website)
+----------+-------------+
| event_id | calendar_id |
+----------+-------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
+----------+-------------+
'churchcal_recur_events'-以下是每个事件重复发生的规则
'churchcal_recur_events' - Here are the rules for each event's recurrence
+----------+-----------+------------+----------------+------------+-------------+-----------+
| event_id | frequency | recur_type | recur_day_num | recur_day | recur_start | recur_end |
+----------+-----------+------------+----------------+------------+-------------+-----------+
| 2 | 1 | Week | NULL | Sunday | NULL | NULL |
| 3 | 2 | Week | NULL | Wednesday | 2013-04-01 | NULL |
| 4 | 2 | Month | third | Friday | 2013-04-01 | NULL |
+----------+-----------+------------+----------------+------------+-------------+-----------+
新代码(PHP)-这是一天循环的事情
New Code (PHP) - This is in a day loop
//query all events
$get_events = db_query("SELECT * FROM {churchcal_events ce, churchcal_recur_events cre}
WHERE (MONTH(ce.date) = :month AND YEAR(ce.date) = :year AND DAY(ce.date) = :day) OR
(ce.id = cre.event_id AND ce.recurring = :recur AND cre.recur_day = :calendar_day)
ORDER BY starttime",
array(
':month' => $month,
':year' => $year,
':day' => $list_day,
':recur' => '1',
':calendar_day' => date('l', strtotime($month . '/' . $list_day . '/' . $year)),
));
foreach($get_events as $event) {
//see if events belong to this calendar
$calendar_assign = db_query("SELECT * FROM {churchcal_assign} WHERE event_id = :event_id AND calendar_id = :cal_id",
array(
':event_id' => $event->id,
':cal_id' => $cal_id,
));
if($calendar_assign->rowCount() > 0) {
//one-time events
if(($event->recurring != '1') && ($single_event_id != $event->id)) {
$calendar .= calendar_event($event->id, $event->name, $event->starttime);
$single_event_id = $event->id;
//$calendar .= $single_event_id;
}
//monthly recurring events
if(($event->recur_type == 'Month') && ($event->recurring == '1')
//day is on or before end date
&& (($event->recur_end >= date('Y-m-d', strtotime($month . '/' . $list_day . '/' . $year))) || ($event->recur_end == '0000-00-00') || ($event->recur_end == NULL))
//day is on or after start date
&& (($event->recur_start <= date('Y-m-d', strtotime($month . '/' . $list_day . '/' . $year))) || ($event->recur_start == '0000-00-00') || ($event->recur_start == NULL))
//day is equal to date of recurrence, i.e. first friday, third monday, etc
&& ($year . date('m', strtotime($year . '/' . $month . '/' . $list_day)) . $list_day == date('Ymj', strtotime($event->recur_day_num . ' ' . $event->recur_day . ' of ' . date('F', strtotime($month . '/' . $list_day . '/' . $year)) . ' ' . $year)))
//day is in agreement with month frequency, i.e. every month, every other, etc. from start date
&& ($month % $event->frequency == date('m', strtotime($event->recur_start)) % $event->frequency)) {
$calendar .= calendar_event($event->id, $event->name, $event->starttime);
}
//weekly recurring events
if(($event->recur_type == 'Week') && ($event->recurring == '1')
//day is on or before end date
&& (($event->recur_end >= date('Y-m-d', strtotime($month . '/' . $list_day . '/' . $year))) || ($event->recur_end == '0000-00-00') || ($event->recur_end == NULL))
//day is on or after start date
&& (($event->recur_start <= date('Y-m-d', strtotime($month . '/' . $list_day . '/' . $year))) || ($event->recur_start == '0000-00-00') || ($event->recur_start == NULL))
//day is in agreement with week frequency, i.e. every week, every other, etc. from start date
&& (date('W', strtotime($month . '/' . $list_day . '/' . $year)) % $event->frequency == date('W', strtotime($event->recur_start)) % $event->frequency)) {
$calendar .= calendar_event($event->id, $event->name, $event->starttime);
}
}
}
如果您不是像我一样在Drupal模块中创建此代码，则可以使用自己的数据库查询功能(例如PHP的默认"mysql_query()"；)替换"db_query()"；
If you are not creating this in a Drupal module like I am, you can replace 'db_query()' with your own database query function like PHP's default 'mysql_query()';
这篇关于PHP日历以及来自MySQL数据库的重复事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
