当数据不可用时显示年度报告(CI、PHP、MySQL)
我想显示基于月份的年度报告,假设我在一月、八月和二月有订单。 12 月,但今年剩余时间没有订单。
当我使用 CI 查询时,
select month(order_date) as month_name,
count(order_id) as amount
from order
where year(order_date) = 2011
group by month(order_date)
仅返回 3 行。
假设我然后这样做: use $query->result_array()
Array ([0] => Array ( [month_num] => 1 [amount] => 4 )
[1] => Array ( [month_num] => 8 [amount] => 1 )
[2] => Array ( [month_num] => 12 [amount] => 19 )
)
这很好,但我需要用缺失的月份填写今年剩余的时间。如果数据库中不存在该月份,我需要将金额默认为零。
本质上,我最终会得到一个数组,其中包含一年中的所有月份,其中的值从数据库中获取,如果该月份不存在,则为零。
I want to display a yearly report based on month, let say I have orders in January, August & December but for the rest of the year there are no orders.
When I query using CI
select month(order_date) as month_name,
count(order_id) as amount
from order
where year(order_date) = 2011
group by month(order_date)
There is only 3 rows returned.
Say I then do this: use $query->result_array()
Array ([0] => Array ( [month_num] => 1 [amount] => 4 )
[1] => Array ( [month_num] => 8 [amount] => 1 )
[2] => Array ( [month_num] => 12 [amount] => 19 )
)
This is fine but I need to fill in the rest of the year with the missing months. If the month doesn't exist in the database I need to default the amount to zero.
Essentially, I will end up with an Array with all the months of the year in with the values taken from the database or zero if the month does not exist.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
我不确定这是否可以在 SQL 中完成。可能是。
这是仅使用 PHP 的解决方案。
如果您使用键作为月份数,使用值作为订单数(就像上面的答案一样),您还可以避免使用多维数组:
然后您可以在一行中形成报告:
演示
I'm not sure if this is possible to do in SQL. It could be.
Here is a solution using just PHP.
You can also avoid a multidimensional array if you use the keys as the month number and the value as the number of orders (like the above answer does):
Then you can form your report in one line:
Demo