如何使从数据库查询多维数组

Question

table name = call 

表结构：

user_id | call_time | call_type| 

让我们来填充它对于理​​解

user_id | call_time     | call_type| 
    0  2012-04-05 07:40:58 GMT+05:00  Mised 
    0  2012-04-06 08:58:45 GMT+05:00  Out 
    0  2012-04-08 09:40:58 GMT+05:00  Incom 

我想做

我需要的Mysql什么或相当于Zend Framework的查询

1. call_type.that的总和，这是我需要知道每天有多少有或没有收入或收入。

2. 每天有多少call_time，也就是说我有2012-04-05那么它在数据库中有多少次。

我需要一个像

i = start from first day of the month and go up to last day of current month, 

我（2012-04-01）的理解多维数组

Array 
(
[ i (2012-04-01)]=>Array 
    (
    [Mised ] = 67(sum of all Missed call in this date) 
    [Out ] = 10(sum of all out call in this date) 
    [Incom ] = 10(sum of all Incom call in this date) 
    [total_call] =87 (Sum Of All) 
    ) 

[ i (2012-04-02) ]=>Array 
    [Mised ] = 17(sum of all Missed call in this date) 
    [Out ] = 2(sum of all out call in this date) 
    [Incom ] = 4(sum of all Incom call in this date) 
    [total_call] =23 (Sum Of All) 
    ) 
. 
. 
. 
. 
. 
. 
. 
. 
. 
[2012-04-30(last day current month)]=>Array 
    (
    [Mised ] = 77(sum of all Missed call in this date) 
    [Out ] = 72(sum of all out call in this date) 
    [Incom ] = 24(sum of all Incom call in this date) 
    [total_call] =173 (Sum Of All) 
    ) 

可以说我得到总战绩意味着日期是（2012 -04-13，2012-04-17，2012-04-23，2012-04-27，2012-04-29） 所以我的数组将从2012-04-01开始，因为这在我的结果中找不到因为我的阵列将是

[2012-04-01]=>Array 
    (
    [Mised ] = 0 
    [Out ] = 0 
    [Incom ] = 0 
    [total_call] = 0 

和（2012-04-13） 阵列将是

[2012-04-01]=>Array 
    (
    [Mised ] = 10 
    [Out ] = 55 
    [Incom ] = 9 
    [total_call] = 74 
    ) 

什么我试图=

public function Get_Calllogs_For_Graph($user_id, $phone_service_id){ 

    $curdate = new DateTime(date('Y-m-d')); 
    $current_month = date("m"); 
    $start_date = date("Y-$current_month-01"); 
    $start_date = $curdate->format("Y-$current_month-d H:i:s"); 
    $curdate = new DateTime(date('Y-m-d')); 
    $curr_date = $curdate->format('Y-m-d H:i:s'); 

    $DB = Zend_Db_Table_Abstract::getDefaultAdapter(); 
    $select = $DB->select() 
          ->from('call', array('*', 'CAST(call_time AS DATE) AS call_time ')) 
            ->where('phone_service_id = ?', $phone_service_id) 
            ->where('user_id = ?', $user_id) 
            ->where(" call_time >= ?", $start_date) 
            ->where(" call_time<= ?", $curr_date); 
             $select = $DB->fetchAssoc($select); 


    $records = array('Outgoing' => array(), 'Incoming' => array(), 'Missed' => array());    
    if(count($select)){ 
     foreach($select as $sel){ 

       if(!array_key_exists($sel['call_name'], $records[$sel['call_type']])){ 
        $records[$sel['call_type']][$sel['call_name']] = $this->Get_Calllogs_By_Callname($user_id, $phone_service_id, $start_date, $curr_date, $sel['call_name'], $sel['call_type']); 
       } 

     } 
    } 
    echo '<pre>'; 
    print_r($records); 
    echo '</pre>'; 

    } 
public function Get_Calllogs_By_Callname($user_id, $phone_service_id, $start_date, $curr_date, $call_name, $call_direction){ 

     $DB = Zend_Db_Table_Abstract::getDefaultAdapter(); 
     $select = $DB->select() 
       ->from('call_log', array('COUNT(*) AS total', 'CAST(call_name AS DATE) AS call_name')) 
         ->where('phone_service_id = ?', $phone_service_id) 
         ->where('user_id = ?', $user_id) 
         ->where('call_type= ?', $call_type) 
         ->having('call_name = ? ', $call_name); 
         //->where("call_type>= ?", $start_date) 
         //->where("call_type<= ?", $curr_date); 

          $select = $select->query()->fetchAll(); 
          if(count($select)){ 

           return $select[0]['total']; 

          }else{ 
           return 0; 
          } 

    } 

Answer 1

select call_time, call_type, count(*) as num_by_type from db 
group by YEAR(call_time), MONTH(call_time), DAY(call_time), call_type. 

所以你必须为每个CALL_TYPE每总和一天（意味着每天最多3项）。

然后，你应该不会很难每天创建一个数组，总和是这三种类型的总和（num_by_type）。