convert flat json to tree view like json

Question

            while($row = mysqli_fetch_assoc($result)) { 
              echo (json_encode($row));  
            }   

The echo produces :

            {"name":"REPORTING","parent":"null","children":"BO"}{"name":"IHS","parent":"BO","children":"1"}{"name":"TOMCAT","parent":"BO","children":"1"}{"name":"WAS","parent":"BO","children":"1"}{"name":"BO","parent":"BO","children":"1"}{"name":"1","parent":"IHS","children":"APP NAME"}{"name":"1","parent":"TOMCAT","children":"APP NAME"}{"name":"1","parent":"WAS","children":"APP NAME"}{"name":"1","parent":"BO","children":"APP NAME"}       

What I'm looking for is:

[
  {
    "name": "REPORTING",
    "parent": "null",
    "children": [
      {
        "name": "BO",
        "parent": "REPORTING",
        "children": [
          {
            "name": "I H S",
            "parent": "BO",
            "children": [
          {
            "name": "34534",
            "parent": "BO",
            "children": [

          {
            "name": "Application Name",
            "parent": "34534",

          }
        ]
          },
          {
            "name": "34535",
            "parent": "BO",
            "children": [

          {
            "name": "Application Name",
            "parent": "34535",

          }
        ]

          },
          {
            "name": "34536",
            "parent": "BO",
            "children": [

          {
            "name": "Application Name",
            "parent": "34536",

          }
        ]

Answer 1

Previously I have also had to do this with a flat data-structure. I solved it with the somewhat processor-intensive recursive function in PHP.

So for example (note code not tested);

PHP:

// MySQL connection defined elsewhere as $con and assuming your table is called Categories
function getCategories($name = null) {
    if(is_null($name)){
        //get all top-level parents
        $sqlString = "SELECT * FROM Categories WHERE parent IS NULL";
    } else {
        $sqlString = "SELECT * FROM Categories WHERE parent = '$name'";
    }

    // Initialise empty categories array;
    $allCats = array();

    $result = mysqli_query($con, $sqlString);

    while ($row = mysqli_fetch_array($result)) {

        $cat['name'] = $row['name'];
        $cat['parent'] = $row['parent'];

        // Fetch any children it may have
        $childCats = getCategories($row['name']);
        if (count($childCats) > 0) {
            $childCat['children'] = $childCats;
        }

        $cat['children'][] = $childCat;
        $allCats['children'][] = $cat;
    }

    return $allCats;
}

$allCats['name'] = "All Categories";
$allCats['parent'] = null;
$allCats['children'] = getCategories();

echo json_encode($allCats);

Answer 2

You can't just dump your rows into a json_encode echo since your data does not have the same structure as the JSON you have in your desired result. You'll have to write code which constructs the tree based on your parent/children values. I'd suggest you create a class with the desired fields, and populate the data in the loop.

A quick example (not finished code, meant to just give some insight to the method):

require_once ('MyClass.php')
$data = new MyClass();
while($row = mysqli_fetch_assoc($result)) {
    if($row['parent'] == 'null') {
        $data->parse($row);
    } else {
        $child = $data->findChild($row['parent']);
        if($child !== false) {
            $child->parseChild($row);
        }
    }
}
echo json_encode($data);

MyClass.php:

class MyClass {
    public $name;
    public $parent;
    public $children;

    public function __construct($name = "") {
        $this->name = $name;
        $this->parent = "null";
        $this->children = array();
    }

    public function parse($rowdata) {
        $this->name = $rowdata['name'];
        $this->parent = $rowdata['parent'];
        echo "Created object " . $this->name . "\n";
    }

    public function parseChild($rowdata) {
        $child = new MyClass();
        $child->parse($rowdata);
        $this->children[] = $child;
    }

    public function findChild($name) {
        if($this->name == $name) {
            return $this;
        } else {
            foreach($this->children as $child) {
                if($child->name == $name) {
                    return $child;
                } else {
                    $ch = $child->findChild($name);
                    if($ch !== false) {
                        return $ch;
                    }
                }
            }
        }
        return false;
    }
}

This code has flaws and bugs, such as the parents need to be before the children. In your example echo IHS is presented before BO, so there is no such parent and the child can't be created. You'll need to order your data properly for this code to work.