php，json_encode，嵌套数组带有一个“左连接”查询

Question

I have a "post" table with corresponding "comments" table (each 'comment' row has a 'post_id' that relates back to the 'post' table).

This is my function that echos my JSON response from my query:

function echo_json_result($result) {

$arr = array();

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

$arr[] = $row;

}

echo json_encode($arr);

}

This currently spits out one big array.

For the interest of readability in the response, I'd prefer the 'comments' associated to these 'posts' be returned as a nested array..

I can think of one way to do this. Create two queries, one for notes and one for all the comments associated with the notes. Then, add the associative array that contains all the comments to notes array and then json_encode that.

Is this the best way?

Answer 1

Try something like this, but the post needs to be in the result before your comments:

function echo_json_result($result) {

    $arr = array();

    while ($row = mysql_fetch_assoc($result)) {
        if( $row['post_id'] != "" )
        {
             if( array_key_exists("comments", $arr['post_id']) )
             {
                 array_push($arr['post_id']['comments'], $row);
             }
            else
            {
                 $arr['post_id']['comments'] = array($row);  
            }
        }
        else
        {
            $arr[$row['id']] = $row;
        }
    }

    echo json_encode($arr);

}