php - 过去12个月内使用PHP进行SQL计数

我有一个MySQL / MariaDB数据库，其中包含用PHP函数date()创建的时间戳。

像这样：

ID | Name | Date
--------------------------
12 | John | 123456789
13 | Mike | 987654321
...
29 | Rick | 123498765
30 | Adam | 987651234

现在，我需要获取自当前人员以来最近12个月的每月所有人员的数量。

例：

当前是三月，所以我需要获得以下信息：

March has 3 persons
February has 14 persons
January has 16 persons
December has 13
November has 16
October has 30
...

并继续。

如何使用PHP做到这一点？

到目前为止，我有这个简单的for循环，该循环返回过去12个月，然后返回每个时间戳，但是我真的不知道如何将返回的时间戳与DB时间戳进行比较。

for ($number = 0; $number <= 11; $number++) {
    $month = strtotime("-".$number." month");

    echo "Timestamp: ".$month."<br>";
    echo "Month: ".date("F", $month);
    echo "<hr>";
};

该循环返回如下内容：

Timestamp: 1488398035
Month: March
---
Timestamp: 1485978835
Month: February
---
Timestamp: 1483300435
Month: January
---
Timestamp: 1480622035
Month: December
---
Timestamp: 1478026435
Month: November
---
Timestamp: 1475348035
Month: October
---
Timestamp: 1472756035
Month: September
---
Timestamp: 1470077635
Month: August
---
Timestamp: 1467399235
Month: July
---
Timestamp: 1464807235
Month: June
---
Timestamp: 1462128835
Month: May
---
Timestamp: 1459536835
Month: April

最佳答案

您可以使用FROM_UNIXTIME和DATE_FORMAT函数分别将时间戳转换为日期并将日期转换为字符串，并获取计数，例如：

SELECT DATE_FORMAT(FROM_UNIXTIME(`date`), '%Y-%m') as `month`, COUNT(ID) as count
FROM table
GROUP BY `month`;

用php

php - 过去12个月内使用PHP进行SQL计数