php - 根据周将日期范围拆分为周和总和列

Question

所以我从数据库中调用数据，其中每条记录都与（列）'start_time'、'no_shows 和'cancelled'相关联。是否成功出席取决于 'no_shows' && 'cancelled' == 0。

我想查看此 SELECT 的结果，并计算每周未出现、取消和出席的次数（基于 start_time）。我怎样才能做到这一点？

用户将提供一个日期范围，数据库将根据该日期范围选择记录。现在这个日期范围必须按周划分，然后得到计数。我完全坚持按周计算。这是我到目前为止所拥有的：

    // Multidimensional Array with [boolean][week #]
  $no_shows_weekly = [
    'no_show' => [],
    'week' => []
  ];
  $cancelled_weekly = [
    'cancelled' => [],
    'week' => []
  ];
  $attended_weekly = [
    'attended' => [],
    'week' => []
  ];

  foreach($result as $r) {
    $start_time = new DateTime($r['start_time']);
    if($r['is_no_show'] == 0 && $r['is_cancelled'] == 0) {
        array_push($attended_weekly['attended'], 1);
        array_push($attended_weekly['week'], date_format($start_time, "W"));
    }
    else {
      array_push($attended_weekly['attended'], 0);
      array_push($attended_weekly['week'], date_format($start_time, "W"));
    }
    array_push($no_shows_weekly['no_show'], $r['is_no_show']);
    array_push($no_shows_weekly['week'], date_format($start_time, "W"));
    array_push($cancelled_weekly['cancelled'], $r['is_cancelled']);
    array_push($cancelled_weekly['week'], date_format($start_time, "W"));
  }
  echo json_encode(array(
       'success'=> 1,
       'msg'=>
         array(
           'No Shows' => $no_shows_weekly,
           'Cancellations' => $cancelled_weekly,
           'Attendend' => $attended_weekly
         )
    ));

我无法对此进行任何计数，而只是提取数据并在相应的一周内分成数组。

我想把数据拉成这样的东西：

Week 50: {No_Shows: 10, Cancelled: 5, Attended: 25} 
Week 52: {No_Shows: 10, Cancelled: 5, Attended: 25} 

一般数据库结构：

+------------+-----------+-----------+
| start_time |  no_shows | cancelled | 
+------------+-----------+-----------+
| 2019-12-20 |     1     |     0     |   
| 2019-12-21 |     0     |     0     |  
| 2019-12-22 |     0     |     1     |  

我也尝试在 MySQL 中这样做：

    SELECT
    WEEKOFYEAR('start_time') AS weekno,
    SUM('is_no_show' = 1) AS no_shows,
    SUM('is_cancelled' = 1) AS cancelled
FROM
    myTable
WHERE
    (
        `start_time` > '2019-12-01' AND `start_time` < '2019-12-07'
    ) AND category LIKE 'Continued Probation'
GROUP BY
    weekno

但是，此语句为我返回 Null。任何建议表示赞赏！谢谢

标签： phpmysql