current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 0 down vote favorite

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.

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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);
 }
share|improve this answer
    
Very helpful and clearly explained. – Purple Lotus Feb 15 at 5:08
up vote 0 down vote

Use

$tracklist_array["track_id"] = "value";
 $tracklist_array["track_name"] = "value";
 array_push($response["banner"]["tracklist"], $tracklist_array);
share|improve this answer
1  
A little more details of your answer (where it should go, why this way) would be of some help to understand hwo you try to answer OP's problem. – Tensibai Feb 12 at 14:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.