使用PHP在过去12个月中计算所有SQL的SQL

Question

I have a MySQL/MariaDB database which includes timestamps created with the PHP function date().

Like this:

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

Now I need to get the count of all persons per month based in the last 12 months since the current one.

Example:

Currently is March, so I need to get something like this:

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

And goes on.

How can I do this with PHP?

---

So far I have this simple for loop which returns last 12 months and then the timestamp for each, but I don't really know how to compare the returned timestamp with the DB timestamp.

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

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

That loop returns something like this:

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

Answer 1

You can use FROM_UNIXTIME and DATE_FORMAT functions to convert timestamp into date and date into string respectively, and get the counts, e.g.:

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