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

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.

As a general rule, if you can access the data and see in your mind how you want that data to look, then it's pretty much possible to get it to do that. It's just a matter of working out the process.

In your case, I would do the following steps:

• Order the data by users, then by date so everything is nicely together
• Loop through the data and each time, check that the current user is the same as the last one. if it's not, create a new array key
• split the date into the parts you want
• check the user array for the key relating to year for that user. If the year exists, search for the month. If the month exists, add 1 to the total for that month. If the year and/or month don't exist, create the keys and set the total to be 1 for that month

Once the records have been processed, you should have the data in the format you need.