php - MySQL Query自动将永久事件填充到表中，无需重复输入过程

Question

我正在使用 MySQL dB，两个表名为“perm_events”用于重复事件，“reservations”用于存储不重复的单个事件。 “perm_events”表存在的唯一目的是不必重复输入相同的信息。这是关于“perm_events”的问题。

例如:

• 事件 A:计划每周日上午 8 点到 9 点无限期举行。
• 事件 B:计划于 2019 年 1 月 12 日至 2019 年 12 月 31 日期间的每周二、周四举行。

我正在使用 PHP UI 收集有关事件的信息，我想根据从 UI 收集的信息编写查询。

收集的信息:(这将存储在“perm_events”表中。)

Event name: Event B
Location:   Rm. 1
Start time:  9:00AM
End Time:   10:00AM
Weekdays:   Tue, Thu
Start Date: 12/1/2019 (If NULL, the event is permanent)
End Date:   12/31/2019

根据上面收集的信息，我想填充一个包含以下内容的表格:

   DATE    WKDAY EVENT NAME LOCATION STARTING_TIME ENDING_TIME 
---------- ----- ---------- -------- ------------- -----------
12/03/2019  TUE   EVENT B     Rm. 1    9:00AM        10:00AM
12/05/2019  THU   EVENT B     Rm. 1    9:00AM        10:00AM
12/10/2019  TUE   EVENT B     Rm. 1    9:00AM        10:00AM
12/12/2019  THU   EVENT B     Rm. 1    9:00AM        10:00AM   
                            .
                            . 
12/31/2019  TUE   EVENT B     Rm. 1    9:00AM        10:00AM

目前，我正在将 ABOVE 事件作为一系列事件输入，持续时间从 12/3/2019 到 12/31/2019，如下所示的 UI(以下存储在“预订”表中):

event_name: Event B(1)
location: Rm. 1
start_time: 9:00AM
end_time: 10:00AM
date: 12/3/2019
end_date: 12/31/2019
(Reserve Button)

预订此事件后，我将重复该过程继续下一个过程，直到过程完成 - 如下所示。

event_name: Event B(2)
location: Rm. 1
start_time: 9:00AM
end_time: 10:00AM
date: 12/5/2019
end_date: 12/31/2019
(Reserve Button)
.
.
and so on...

我必须执行这个看似无穷无尽的过程，直到过程完成(我认为上面的示例中有 8 或 9 个)。此外，对于没有结束日期的永久事件，我必须一遍又一遍地不断输入相同的事件信息。

简而言之，我希望通过不为延长的事件或永久事件重复输入事件信息来节省时间 - 一个接一个。这将是一个更有效率的过程。

我该怎么做呢？

提前感谢所有阅读我的问题的专家。

Answer 1

我终于在 Strawberry 的帮助下完成了任务(非常感谢 Strawberry)。

