php - 如何使用存储的时间戳数据计算每个月的报告总数并以 json 数组格式输出

Question

我正在创建一个监视网站，用户可以在其中举报。这些报告存储在名为 sent_report 的表中，每个报告都有自己的时间戳值。

现在我想根据这些时间戳值获取每个月(即一月、二月...十二月)的报告总数，并将其存储在类似数组的格式中，可用于绘制图表使用 JavaScript 库

我尝试了很多 SQL 查询都无济于事，我什至都不记得它们了

我希望我的输出看起来像这样

Jan: 0,Feb: 0,Marc: 3,Apr: 7,...,Dec: 10

我的 table 看起来像这样

Id | Surname | Firstname |   Content   |    Report_date
 1 |  David  |  Johnson  | Lorem Ipsum | 2019-02-24 01:14:18
 2 |  Jason  |   Jimmy   | Lorem Ipsum | 2019-08-24 15:44:20
 3 |   Sam   |  Robinson | Lorem Ipsum | 2019-08-24 13:54:44
 4 |  Karla  |  Thompson | Lorem Ipsum | 2019-05-24 09:14:12

请注意，我并不是试图获取每行的内容，而是获取有关时间戳的计数

或者如果可能的话采用可用于在 JavaScript 中绘制图形的数组格式。我怎样才能做到这一点？

已更新我的 PHP 代码

require '../../users/docs/connection.php';

$query = sprintf("SELECT 
    SUM(IF(MONTH(report_time) = 1 , 1 , 0)) AS Jan,
    SUM(IF(MONTH(report_time) = 2 , 1 , 0)) AS Feb,
    SUM(IF(MONTH(report_time) = 3 , 1 , 0)) AS Mar,
    SUM(IF(MONTH(report_time) = 4 , 1 , 0)) AS Apr,
    SUM(IF(MONTH(report_time) = 5 , 1 , 0)) AS May,
    SUM(IF(MONTH(report_time) = 6 , 1 , 0)) AS Jun,
    SUM(IF(MONTH(report_time) = 7 , 1 , 0)) AS Jul,
    SUM(IF(MONTH(report_time) = 8 , 1 , 0)) AS Aug,
    SUM(IF(MONTH(report_time) = 9 , 1 , 0)) AS Sep,
    SUM(IF(MONTH(report_time) = 10 , 1 , 0)) AS 'Oct',
    SUM(IF(MONTH(report_time) = 11 , 1 , 0)) AS Nov,
    SUM(IF(MONTH(report_time) = 12 , 1 , 0)) AS 'Dec'
FROM sent_reports
WHERE YEAR(report_time) = 2019");

$result = $conn->query($query);
$data = array();

foreach ($result as $row) {
    $data[] = $row;
}

$result->close();
$conn->close();

print json_encode($data);

更新我的 JavaScript 代码

$(document).ready(function () {
    $.ajax({
        url: "http://localhost/projects/DiseaseSurvey/admin/docs/charts-data.php",
        method: "GET",
        success: function (data) {
            console.log(data);
        },
        error: function (data) {
            console.log(data);
        },
    })
})

Answer 1

1:每月一行(仅包括发送任何报告的月份):

如果您想要每月一行，请确保您仅选择了一年，否则对于 Jan 月份，您将从所有年份中计数。

SELECT DATE_FORMAT(Report_date,'%b') AS Month_Name, COUNT(0) AS Report_Count
FROM sent_report
WHERE YEAR(Report_date) = 2019         -- It is important otherwise you can have multiple Jan month from year 2018, 2019...
GROUP BY MONTH(sent_datetime);


Output:
+---------------------------+
| Month_Name | Report_Count |
| Jan        | 10           |
| Feb        | 5            |
| ..         | ..           |
+---------------------------+

2:每月一行 - 包括所有月份

获取所有月份，即使没有针对这些月份发送报告。创建一个包含所有月份名称的临时表，然后使用上述查询进行左连接。

SELECT 
    months.Month_Name,
    IFNULL(reports.Report_Count, 0) Report_Count
FROM (
   SELECT 'Jan' AS Month_Name 
   UNION SELECT 'Feb'
   UNION SELECT 'Mar'
   UNION SELECT 'Apr'
   UNION SELECT 'May'
   UNION SELECT 'Jun'
   UNION SELECT 'Jul'
   UNION SELECT 'Aug'
   UNION SELECT 'Sep'
   UNION SELECT 'Oct'
   UNION SELECT 'Nov'
   UNION SELECT 'Dec'
) AS months
LEFT JOIN (
    SELECT 
        DATE_FORMAT(Report_date,'%b') AS Month_Name, 
        COUNT(0) AS Report_Count
    FROM sent_report
    WHERE YEAR(Report_date) = 2019         -- It is important otherwise you can have multiple Jan month from year 2018, 2019...
    GROUP BY MONTH(Report_date)
) AS reports ON months.Month_Name = reports.Month_Name;

3:一行包含所有月份作为列。

SELECT 
    SUM(IF(MONTH(Report_date) = 1 , 1 , 0)) AS Jan,
    SUM(IF(MONTH(Report_date) = 2 , 1 , 0)) AS Feb,
    SUM(IF(MONTH(Report_date) = 3 , 1 , 0)) AS Mar,
    SUM(IF(MONTH(Report_date) = 4 , 1 , 0)) AS Apr,
    SUM(IF(MONTH(Report_date) = 5 , 1 , 0)) AS May,
    SUM(IF(MONTH(Report_date) = 6 , 1 , 0)) AS Jun,
    SUM(IF(MONTH(Report_date) = 7 , 1 , 0)) AS Jul,
    SUM(IF(MONTH(Report_date) = 8 , 1 , 0)) AS Aug,
    SUM(IF(MONTH(Report_date) = 9 , 1 , 0)) AS Sep,
    SUM(IF(MONTH(Report_date) = 10 , 1 , 0)) AS 'Oct',
    SUM(IF(MONTH(Report_date) = 11 , 1 , 0)) AS Nov,
    SUM(IF(MONTH(Report_date) = 12 , 1 , 0)) AS 'Dec'
FROM sent_report
WHERE YEAR(Report_date) = 2019         -- It is important otherwise you can have multiple Jan month from year 2018, 2019...

Output:

+-----------------------------------------------------------------------+
| Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| 2   | 5   | 7   | 1   | 0   | 1   | 8   | 1   |  4  | 2   | 1   | 2   |
+-----------------------------------------------------------------------+