duanchuan6350 2015-07-08 07:32
浏览 43
已采纳

使用SQL和Codeigniter返回一个4维数组

I need to do a query and get certain kind of data. I have 2 tables, users and connections, I need to get per user how many times he/she connected per month and year.

users             connections
...........    ................
john              10/02/2014
john              15/02/2014
john              03/01/2015
john              06/02/2015

Is there a chance to get this info in this format:

 john=>
   [0]=>2014
     [0]=>02
       'total' =>2
   [1]=>2015
     [0]=>01
       'total' => 1
     [1]=>02
       'total' => 2
     [2]=>03
       'total'=> 1 

I'm using Codeigniter and also PHP.

Answering to @CodeGodie what I've done so far is:

public function getPeriodicity(){
    $this->db->select('u.vusr_user, extract (MONTH from (to_timestamp(c.vuc_log_in))) as month, extract (YEAR from (to_timestamp(c.vuc_log_in))) as yearly, COUNT(c.vuc_log_in)');
    $this->db->from('vts_users_conn c');
    $this->db->join('vts_users u', 'c.vuc_vusr_id = u.vusr_id');
    $this->db->group_by('u.vusr_user, month, yearly','asc');
    $query = $this->db->get();
    return $query->result_array();
  }
  • 写回答

2条回答 默认 最新

  • dthswrp84966 2015-07-08 08:28
    关注

    Assuming you are using Codeigniter's $this->db->result_array() to obtain your database results, your initial array will look like this:

    $res = array(
        array(
            "name" => "john",
            "date" => "10/02/2014"
        ),
        array(
            "name" => "john",
            "date" => "15/02/2014"
        ),
        array(
            "name" => "john",
            "date" => "03/01/2015"
        ),
        array(
            "name" => "john",
            "date" => "06/02/2015"
        ),
        array(
            "name" => "john",
            "date" => "06/03/2015"
        )
    );
    

    In order to change this array to your desired output, I would do the following:

    foreach ($res as $row) {
        $date_arr = explode("/", $row['date']);
        $n = $row['name'];
        $y = $date_arr[2];
        $m = $date_arr[1];
    
        if (!isset($final[$n]))
            $final[$n] = array();
    
        if (!isset($final[$n][$y]))
            $final[$n][$y] = array();
    
        if (!isset($final[$n][$y][$m])) {
            $final[$n][$y][$m] = array("total" => 1);
        } else {
            $final[$n][$y][$m]["total"] = $final[$n][$y][$m]["total"] + 1;
        }
    }
    

    If you var_dump your final result (var_dump($final)), you will get the following:

    array (size=1)
      'john' => 
        array (size=2)
          2014 => 
            array (size=1)
              '02' => 
                array (size=1)
                  'total' => int 2
          2015 => 
            array (size=3)
              '01' => 
                array (size=1)
                  'total' => int 1
              '02' => 
                array (size=1)
                  'total' => int 1
              '03' => 
                array (size=1)
                  'total' => int 1
    

    Hope this helps.

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 距离软磁铁一定距离的磁感应强度大小怎么求
  • ¥15 霍尔传感器hmc5883l的xyz轴输出和该点的磁感应强度大小的关系是什么
  • ¥15 vscode开发micropython,import模块出现异常
  • ¥20 Excel数据自动录入表单并提交
  • ¥30 silcavo仿真,30分钟,只需要代码
  • ¥15 FastReport 怎么实现打印后马上关闭打印预览窗口
  • ¥15 利用3支股票数据估计其均值和方差的95%置信区间。
  • ¥15 微信小程序运行一项功能时,弹出未知错误弹框,检查代码没有问题
  • ¥15 ATAC测序生成self-pseudo replicates之前是否要进行去线粒体reads
  • ¥15 python模糊字匹配函数问题
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部