PHP add another array

Question

I'm trying to render a Highcharts column drilldown with dynamic values retrieved from database.

And I have a php query like this:

$query = mssql_query("SELECT count(create_at) as create,  month(create_at) as month, year(create_at) as year
                FROM tblCrew 
                WHERE create_at >=  '01 JUL 2011' AND create_at <=  '31 JUL 2013'
                GROUP BY  month(create_at), year(create_at) 
                ORDER BY  year(create_at) ASC, month(create_at) ASC ");

And to get the result I looped it like this:

while ($data = mssql_fetch_array($query))
            {
                if (!isset($case[$data['year']])) {
                                $case[$data['year']] = array(
                                    'name' => $data['year'],
                                    'data' => array()
                                );
                    }

                    $case[$data['year']]['data'][] = array(
                        $data['month'],
                        $data['create']
                    );

            }

And this is the results:

$case = Array([2011] => Array
    (
        [name] => 2011
        [data] => Array
            (
                [0] => Array ( [0] => JUL [1] => 166 )
                [1] => Array ( [0] => AUG [1] => 144 )
                ........
                .......
                [5] => Array ( [0] => DEC [1] => 124 )
            )
    )

[2012] => Array
    (
        [name] => 2012
        [data] => Array
            (
                [0] => Array ( [0] => JAN [1] => 143 )
                [1] => Array ( [0] => FEB [1] => 177 )
                ........
                .......
                [5] => Array ( [0] => DEC [1] => 132 )
            )
    )       

[2013] => Array
    (
        [name] => 2013
        [data] => Array
            (
                [0] => Array ( [0] => JAN [1] => 166 )
                ........
                .......
                [5] => Array ( [0] => JUL [1] => 124 )
            )
    )       

)

And now I am trying to add another array where I will sum up the data per year and put it in another array. Like the example below.

$total[] = Array[{name: '2011', total: '1520'}, {name: '2012', total: '2241'}, {name: '2013', total: '1864'}]   

Answer 1

You can add another array, initialize it the same time you initialize $case[$data['year']] and add values while looping the results :

$total = array();
while ($data = mssql_fetch_array($query))
        {
            if (!isset($case[$data['year']])) {
                            $case[$data['year']] = array(
                                'name' => $data['year'],
                                'data' => array()
                            );
                            $total[$data['year']] = array(
                                'name' => $data['year'],
                                'total' => 0
                            );
                }

                $case[$data['year']]['data'][] = array(
                    $data['month'],
                    $data['create']
                );
                $total[$data['year']]['total'] += $data['create'];

        }
print_r($total);