Need to Convert MySQL records to JSON tree

I have some datasets which look like

UID | VALUE | PARENTUID in MySQL.

I wou开发者_如何学JAVAld really appreciate if someone could tell me how to convert it into a JSON tree exactly like the one above.

All the values are strings. I tried 3 solutions from StackOverflow but couldn't get them to work.

example solution

Convert PHP array to JSON tree

It turns out that relational databases are terrible at handling trees. You are probably better off trying a different method of storing your data. If you have your heart set on that schema, you basically have three options, none of which are very good.

Option 1:

If you know the root of your tree and know the depth of the tree:

SELECT uid, value, parent_uid
FROM your_table level1
LEFT JOIN your_table level2 ON uid = parent_uid
  ON level1.uid = level2.uid
LEFT JOIN your_table level2 ON uid = parent_uid
  ON level3.uid = level3.uid
/* repeat the join until you have gone enough levels down */
WHERE uid = 7 /* or whatever the uid is */
ORDER BY parent_uid, uid

You can use this to get everything that is supposed to be in the tree for that root and reconstruct it in php. This option sucks because it's slow and not flexible.

Option 2

If you know the root of the tree and not the depth:

<?php
$root_id = 7;
$id_list = array($root_id);
$tree = array();
while (!empty($id_list)) {
    $new_list = array();
    foreach ($id_list as $id) {
       $query = "SELECT * FROM your_table WHERE parent_uid = '$id'";
       $results = mysql_query($query);
       while ($next = mysql_fetch_array($results)) {
          array_push($new_list, $next['uid']);
       }
       // find the item in $tree and add it, also ugly
    }
    $id_list = $new_list;
}
echo json_encode($tree);

In spite of being more flexible this option is slower and uglier.

Option 3:

<?php
$query = "SELECT * FROM your_table ORDER BY parent_uid";
$result = mysql_query($query);
$tree = array();
while ($next = mysql_fetch_array($result)) {
   // attach the item to $tree, this is slow and/or ugly code
}
echo json_encode($tree);
?>

This code requires you to get the entire table from mysql, plus it's kind of slow.

Conclusion:

I didn't test any of these code samples because they all suck. Find another storage method for trees. SQL just sucks for this job. You might consider saving it in xml or json to start with, unless the dataset is too large in which case you will want a storage engine designed for this task.

When I posted my first answer I thought I would have persuaded you to find a different approach. Having failed to do so and being obsessed with cool recursive algorithms, here is a working solution based on option 2 from my previous answer.

It is going to make one call to mysql_query for every node in the tree, which is pretty bad for performance, so don't expect this to scale very well, but it won't be too slow for light weight use.

You will have to work the to_array() function to make the json turn out exactly the way you want it, but this should get you off the ground.

<?php

Class Node {
   public $id;
   public $parent_id;
   public $value;
   public $children;
   public $depth;

   function __construct($id, $parent_id, $value) {
      $this->id = $id;
      $this->parent_id = $parent_id;
      $this->value = $value;
      $this->children = array();
      $this->depth = 0;
   }   

   function get_children_from_mysql() {
      $query = "SELECT * FROM your_table WHERE parent_uid = '$this->id'";
      $results = mysql_query($query);
      while ($next = mysql_fetch_array($results)) {
         $next_node = new Node($next['uid'], $next['parent_uid'], $next['value']);
         $this->children[$next_node->id] = $next_node;
         $next_node->get_children_from_mysql();
      }   
   }   

   function to_array() {
      if (count($this->children) > 0) {
         $arr = array();
         foreach ($this->children as $child) {
            array_push($arr, $child->to_array());
         }   
         return array($this->value => $arr);
      } else {
         return $this->value;
      }   
   }   

   function to_json() {
      return json_encode($this->to_array());
   }

}

// you need to know the root uid/value or get it from mysql
$root_uid = 1;
$root_value = "root node value";
$root = new Node($root_uid, 0, $root_value);
$root->get_children_from_mysql(); // magical recursive call

echo $root->to_json();

?>