dpjuppr1361 2012-04-09 15:35
浏览 75
已采纳

如何从数据库查询中创建多维数组

table name  =  call

Table structure:

user_id | call_time | call_type| 

Let's populate it for understanding

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 

What I want to do

I need Mysql or equivalent Zend Framework query

  1. sum of of call_type.that is I need to know how many there are Out or Missed or Income in each day.

  2. how many call_time exists in each day.that is lets say I have 2012-04-05 so how many time its in database.

I need multi-dimensional array like

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

i(2012-04-01) for understanding

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

lets say i get total record mean dates that is(2012-04-13 , 2012-04-17 , 2012-04-23 ,2012-04-27 , 2012-04-29) so my array will start from 2012-04-01 as this is not found in my result so for that my array will be

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

and for (2012-04-13) array will be

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

what i have tried =

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

    }
  • 写回答

1条回答 默认 最新

  • dougua3706 2012-04-09 15:53
    关注
    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.
    

    so you have the sum for each call_type per day (means max 3 entries per day).

    Then it shouldn't be too hard for you to make an array per day, total sum being the sum of the sums of the three types (num_by_type).

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 C#调用python代码(python带有库)
  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面