我最终没有使用 MySQL 查询 - 相反，我使用了“按位运算符”来比较单个工作日位与组合的周位，正如 Strawberry 向我介绍的那样。 (请参阅下面的代码和以下注释:

//比较按位运算符 (&) $wkDayBit(1 到 127) 和 $daybit(pow(2,$i++)).)

“按位运算符”技术是解决此任务的关键。

下面的代码是我目前使用的代码的简化版本。

我希望下面的内容能帮助处于类似情况的人。

<?php
  // Initialize variables.
  $wkDayBit  = 0;
  $count     = 1;
  $frmWkday  = array('Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat');
  $wkdays    = array('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday');
  $wkDayBits = array(1, 2, 4, 8, 16, 32, 64);
  $permEvtArray = array();

  // Credit to HADI for the below function.
  function dateRange($first, $last, $step = '+1 day', $format = 'Y-m-d') {
    $dates   = array();
    $current = strtotime($first);
    $last    = strtotime($last);

    while ($current <= $last) {
      $dates[] = date($format, $current);
      $current = strtotime($step, $current);
    }
    return $dates;
  }

  if (isset($_POST['addPermEvent'])) {
    if (isset($_POST['wkday'])) {
      $ttlRep = 0;                        // Initialize the variable $ttlRep.
      $tmpWkDayBits = $_POST['wkday'];    // Assign array 'wkday' to $tmpWkDayBits.

      // Loop through the newly assigned $tmpWkDayBits using foreach loop
      foreach ($tmpWkDayBits as $row => $tmpWkDayBit) {
        $wkDayBit = $wkDayBit + $tmpWkDayBit;
      }
    }

    // Call function dateRange() & get all dates that are between start date and end  date.
    $dateRange = dateRange($_POST['stDate'], $_POST['endDate']);

    // Convert the bitwise integer to an array (Credit to Sammitch for this bit).
    for ($i=0; $i<7; $i++) {          // $i = loop 0 thru 6
      $daybit = pow(2,$i);            // 1, 2, 4, 8, 16, 32, 64

      // Compare bitwise operator(&) $wkDayBit(1 thru 127) and $daybit(pow(2,$i++)).
      if ($wkDayBit & $daybit) {
        $newWkdays[]=$frmWkday[$i];
      }
    }

    // Increment $ttlRep with # days of dated perm events one at a time (e.g., #/$ttlRep)
    foreach ($dateRange as $rsvDate) {
      if (in_array(date('D', strtotime($rsvDate)), $newWkdays)) {
        $ttlRep ++;
      }
    }

    /*-----------------------------------------------------
    Compare to see if checked weekday(s), that is(are)
    in the date-range($dateRange) is in $newWkdays[] array. 
     +-----------------------------------------------------*/
    foreach ($dateRange as $rsvDate) {
      if (in_array(date('D', strtotime($rsvDate)), $newWkdays)) {

        // Add a counter to the event title.
        $eventTitle = $_POST['permEvTtl'] . " [" . $count . "/" . $ttlRep . "]";
        $count++;
    
        // Assign each event details to an array.
        $permEvt = [
          'eventTitle' => $eventTitle,                         // Text field
          "reservDate" => $rsvDate,                            // 2020-06-01 format
          "wkDay" => $wkdays[date('w', strtotime($rsvDate))],  // $wkdays['n']
          "reservTime" => date('g:i A', strtotime($_POST['stTime'])), // 8:00 PM
          "endTime" => date('g:i A', strtotime($_POST['endTime'])), // 8:00 PM format
          'userGr' => $_POST['userGr'],                             // Text field
          'rmReserv' => $_POST['rmReserv'],                         // Text field
          'reservCreated' => date('Y-m-d H:i:s')  // 2020-06-01 22:32:06 format
        ];

        $permEvtArray[] = $permEvt;
      }
    }
  }
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <meta http-equiv="X-UA-Compatible" content="chrome">
  <link rel='stylesheet' type='text/css' href='assets/css/testCodes.css'>
  <title>Permanent Events Test Codes</title>
</head>
<body>
<div class="permEvent-form">
  <form method="POST">
    <h3>BOOK PERM EVENT</h3>
  <div>
    <label for="permEvTtl">Perm Event Title:</label>
    <input type="text" name="permEvTtl" id="permEvTtl" required placeholder="Title"><br>
    <label for="rmReserv">Room/Loc:</label>
    <input type="text" name="rmReserv" id="rmReserv" required placeholder="Room"><br>
  </div>
  <div>
    <label for="stDate">Start/End Dates:</label>
    <input type="date" name="stDate" id="stDate" required>
    <input type="date" name="endDate" id="endDate" required>
  </div>
  <div>
    <label for="stTime">Start/End Times:</label>
    <input type="time" name="stTime" id="stTime" required>
    <input type="time" name="endTime" id="endTime" required>
  </div>
  <div>
    <label for="userGr">User Group:</label>
    <input type="text" name="userGr" id="userGr" required placeholder="Group"><br>
  </div>
  <div class="wkday_chk">
    <h3>Weekdays</h3>
    <?php for ($i=0; $i<7; $i++): ?>
      <input type="checkbox" name="wkday[]" id="<?php echo $frmWkday[$i]?>" value="<?php echo $wkDayBits[$i]?>">
      <label for="<?php echo $frmWkday[$i]?>"><?php echo $frmWkday[$i]?></label>
    <?php endfor ?>
  </div>
  <div class="permAdd_btn">
    <input type="submit" name="addPermEvent" value="ADD PERM EVENT">
  </div>
  </form>
</div>

<div class="reserv-table">
  <table>
    <thead>
      <tr>
        <th>DATE<br>WKDAY</th>
        <th>STA TIME<br>END TIME</th>
        <th>EVENT TITLE</th>
        <th>LOCATION<br>GROUP</th>
        <th>RESERVED ON</th>
      </tr>
   </thead>
   <tbody>
      <!-- loop through rm_reserved VIEW to populate Reservation table -->
      <?php if ($permEvtArray != NULL):?> 
        <?php foreach ($permEvtArray as $rsv):?>
          <tr>
            <td><strong><?php echo $rsv["reservDate"]."<br>".$rsv["wkDay"]?></strong></td>
            <td><?php echo $rsv["reservTime"]."<br>".$rsv["endTime"]?></td>
            <td><?php echo $rsv["eventTitle"]?></td>
            <td><?php echo $rsv["rmReserv"]."<br>".$rsv["userGr"]?></td>
            <td><?php echo "<strong>".$rsv["reservCreated"]?></td>
          </tr>
        <?php endforeach?>
      <?php endif ?>  
    </tbody>
  </table>
</div>
</body>
</html>