Obtaining mysql to get hierarchical data structure

Question

I have this database structure:

USERS
-----------------------------
| id | parent | name | points
-----------------------------

I need to get from this structure the multiple nesting (hierarchical) array.

For example, from this data:

USERS
------------------------------
| id | parent | name | points
------------------------------
| 1  | null   | A    | 20
| 2  | 1      | B    | 10
| 3  | 1      | C    | 30
| 4  | 3      | D    | 40
| 5  | 2      | E    | 50
------------------------------

how to get following php array:

[
  "1" => [
    "points" => 20,
    "childs" => [
      "2" => [
        "points" => 10,
        "childs" => [
          "5" => [
            "points" => 50,
            "childs" => null
          ]
        ]
      ],
      "3" => [
        "points" => 30,
        "childs" => [
          "4" => [
            "points" => 40,
            "childs" => null
          ]
        ]
      ]
    ]
  ]
]

Thanks!

Answer 1

Here is a working example with infinite depth:

 //Obtain this from database
 $datas = [
    [
        "id" => 1,
        "parent" => null,
        "name"  => "A",
        "points" => 20
    ],
    [
        "id" => 2,
        "parent" => 1,
        "name"  => "B",
        "points" => 10
    ],
    [
        "id" => 3,
        "parent" => 1,
        "name"  => "C",
        "points" => 30
    ],
    [
        "id" => 4,
        "parent" => 3,
        "name"  => "D",
        "points" => 40
    ],
    [
        "id" => 5,
        "parent" => 2,
        "name"  => "E",
        "points" => 50
    ]   
 ];

 $ordered = [];

 for($i=0; $i<count($datas); $i++)
 {
    $data = &$datas[$i];
    $id = $data["id"];
    $data["childs"] = [];
    $ordered[$id] = &$data; 
 }

 $result = [];
 for($i=0; $i<count($datas); $i++)
 {
    $data = &$datas[$i];
    $id = $data["id"];
    $parent = $data["parent"];

    //unset not needed properties
    unset($data["id"]);
    unset($data["parent"]);                 

    if($parent){
        $ordered[$parent]["childs"][$id] = &$data;
    } else {
        $result[$id] = &$data;
    }
 }

---

The result is:

print_r($result);

Array
(
    [1] => Array
        (
            [name] => A
            [points] => 20
            [childs] => Array
                (
                    [2] => Array
                        (
                            [name] => B
                            [points] => 10
                            [childs] => Array
                                (
                                    [5] => Array
                                        (
                                            [name] => E
                                            [points] => 50
                                            [childs] => Array
                                                (
                                                )    
                                        )    
                                )    
                        )    

                    [3] => Array
                        (
                            [name] => C
                            [points] => 30
                            [childs] => Array
                                (
                                    [4] => Array
                                        (
                                            [name] => D
                                            [points] => 40
                                            [childs] => Array
                                                (
                                                )    
                                        )    
                                )    
                        )    
                )    
        )    
)

ordered contains an array of data "ordered" by id (so you can search for a item easily) And result contains data hierarchy.

Hope it helps