PHP nested array from flat MySQL query result -

i need create line chart using highcharts.com js. plugin requires json data following structure:

series: [{     {     name: 'book 2',     data: [             1970, 120,             2001,  50,             2005, 180,             2014,  50           ]     },      {     name: 'another book',     data: [             1970, 120,             2001,  50,             2005, 180,             2014,  50           ]     }             }] 

(the data example)

i want query needed data mysql database. data extracted meekrodb.com library in php.

$results = db::query("select booktitle, editionnr, year editions"); 

the query far outputs flat array:

    (         [0] => array             (                 [booktitle] => booktitle_a                 [editionnr] => 11                 [year] => 2012             )         [1] => array             (                 [booktitle] => booktitle_a (the same)                 [editionnr] => 12                 [year] => 2013             )          [2] => array             (                 [booktitle] => another_booktitle                 [editionnr] => 1                 [year] => 2000             ) ... 

the top level indexes correspond rows of result of query. data output must hierarchial. how can convert nested array looks this?

array (     [name] => book_title_a     [data] => array         (             [0] => 2012, 11  // these rows year (=2012) , editionnr (=11th edition)             [1] => 2013, 12         )      [name] => another_book_title     [data] => array         (             [0] => 2000, 1             [1] => 2011, 2             [2] => 2012, 3         ) ) 

i appreciate help.

-andi

try this:

$data = array();  foreach ( $rows $row ) {   $booktitle = $row['booktitle'];   if ( !isset( $data[$booktitle] ) ) {     $data[$booktitle] = array( "name" => $booktitle, "data" => array() );   }   $data[$booktitle]['data'][] = array( $row['year'], $row['editionnr'] ); }  echo json_encode( array_values( $data ) );