Get nested/hierarchical JSON from MySQL/PHP

Question

I am using spacetree chart and I require JSON in hierarchical format. See sample required JSON format here. I have ID,ParentID,Name,Description fields in Mysql database table. Now How can I convert data in Hierarchical/nested JSON using PHP? I know json_encode($array). But, I require nested/Hierarchical php array for this. Let me know to do this.

Answer 1

If you have PHP json extension on your server installed, just use it on an array. The steps will be:

1. Build PHP array with hierarchy based on mysql resutls
2. call a json_encode($array) http://pl.php.net/manual/en/function.json-encode.php

If you don't have json_encode enabled on your server and cannot install it... You'll just have to write it by hand.

It'll be something like that (untested code warning):

function my_json_encode($array)
{
  $return = '{';
  $count = count($array);
  $i = 0;
  foreach ($array as $key => $val)
  {
    $return .= '"'.$key.'" : ';
    if (!is_array($val))
      $return .= '"'.$val.'"';
    else
      $return .= my_json_encode($val);
    if ($i < $count-1)
      $return .=",";
    $i++;
  }
  $return .= '}';
  return $return;
}

Answer 2

You're basically asking two questions here - 1) how to get an hierarchical php structure from a db table and 2) how to encode this structure in json. For the first question see, for example, my (old but working) code. For the second, I believe simple json_encode will work pretty well.

Answer 3

Hi stereofrog, can you please help me, I have the same question. How to get an hierarchical php structure from a db table, in php array, or JSON, but with the following format:

[{ "attributes" : {"id" : "111"}, "data" : "Some node title", "children" : [ { "attributes" : { "id" : "555"}, "data" : "A sub node title here" } ], "state" : "open" }, { "attributes" : {"id" : "222"}, "data" : "Other main node", "children" : [ { "attributes" : { "id" : "666"}, "data" : "Another sub node" } ], "state" : "open" }]

My SQL table contains the fields: ID PARENT ORDER TITLE

Can you please help me with this? I'm going crazy trying to get this. I tried to modify your adj_tree function but I didn't got the array/json format I need.

Many thanks in advance. Daniel