PHP MySQL : print total post monthly archive

Question

我需要以 json 格式按一年中的月份(12 个月)打印总计(计数)新闻文件，如下所示:

输出:

["January:31","February:28","March:0","April:130","May:450","June:0","July:0","August:0","September:0","October:520","November:20","December:31"]

PHP 代码:

SELECT COUNT(*) AS id,
YEAR(date) as `year`,
MONTH(date) as `month`,
MONTHNAME(date) as `month_name`,
FROM `aticle`
GROUP BY `year`, `month`;

注意: 如果我们在任何月份都没有新闻打印:0 即:“三月:0”，“七月:0".....

我怎样才能打印这个？!

Answer 1

您可以使用IFNULL来检查是否找不到数据，它应该是0..

请尝试下面给出的查询。

SELECT IFNULL(COUNT(*),0) AS id, YEAR(date) as `year`, MONTH(date) as `month`, MONTHNAME(date) as `month_name`,FROM `aticle`GROUP BY `year`, `month`;

谢谢