如何使用PHP和MySQL检索此表的分组信息？

Question

I have a table that looks like this

the column date contains different dates in the range (2015-2009). Is it possible to use one query to retrieve data like this?

Here [2015] - is year that has an array. [01]=>'12', [01] os the month and 12 number of articles in this month i.e.

Answer 1

Provided the date field is actually a date, this should work:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
}

$sql = "SELECT date_format(`date`, \"%Y-%m\") as YearMonth, count(*) as cnt " .
       "FROM someTable " .
       "GROUP BY YearMonth " .
       "ORDER BY YearMonth ";

$data = array();

$result = $conn->query($sql);
if ($result->num_rows > 0) {
    $currentYear = "";
    while($row = $result->fetch_assoc()) {
        $rowYear = substr($row["YearMonth"], 0, 4);
        $rowMonth = substr($row["YearMonth"], 5, 2);
        if ($rowYear != $currentYear) {
            $currentYear = $rowYear;
            $data[$currentYear] = array();
        }
        $data[$currentYear][$rowMonth] = $row["cnt"];
    }
}

$conn->close();

print_r($data);