How to form JSON from this multidimensional array

Question

My query returns the following:

post_id    post_type    post_text
1          text         bla1
2          pic          bla2
3          text         bla3

I loop through this result as follows:

$posts = array();
foreach ($query->result() as $row) {
    $post = array();
    $post['post_id']         = $row->post_id;
    $post['post_type']       = $row->post_type;
    $post['post_text']       = $row->post_text;
    $posts[] = $post;
}
$data['posts'] = $posts;

return $data;

If I json_encode and output from my controller I get this:

{
    "stream": {
        "posts": [{
            "post_id": "1",
            "post_type": "text",
            "post_text": "bla1",
        },
        {
            "post_id": "2",
            "post_type": "pic",
            "post_text": "bla2",
        },
        {
            "post_id": "3",
            "post_type": "text",
            "post_text": "bla3",            
        }]
    }
}

However if a post has comments, my query returns

post_id    post_type    post_text    comment_id    comment_text
1          text         bla1         7             asd
1          text         bla1         8             sdf
2          pic          bla2
3          text         bla3         10            rty
3          text         bla3         11            yuo

How should I set up the foreach loop to build an array that will output JSON like this?

{
    "stream": {
        "posts": [{
            "post_id": "1",
            "post_type": "text",
            "post_text": "bla1",
            "comment": [{
                "comment_id": "7",
                "comment_text": "asd",
            },
            {
                "comment_id": "8",
                "comment_text": "sdf",
            }],
        },
        {
            "post_id": "2",
            "post_type": "pic",
            "post_text": "bla2",
        },
        {
            "post_id": "3",
            "post_type": "text",
            "post_text": "bla3",            
            "comment": [{
                "comment_id": "10",
                "comment_text": "rty",
            },
            {
                "comment_id": "11",
                "comment_text": "yuo",
            }],
        }]
    }
}

This JSON is consumed on Secha framework as follows:

                    proxy: {
                        type: 'jsonp',
                        url: 'http://myurl',
                        reader: {
                            type: 'json',
                            rootProperty: 'stream.posts'
                        }
                    }

This can be done with a little modification to your result set loop, but I think you should be concerned with how your managing your data. Are you holding entire post information for each comment on the post? — anson
– anson, Commented Sep 17, 2012 at 1:27
yes but open to sugestions on changing my query - since it uses a left join, duplicate rows are returned if there's >1 comment — pepe
– pepe, Commented Sep 17, 2012 at 1:31
Ahh ok, personally I prefer to query each post individually for the existence of comments inside the result set loop. I realize there may be people pushing for querying as little as possible, but this solution is more straightforward and "normalized". Plus, if you are limiting your post query then its not that bad. Also, doing it this way will make your construction of the array a little easier. — anson
– anson, Commented Sep 17, 2012 at 1:38
To construct the array in the method your using now, a tip I could give would be to make the key for each post array the id of the post. That way you can easily refer to it (if it exists) and add a comment. — anson
– anson, Commented Sep 17, 2012 at 1:40
yeah I use that construct for sending the array to a view, but now I need to build the array as above for JSON to send it to mobile framework (Sencha) - — pepe
– pepe, Commented Sep 17, 2012 at 1:41

anson · Accepted Answer · 2012-09-17 01:58:46Z

2

Although I prefer to query each post individually for the existence of comments, here would be one attempt to construct the array using one query.

$posts=array();
$data = array();
foreach($query->result() as $row){
  // if the post hasn't been created in the array
  // create it
  if(!isset($posts[$row['post_id']])){
    $posts[$row['post_id']] = array(
      "post_type" => $row['post_type'],
      "post_text" => $row['post_text']
    );
  }
  // if a comment is found
  if(isset($row['comment_id'])){
    $posts[$row['post_id']]['comments'][] = array(
      "comment_id" => $row['comment_id'];
      "comment_text" => $row['comment_text'];
    );
  }

}
$data['posts'] = $posts;

JSON encoding this array would create something like...

{ "posts" :
   {
     1 : {
       "post_type" : "something",
       "post_text" : "something else"
     },
     2 : {
       "post_type" : "something",
       "post_text" : "something else",
       "comments" : [
         {"comment_id":1,"comment_text":"something"},
         {"comment_id":2,"comment_text":"something"}
       ]
     },
    ....
   }
 }

I cant vouch for this code completely working as I haven't tested it, but it should be a start. Again, if I were you I'd just query each post individually.

answered Sep 17, 2012 at 1:58

anson

4,1742 gold badges25 silver badges30 bronze badges

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

7 Comments

pepe Over a year ago

thx andbeyond - the issue I may have with this solution is that the key for each post changes - and it should be the same (as my example in OP) due to the way the JSON is consumed in the app

anson Over a year ago

have you tried it out? the key for each post will be the ID of the post and it should not change, it only creates the post data once, and then on subsequent loops it will only add comment data (if it exists)

pepe Over a year ago

I meant the key will change from post to post (1, 2...) which prevents me from accessing all posts as an array with the same key

anson Over a year ago

I still don't understand the issue. This should actually be more dynamic for you, because you can loop through the post set using foreach() OR grab a specific post if you have the ID using $posts[$post_id].

pepe Over a year ago

I added the code in OP used on my JS framework to read the JSON - there you see that the rootProperty will target the JSON array posts allowing to pick fields within posts such as post_text etc. If there is a variable key instead of posts then I cannot target post_text anymore, since the JSON reader would have to change for each iteration eg: stream.posts.1, stream.posts.2, etc -- in essence all posts need to be in the same array

|

Samuel Rossille · Accepted Answer · 2012-09-17 01:32:05Z

I don't know PHP syntax so I wont write the code. However this is not a PHP problem, but an algorithm problem.

What you could do to handle this data is doing it in three steps, with a intermediate result that would be a dictionary of posts. In this dictionary, keys will be post ids, and values will be posts.

1/ Iterate over the lines returned by your query. For each of this lines, check if the post involved in this line exists in the dictionary. No -> Add a new post object. Yes -> Just add the comment to the existing post object.

2/ Iterate through your dictionary to add each post contained in it to an array.

3/ Sort your array by the id of the post (here i'm assuming that the id is in the same order as the posts, but just a little advice: you should probably have a post date and sort by post date).

If you master PHP syntax, your should be able to implement it easily. Don't hesitate to submit an edit if you know the PHP syntax for this.

I hope this helps.

pepe · Accepted Answer · 2012-09-23 23:26:32Z

This code

    foreach($query->result() as $row){
      // if the post hasn't been created in the array
      // create it
        if(!isset($post[$row->post_id])){
            $post[$row->post_id] = array(
              "post_id" => $row->post_id,
              "post_text" => $row->post_text
            );
        }
        // if a comment is found
        if(isset($row->comment_id)){
            $post[$row->post_id]['comments'][] = array(
              "comment_id" => $row->comment_id,
              "comment_text" => $row->comment_text
            );
        }
    }

    return $posts[] = array_values($post);

returns this (what I need):

   [
     {
       "post_id"   : "something",
       "post_text" : "something else"
     },
     {
       "post_id"   : "something",
       "post_text" : "something else",
       "comments" : [
         {"comment_id":1,"comment_text":"something"},
         {"comment_id":2,"comment_text":"something"}
       ]
     },
    ....
   ]

Collectives™ on Stack Overflow

How to form JSON from this multidimensional array

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related