PHP nested array from flat MySQL query result

Question

I need to create a line chart using highcharts.com js. The plugin requires JSON data with the 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 is just an example)

I want to query the needed data from a MySQL Database. The data is extracted with the meekrodb.com library in PHP.

$results = DB::query("SELECT booktitle, EditionNr, Year FROM editions");

The query so far outputs this 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 to the rows of the result of the query. However the data output must be hierarchial. How can I convert it to a nested array that looks like this?

Array
(
    [name] => book_title_a
    [data] => Array
        (
            [0] => 2012, 11  // these are the rows Year (=2012) and EditionNr (=11th edition)
            [1] => 2013, 12
        )

    [name] => another_book_title
    [data] => Array
        (
            [0] => 2000, 1
            [1] => 2011, 2
            [2] => 2012, 3
        )
)

I appreciate your help.

-Andi

in my earlier example I messed up with the [] brackets an nested the array too deeply. Corrected it now — kabauter
– kabauter, Commented Apr 6, 2014 at 14:28

Phil · Accepted Answer · 2014-04-06 13:01:22Z

1

Try something like this:

$data = array();

foreach ( $rows as $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 ) );

answered Apr 6, 2014 at 13:01

Phil

2722 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kabauter Over a year ago

Thanks. It seems to work now. However I changed one line so that the keys inside the "data" array are not nested too deeply: $data[$bookTitle]['data'][] = $row['Year'].", ".$row['EditionNr'];

kabauter Over a year ago

@vp_arth: you're right, but doesn't $data[$bookTitle]['data'][] = array( $row['Year'], $row['EditionNr'] ); create an array inside an array (because of [] AND array())? thus nesting it one branch too deeply.

vp_arth Over a year ago

no, this exactly what are you ask, data is array, and adds [Year, Nr] pairs to this array here

vp_arth Over a year ago

if you have a number entities with same bookTitle it will be data: [[Year1, Nr1], [Year2, Nr2]...]

Su4p · Accepted Answer · 2014-04-06 13:01:31Z

0

$result = array();
$prevbooktitle = '';
$book_count = 0;
foreach($tmp as $val){

    if($val['booktitle'] != $prevbooktitle){
      $result[$book_count]['name'] = $val['booktitle'];
      $book_count++;
      $prevbooktitle = $val['booktitle'] ;
    }

    $result[$book_count]['data'][] = array($val['Year'],$val['EditionNr']);

}

answered Apr 6, 2014 at 13:01

Su4p

86510 silver badges25 bronze badges

Comments

smistry · Accepted Answer · 2014-04-06 13:33:50Z

0

    $series = new stdClass();
    $JSONBooksArray = array();
    foreach($books AS $book){

        $jsonbook = new stdClass();
        $jsonbook->name = $book['booktitle'];
        $bookdata = array();
        $bookdata[0] = $book['Year'];
        $bookdata[1] = $book['EditionNr'];
        $jsonbook->data = $bookdata;
        $JSONBooksArray[] = $jsonbook;

    }
    $series->series = $JSONBooksArray;
    echo json_encode($series,JSON_PRETTY_PRINT);

answered Apr 6, 2014 at 13:33

smistry

1,1261 gold badge9 silver badges9 bronze badges

1 Comment

kabauter Over a year ago

It's a very readable approach using classes, but it generates duplicates for the booktitle entries instead of grouping the same book.

Collectives™ on Stack Overflow

PHP nested array from flat MySQL query result

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related