Nested MySQL to JSON

Hello,

i have two table called message and comment. i want to export the data to MySQL to JSON based on their relation.

table:

message
mid message
1 first post
2 second post

comment
cid cmessage mid
1 comment1 1
2 comment2 1
3 comment3 2

i can do with php and display with child, parent relationship…

can someone help me to export it in JSON format. like

[
				{
					"id" 		: "1",
					"message" 	: "first post",
					"comment" 	:
					[
						{
							"cid" 		: "1",
							"cmessage" 	: "comment 1"
						},
						{
							"cid" 		: "2",
							"cmessage" 	: "comment 2"
						},
					]
				},
				{
					"id"		:	"2",
					"message"	:	"second post",
					"comment" 	:
					[
						{
							"cid" 		: "3",
							"cmessage" 	: "comment3"
						},
					]
				}
			]

Use json_encode on the PHP array you have formatted like the above.

problem is that i can’t able to design the array structure. i know json_encode() function.

thanks.

?

Don’t use a JOIN as that will return a table of results, what you need is a tree.

  1. SELECT all from messages
  2. Iterate each message
  3. SELECT all from comments that apply to message_id
  4. Build array

Cheers,
Alex

here is the code…

$query = "SELECT
				id,
				message
			FROM
				message
			ORDER BY id ASC" ;


$result = mysql_query($query) or die(mysql_error());
$parent = array() ;

	while($row = mysql_fetch_array($result))
	{
		$parent[] 		= array("id"=>$row['id'],"message"=>$row['message']);

		 
		$query1 = "SELECT
				cid,
				cmessage,
				parent_id
			FROM
				comment
			WHERE parent_id = '$row[id]'
			ORDER BY cid ASC" ;

		$result1 = mysql_query($query1) or die(mysql_error());
	
		while($row1 = mysql_fetch_array($result1))
		{
			$parent[] = array("comment"=>$row1['cmessage']);
			
		}
	}
	

     echo  json_encode($parent); 

output:

[
{
"id": "1",
"message": "Hello"
},
{
"comment": "comment1"
},
{
"comment": "comment2"
},
{
"id": "2",
"message": "Hello1"
},
{
"id": "3",
"message": "Hello2"
},
{
"comment": "comment5"
}
]

now i want that comment array start right after thier parent message. like in JSON format. like

"data": 
			[
				{
					"id": "1",
					"message": "Hello",
					"comment": 
					{
						"comment": "comment1"
					}
				},

I would do it with one query:


// Psuedo code here
$sql = 'SELECT id,message,comment FROM ...';
$rows = mysql_fetch_rows($sql);

// Real stuff
$items = array();
foreach($rows as $row)
{
  $id = $row['id'];
  if (isset($items[$id])) $item = $items[$id];
  else
  {
    $item = array
    (
      'id' => $id,
      'message' => $row['message'],
      'comments' => array()
    );
  }
  $item['comments'][] = $row['comment'];
  $items[$id] = $item;
}

In a pm kamal20 asked for more info on the query. The query would look something like this:


  $sql = <<<EOT
SELECT mid,message,cmessage
FROM message
LEFT JOIN comment ON comment.mid = message.mid;
EOT;

Use an interactive tool such as mysql to run the query and look at the result. This will provide at least a basic understanding of mysql joins.

Notice that the message data is repeated for each comment associated with a particular message. The php code takes care of converting the flat record back into an array of arrays.

Don’t be shy about asking for help. Everyone started somewhere.