Question

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.

当我问询使用 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 然后做: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.

基本上,如果该月不存在,我将用阿雷拉来最后,用从数据库中得出的数值或零。

Answer 1

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

这里是使用只有购买力平价的解决办法。

$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);

Demo

Answer 2

$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;
}

友情链接