PHP - encoding nested JSON array

Question

I'm trying to create a JSON response with nested nodes. The first node's data comes from a table called "category" and the second node's data comes from another table called "tracks". each "track" inside the "tracks" table is connected by "category_id" with "category" table. So far I've created this response,

{
    "category": [
        {
            "category_id": "1",
            "category_name": "Editor's Choice ",
            "cover_url": "http://www.example.com"
        },
        {
            "category_id": "2",
            "category_name": "New Releases",
            "cover_url": "http://www.example.com"
        }
    ],
    "success": 1
}

Now I want another child node "track_list" which data will come from the "tracks" table inside the "category" array. The final JSON will probably look like this,

{
    "category": [
        {
            "category_id": "1",
            "category_name": "Editor's Choice ",
            "cover_url": "http://www.example.com",
            "track_list": [
                {
                    "track_id": "",
                    "track_name": ""
                },
                {
                    "track_id": "",
                    "track_name": ""
                }
            ]
        },
        {
            "category_id": "2",
            "category_name": "New Releases",
            "cover_url": "http://www.example.com",
            "track_list": [
                {
                    "track_id": "",
                    "track_name": ""
                },
                {
                    "track_id": "",
                    "track_name": ""
                }
            ]
        }
    ],
    "success": 1
}

Here goes the code I've already tried,

// array for JSON response
$response = array();

// include db connect class
require_once __DIR__ . '/db_connect.php';

// connecting to db
$db = new DB_CONNECT();

// get all category data name from table
$result = mysql_query("SELECT *FROM category") or die(mysql_error());

// check for empty result
if (mysql_num_rows($result) > 0) {

    // looping through all results
    // category node
    $response["category"] = array();

    while ($row = mysql_fetch_array($result)) {

        // temp category array
        $category = array();
        $category["category_id"] = $row["category_id"];
        $category["category_name"] = $row["category_name"];
        $category["cover_url"] = $row["cover_url"];

        // push single category into final response array
        array_push($response["category"], $category);
    }

    // success
    $response["success"] = 1;

    // echoing JSON response
    echo json_encode($response, JSON_PRETTY_PRINT);
} else {

    // no category found
    $response["success"] = 0;
    $response["message"] = "No result found";

    // echo no users JSON
    echo json_encode($response, JSON_PRETTY_PRINT);
}

I have no idea about the nested JSON as I'm completely new in PHP. Any kind of help would be greately appriciated.

Answer 1

Change while loop like this

while ($row = mysql_fetch_array($result)) {

    // temp category array
    $category = array();
    $category["category_id"] = $row["category_id"];
    $category["category_name"] = $row["category_name"];
    $category["cover_url"] = $row["cover_url"];

    $category_id = $row["category_id"]; 

    $track_list = array(); // for track list information

    //Get the tracks associated with category
    $result_tracks = mysql_query("SELECT *FROM tracks WHERE category_id = $category_id") or die(mysql_error());

    while ($row_track = mysql_fetch_array($result_tracks)) {

            $track_list_temp = array();
            $track_list_temp["track_id"] = $row_track["track_id"];
            $track_list_temp["track_name"] = $row_track["track_name"];

            array_push($track_list, $track_list_temp);             
    }

     $category["track_list"] = $track_list; // track list in category array

    // push single category into final response array
    array_push($response["category"], $category);
 }

Answer 2

Use

$tracklist_array["track_id"] = "value";
 $tracklist_array["track_name"] = "value";
 array_push($response["banner"]["tracklist"], $tracklist_array);