php, json_encode, nested arrays w/ one "left join" query

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?

Niels · Accepted Answer · 2011-12-15 15:06:38Z

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);

}

answered Dec 15, 2011 at 15:06

Niels

50.1k4 gold badges68 silver badges86 bronze badges

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

Comments

user2626210 · Accepted Answer · 2017-05-27 13:57:50Z

0

This is the job an ORM can do. I give for example a bunch of class doing the job with no limit of depth. Provided an array parameter mapping the fields and the desired nested array:

For instance :

SELECT article.name, article.id as id, comment.id as comment_id , comment.value FROM article JOIN comment ON comment.article_id = article.id

And the corresponding mapping array :

$mapping = [ 'id', 'name', 'comments' => ['id','value']];

answered May 27, 2017 at 13:57

user2626210

1252 gold badges6 silver badges16 bronze badges

Collectives™ on Stack Overflow

php, json_encode, nested arrays w/ one "left join" query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related