Nested JSON From PHP Array

Question

I've loaded my iTunes song data into a MySQL database and I'm trying to use PHP to get a JSON feed of album information. This code..

        <?php

    /* Connect  to database */
    require ('include/connect.php');

    /* Build the query */
            $query = "SELECT a.album,a.name,a.artist,a.year AS track_year, b.year AS album_year FROM staging a, (SELECT album, MAX(year) AS year FROM staging GROUP BY album) b WHERE a.album = b.album";

    /* Loop through the results and build a JSON array for the data table */
    $result = $mysqli->query($query);
    $info = array();
    while ($row = $result->fetch_array(MYSQLI_ASSOC)) {

                if (!isset($info[$row['album']])) {
                    $info[$row['album']] = array(
                        'record' => $row['album']
                        , 'artist' => $row['artist']
                        , 'year' => $row['album_year']
                        , 'tracks' => array()
              );
           }

            $info[$row['album']]['tracks'][] = $row['name'];
    }       

    $data = json_encode($info);
            ?>

produces this result (truncated to two albums)...

{
"Abbey Road": {
    "album": "Abbey Road",
    "artist": "The Beatles",
    "year": "1969",
    "tracks": [
        "Come Together",
        "Something",
        "Maxwell's Silver Hammer",
        "Oh! Darling",
        "Octopus's Garden",
        "I Want You (She's So Heavy)",
        "Here Comes The Sun",
        "Because",
        "You Never Give Me Your Money",
        "Sun King",
        "Mean Mr. Mustard",
        "Polythene Pam",
        "She Came In Through The Bathroom Window",
        "Golden Slumbers",
        "Carry That Weight",
        "The End",
        "Her Majesty"
    ]
},
"Accelerate": {
    "album": "Accelerate",
    "artist": "R.E.M.",
    "year": "2008",
    "tracks": [
        "Living Well Is the Best Revenge",
        "Man-Sized Wreath",
        "Supernatural Superserious",
        "Hollow Man",
        "Houston",
        "Accelerate",
        "Until the Day Is Done",
        "Mr. Richards",
        "Sing for the Submarine",
        "Horse to Water",
        "I'm Gonna DJ",
        "Supernatural Superserious (Live)"
    ]
}
}

I'd like for my results to look like this...

[
      {
        "album": "Abbey Road",
        "artist": "The Beatles",
        "year": "1969",
        "tracks": [
            "Come Together",
            "Something",
            "Maxwell's Silver Hammer",
            "Oh! Darling",
            "Octopus's Garden",
            "I Want You (She's So Heavy)",
            "Here Comes The Sun",
            "Because",
            "You Never Give Me Your Money",
            "Sun King",
            "Mean Mr. Mustard",
            "Polythene Pam",
            "She Came In Through The Bathroom Window",
            "Golden Slumbers",
            "Carry That Weight",
            "The End",
            "Her Majesty"
        ]
    },
     {
        "album": "Accelerate",
        "artist": "R.E.M.",
        "year": "2008",
        "tracks": [
            "Living Well Is the Best Revenge",
            "Man-Sized Wreath",
            "Supernatural Superserious",
            "Hollow Man",
            "Houston",
            "Accelerate",
            "Until the Day Is Done",
            "Mr. Richards",
            "Sing for the Submarine",
            "Horse to Water",
            "I'm Gonna DJ",
            "Supernatural Superserious (Live)"
        ]
    }
    ]

Obviously, I'm new to encoding JSON with PHP. What am I doing wrong? Thanks!

Answer 1

Call array_values on your array:

$data = json_encode(array_values($info));

This removes your named indexes and converts them to numerical ones instead, so json_encode should treat your array as an indexed (and not an associative) array.