MySQL hierarchy data to JSON string in PHP

Question

I have data in a MYSQL DB that looks like this:

id,parentid,name,count
1,0,top,10
2,1,middle1,5
3,1,middle2,5
4,3,bottom1,3
5,3,bottom2,2

and want to output it via PHP as a heirarchical JSON string where 'top' has a collection of 'middle's etc.

Get my drift? Anyone have a recursive PHP function to help?

Answer 1

if you've got your data in a PHP array/associative array, then you can use PHP 5.2's JSON functions:

http://www.php.net/manual/en/function.json-encode.php

keep reading on that page to the comments area, they practically give away the code without the fun of figuring it out yourself.

Answer 2

You can use the function here http://tagarga.com/blok/on/061029 to convert your adjacency list to a nested array, then json_encode() it.

 function adj_tree(&$tree, $item) {
  $i = $item['id'];
  $p = $item['parentid'];
  $tree[$i] = isset($tree[$i]) ? $item + $tree[$i] : $item;
  $tree[$p]['_children'][] = &$tree[$i];
 }

 $tree = array();
 $rs = my_query("SELECT * FROM categories");
 while($row = my_fetch($rs))
  adj_tree($tree, $row);

 echo json_encode($tree[0]);

Answer 3

Hi, How would we implement this if we will use the Modified PreOrder Tree Traversal instead of Adjacency Model list?

As dicussed in this site: http://articles.sitepoint.com/article/hierarchical-data-database/2

can you help me create the function that will convert the records row (preOrdere Tree) to a multidimensional array? Then I can use json_encode($tree)

Thanks,