当数据不可用时显示年度报告(CI、PHP、MySQL)

发布于 2024-12-15 00:10:34 字数 673 浏览 0 评论 0原文

我想显示基于月份的年度报告,假设我在一月、八月和二月有订单。 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

演出会有结束 2024-12-22 00:10:34

我不确定这是否可以在 SQL 中完成。可能是。

这是仅使用 PHP 的解决方案。

$orders = array(
    array(
        'month_num' => 1,
        'amount' => 4
    ),
    array(
        'month_num' => 2,
        'amount' => 0
    ),   
    array(
        'month_num' => 3,
        'amount' => 4
    )
);

$report = array_combine( range( 1, 12), array_fill( 1, 12, 0));
foreach( $orders as $order)
{
    $report[ $order['month_num'] ] = $order['amount'];
}

var_dump( $report);

如果您使用键作为月份数,使用值作为订单数(就像上面的答案一样),您还可以避免使用多维数组:

$orders = array( 1 => 4, 2 => 0, 3 => 0, 4 => 0);

然后您可以在一行中形成报告:

$report = array_replace( array_combine( range( 1, 12), array_fill( 1, 12, 0)), $orders);

演示

I'm not sure if this is possible to do in SQL. It could be.

Here is a solution using just PHP.

$orders = array(
    array(
        'month_num' => 1,
        'amount' => 4
    ),
    array(
        'month_num' => 2,
        'amount' => 0
    ),   
    array(
        'month_num' => 3,
        'amount' => 4
    )
);

$report = array_combine( range( 1, 12), array_fill( 1, 12, 0));
foreach( $orders as $order)
{
    $report[ $order['month_num'] ] = $order['amount'];
}

var_dump( $report);

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):

$orders = array( 1 => 4, 2 => 0, 3 => 0, 4 => 0);

Then you can form your report in one line:

$report = array_replace( array_combine( range( 1, 12), array_fill( 1, 12, 0)), $orders);

Demo

冰之心 2024-12-22 00:10:34
$month_array = Array ( [0] => Array ( [month_num] => 1 [amount] => 4 ) [1] => Array ( [month_num] => 8 [amount] => 1 ) [2] => Array ( [month_num] => 12 [amount] => 19 ) );

$new_month_array = array();
foreach( $month_array as $month ){
    $new_month_array[] = $month;
}
$month_array = Array ( [0] => Array ( [month_num] => 1 [amount] => 4 ) [1] => Array ( [month_num] => 8 [amount] => 1 ) [2] => Array ( [month_num] => 12 [amount] => 19 ) );

$new_month_array = array();
foreach( $month_array as $month ){
    $new_month_array[] = $month;
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文