using two mySQL queries to create nested JSON array

Question

I am trying to create a nested JSON array with the results of the second query being attached to the rows of the first.

My code so far is as follows:-

$output = array();

$sql = "select cp_comments.*,users.user_login from ".$wpdb->prefix."cp_comments cp_comments
                left join ".$wpdb->prefix."users users on users.ID=cp_comments.uid
                where songid='$id'
                order by cp_comments.id asc";               
    $comments = $wpdb->get_results($sql);



    foreach($comments as $c){


        $sql = "select cp_replies.*,users.user_login from ".$wpdb->prefix."cp_replies cp_replies
                    left join ".$wpdb->prefix."users users on users.ID=cp_replies.uid
                    where cp_replies.cid='".$c->id."'
                    order by cp_replies.id asc";
        $replies = $wpdb->get_results($sql);


    $output['comment-'.$c->id] = $c;        


        if($replies){

            foreach($replies as $r){

            // The line below causes the problem
            //$output['comment-'.$c->id][] = $r;


            }       
        }           
    }

    echo json_encode( $output );

As you can see what I am attempting to do is retrieve the results of query-1, lop through them and populate an array. So far so good. Then for each row in the resultset, perform a second query, using $c->id as a variable to dynamically change the second query dependant upon the id, then nest this data within each returned row of the first query.

The line I have commented out causes an error:-

Fatal error: Cannot use object of type stdClass as array in

Although I have a rough understanding of why this error is happening, I do not know how to fix it and when I have tried a standard multidimensional array using while loops etc, I was then not able to access the $c->$id variable making the whole second query worthless.

Essentially I would like the data returned in this format:-

{ "comment-2" : [ { "avatar" : "http://www.songbanc.com/wp-content/uploads/avatars/1/8bb11e958a26913e2c13393014e854d5-bpthumb.jpg",
        "body" : "More tests....",
        "display_name" : "admin",
        "id" : "26",
        "playtime" : 36.206896551699998,
        "posttime" : "2011-10-08 11:11:55",
        "cid" : "26",
        "songid" : "30",
        "uid" : "1",
        "user_login" : "admin",
        "user_url" : "http://www.songbanc.com/members/admin/"
        "cid": "1",
        "replies" : [ { "cid" : "26",
                        "body" : "test reply",
                        "posttime" : "2011-10-08 11:11:55"
                      }]

      },

It is currently 2 dimensional however without the 'replies'.

atxdba · Accepted Answer · 2012-01-10 01:31:21Z

1

$output['comment-'.$c->id] is an object. I think you want something like

$output['comment-'.$c->id]->replies[] = $r;

answered Jan 10, 2012 at 1:31

atxdba

5,2165 gold badges27 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

using two mySQL queries to create nested JSON array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related