php - 按类别对结果进行分组，然后将结果分成几天？

Question

目前正在使用以下

SELECT category, COUNT(*) as count
FROM
table
GROUP BY category

计算每个类别中发布了多少项目，但是我想做的是为每个类别获取一个结果数组，如下所示

Category: FirstCategory:
    Date: 26/03/2019,
    Posts: 156,
    Date: 27/03/2019,
    Posts: 127
Category: SecondCategory:
    Date: 26/03/2019,
    Posts: 122,
    Date: 27/03/2019,
    Posts: 135

这种事情可能吗？

Answer 1

在 SQL 中按类别和日期分组。然后在 PHP 中按类别对结果进行分组:

$result = $mysqli->query("
    SELECT category, date, COUNT(*) as count
    FROM `table`
    GROUP BY category, date
");

$data = [];
while ($row = $result->fetch_assoc()) {
    $data[$row['category']][$row['date']] = $row['count'];
}

你会得到一个这样的数组:

[
    'FirstCategory' => [
        '26/03/2019' => 156,
        '27/03/2019' => 127,
    ],
    'SecondCategory' => [
        '26/03/2019' => 122,
        '27/03/2019' => 135,
    ],
]