更新：
以下是我原来的问题.看到我的答案,看看我是如何解决它的.
以下是我原来的问题.看到我的答案,看看我是如何解决它的.
我试图用MySQL数据库表’churchcal_events’中的事件填充我的日历.我可能会为特定日期和周期性事件设置一次性事件,可以设置为每周一,每隔周四或第二个星期五的每个月.
一次性事件没问题.每周活动(每周,每隔一次)都有效.但每个月的活动仅在第一个月显示,而不是在接下来的几个月.
churchcal_events表 – 排除对此问题不重要的字段
+----+----------------+------------+-----------+------------+------------+ | 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 | +----+----------------+------------+-----------+------------+------------+
//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’出现在每个月的第一个星期五？
如果有人想做类似的事情,我会发布我在过去五个小时内编写和测试的代码.
原文链接：https://www.f2er.com/php/136502.html我决定将我的两个表转换为三个,将所有重复选项移动到第三个表：
‘churchcal_events’ – 我还没有包含此事件信息的其他字段
+----+-----------+------------+------------+------------+-----------+ | 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’ – 将事件路由到适当的日历(因为整个网站的不同部门都会有日历)
+----------+-------------+ | event_id | calendar_id | +----------+-------------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 1 | +----------+-------------+
‘churchcal_recur_events’ – 以下是每个事件重复发生的规则
+----------+-----------+------------+----------------+------------+-------------+-----------+ | 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 | +----------+-----------+------------+----------------+------------+-------------+-----------+
//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",':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",));
if($calendar_assign->rowCount() > 0) {
//one-time events
if(($event->recurring != '1') && ($single_event_id != $event->id)) {
$calendar .= calendar_event($event->id,$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->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_start == '0000-00-00') || ($event->recur_start == NULL))
//day is in agreement with week frequency,i.e. every week,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->starttime);
}
}
}
如果您不是像我一样在Drupal模块中创建它,您可以将’db_query()’替换为您自己的数据库查询函数,如PHP的默认’MysqL_query()’;
