我显示从今天起三个月的订单.如果今天是2015年7月17日,则显示从2015年4月17日到2015年7月17日的数据.我的问题是我必须按星期显示此数据.在3个月内,共有12周.我必须显示所有12周的平均订单价格.

现在,在查询中,我将显示此期间的所有订单.但是我不必显示所有订单的average_prices,也不必显示12周的12个平均价格.怎么做？

<?php
public function average_price_by_week() {
$date = new DateTime("now");
$date->modify('-3 month');
$current =$date->format('Y-m-d');

$this->db->select('DATE_FORMAT(DATE_SUB(ordersheader.createdDate, INTERVAL DAYOFWEEK(ordersheader.createdDate)-2 DAY), "%Y-%m-%d") AS "interval"',FALSE);
	    	
$this->db->select_avg('unitPrice');
$this->db->from('orderitems');
$this->db->join('ordersheader', 'ordersheader.idOrder=
orderitems.idOrder');
			
$this->db->where('ordersheader.createdDate > ',$current);
			
			
$this->db->order_by('interval');
$this->db->group_by('interval');
$query=$this->db->get();
return $query->result_array();

我的表ordersheader具有以下结构：

idOrder(主键),idCustomer,createdDate,orderDueDate

我的表订单项具有以下结构：

id(主键),idOrder,商品编号,包装类型,尺寸,数量,单价,收入

一个idOrder可以有许多订单项.

编辑：我必须在图表中显示此数据.在我的控制器中,我有：
如何显示按周和查询间隔的平均价格而不是数组$firm来获取上述数组中的值？

<?php
 public function column_chart() {   
        
      $size = $this->uri->segment(3);
      $interval = $this->uri->segment(4);
      $firms = $this->Receivedorders_model->average_price_by_week($size,$interval);

      $new_result_array=[];
      $average_prices=[];

      foreach($firms as $row){
          
         if(!isset($new_result_array[date('W',strtotime($row['interval']))])){
           $new_result_array[date('W',strtotime($row['interval']))]=[];
           $new_result_array[date('W',strtotime($row['interval']))]['weekly_order_total']=0;
           $new_result_array[date('W',strtotime($row['interval']))]['orders']=[];
         }
        
         $new_result_array[date('W',strtotime($row['interval']))]['weekly_order_total']+=$row['unitPrice'];
         $new_result_array[date('W',strtotime($row['interval']))]['orders'][]=$row;
      }

      print_r($new_result_array);

      foreach($new_result_array as $week=>$orders){
         $average_prices[$week]=$orders['weekly_order_total']/count($orders['orders']);
      }

      print_r($average_prices);



/* How to show average price by week and interval from query in the following array -instead of $firms to take values from the above array  */
     
      $p = array();
      foreach ($firms as $key => $firm) {
        $p[$key] = array('c' => array(array(
          'v' =>  $firm['interval'],  
        ),
        
     
        array(
          'v' =>  round($firm['unitPrice'],2), 
        )));
      }
      echo json_encode(array(
      'cols' => array(
          array('id' => 'name',  'label' => lang("customer"),  'type' => 'string'),
          array('id' => 'incomes', 'label' => lang("chart_average_price"),  'type' => 'number'),

        ),
        'rows' => $p

      ));
      
    }

解决方法:

要在PHP中完成整个过程,请更改您的选择并删除select_avg,然后对unitPrice进行常规选择.

$new_result_array=[];
$average_prices=[];

foreach($result_array as $row){
     //Initialize the index's if they dont exist
   if(!isset($new_result_array[date('W',strtotime($row['createdDate']))]){
     $new_result_array[date('W',strtotime($row['createdDate'])]=[];
     $new_result_array[date('W',strtotime($row['createdDate'])]['weekly_order_total']=0;
     $new_result_array[date('W',strtotime($row['createdDate'])]['orders']=[];
   }
   //Edited to wrap $row['createdDate'] in strtotime() as all dates are returning week 1 
   $new_result_array[date('W',strtotime($row['createdDate']))]['weekly_order_total']+=$row['unitPrice'];
   $new_result_array[date('W',strtotime($row['createdDate']))]['orders'][]=$row;
}

print_r($new_result_array);

foreach($new_result_array as $week=>$orders){
   $average_prices[$week]=$orders['weekly_order_total']/count($orders['orders']);
}

print_r($average_prices);

W作为日期格式将为您提供周的编号(即1-52).如果需要显示每个每周周期的开始和结束日期,则需要解析该数字.有关更多信息,请参见此问题(可能还有其他问题)

PHP get start and end date of a week by weeknumber