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

Question

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

Answer 1

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.