PHP日历，包含来自MySQL数据库的重复事件

UPDATE: Below is my original question. See my answer to see how I solved it.

更新:以下是我原来的问题。看到我的答案,看看我是如何解决它的。

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.

我试图用MySQL数据库表'churchcal_events'中的事件填充我的日历。我可能会为特定日期和周期性事件设置一次性事件,可以设置为每周一,每隔周四或第二个星期五的每个月。

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 table - excluding fields that are not important to this question

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     |
+----+----------------+------------+-----------+------------+------------+

php code - inside of a loop for each day of the month

php代码 - 每月每天的循环内部

//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);

            }

          }

        }

How can I make ID '3' from the example churchcal_events table show up the first Friday of every month?

如何从示例churchcal_events表中显示ID'3'出现在每个月的第一个星期五?

1 个解决方案

#1

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' - there are additional fields for event info that I haven't included here

'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' - routes events to appropriate calendars (because there will be calendars for different departments throughout the website)

'churchcal_assign' - 将事件路由到适当的日历(因为整个网站的不同部门都会有日历)

+----------+-------------+
| event_id | calendar_id | 
+----------+-------------+
| 1        | 1           |
| 2        | 1           |
| 3        | 1           |
| 4        | 1           |
+----------+-------------+

'churchcal_recur_events' - Here are the rules for each event's recurrence

'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      |
+----------+-----------+------------+----------------+------------+-------------+-----------+

New Code (PHP) - This is in a day loop

新代码(PHP) - 这是一天的循环

    //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);

          }

      }

    }

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()';

如果您不是像我一样在Drupal模块中创建它,您可以将'db_query()'替换为您自己的数据库查询函数,如PHP的默认'mysql_query()';

#1