How to: Convert MySQL Hierarchical Data Flat List Result to Multi-Dimensional PHP Array

Recently, for a fun stuff, I came across a need of an infinite level of categories. I stumbled upon
Gijs Van Tulder‘s article of “Storing Hierarchical Data in a Database” at SitePoint.

Based of Van Tulder article I’m experimenting on my localhost.

Below is the contents of my ‘categories’ table:
+-----+-------+--------+----------+------+------+
| id | title | parent | parentid | lft | rgt |
+-----+-------+--------+----------+------+------+
| 1 | Root | | NULL | 1 | 22 |
| 2 | Fruit | Root | 1 | 2 | 15 |
| 3 | Berry | Fruit | 2 | 3 | 12 |
| 4 | Apple | Fruit | 2 | 13 | 14 |
| 5 | Red | Berry | 3 | 4 | 7 |
| 6 | Blue | Berry | 3 | 8 | 9 |
| 7 | Black | Berry | 3 | 10 | 11 |
| 116 | Car | Root | 1 | 16 | 21 |
| 118 | Sedan | Car | 116 | 17 | 18 |
| 119 | Truck | Car | 116 | 19 | 20 |
| 115 | Hot | Red | 5 | 5 | 6 |
+-----+-------+--------+----------+------+------+

// Related functions

// A little bit modified Val Tulder display_tree() function, to use ADODB database connection.
function display_tree($root = 'Root') {
global $conn;
$array = array();

$sql = "SELECT lft, rgt FROM categories WHERE title='".$root."'";
$row = $conn->GetRow($sql);

$right = array();

$sql2 = "SELECT id, title, parent, parentid, lft, rgt FROM categories WHERE lft BETWEEN '".$row['lft']."' AND '".$row['rgt']."' ORDER BY lft ASC";
$res = $conn->Execute($sql2);

while ($row = $res->FetchRow()) {
if (count($right)>0) {
while ($right[count($right)-1]<$row['rgt']) { array_pop($right); } } $array[] = sass($row); $right[] = $row['rgt']; } return $array; } // Helper function to search specific key and value inside an array. By 'ob at babcom dot biz', taken from PHP.net user contributed notes.
function ArraySearchRecursive($Needle, $Haystack, $NeedleKey="", $Strict=false, $Path=array()) {
if(!is_array($Haystack)) return false;
foreach($Haystack as $Key => $Val) {
if(is_array($Val)&&
$SubPath=ArraySearchRecursive($Needle, $Val, $NeedleKey, $Strict, $Path)) {
$Path=array_merge($Path,Array($Key), $SubPath);
return $Path;
} elseif((!$Strict && $Val == $Needle && $Key == (strlen($NeedleKey) > 0 ? $NeedleKey : $Key)) || ($Strict && $Val === $Needle && $Key == (strlen($NeedleKey) > 0 ? $NeedleKey : $Key))) {
$Path[]=$Key;
return $Path;
}
}
return false;
}

Val Tulder display_tree() result against ‘categories’ table;
Array
(
[0] => Array
(
[id] => 1
[title] => Root
[parent] =>
[parentid] =>
)

[1] => Array
(
[id] => 2
[title] => Fruit
[parent] => Root
[parentid] => 1
)

[2] => Array
(
[id] => 3
[title] => Berry
[parent] => Fruit
[parentid] => 2
)

[3] => Array
(
[id] => 5
[title] => Red
[parent] => Berry
[parentid] => 3
)

[4] => Array
(
[id] => 115
[title] => Hot
[parent] => Red
[parentid] => 5
)

[5] => Array
(
[id] => 6
[title] => Blue
[parent] => Berry
[parentid] => 3
)

[6] => Array
(
[id] => 7
[title] => Black
[parent] => Berry
[parentid] => 3
)

[7] => Array
(
[id] => 4
[title] => Apple
[parent] => Fruit
[parentid] => 2
)

[8] => Array
(
[id] => 116
[title] => Car
[parent] => Root
[parentid] => 1
)

[9] => Array
(
[id] => 118
[title] => Sedan
[parent] => Car
[parentid] => 116
)

[10] => Array
(
[id] => 119
[title] => Truck
[parent] => Car
[parentid] => 116
)

)

My quick and dirty way to array-ing the display_tree result.
// Fetch the $categories
$categories = display_tree();

// dummy array
$dummy = array();

// set Root parentid to 0
$categories[0]['parentid'] = 0;

// move Root to $dummy
$dummy = $categories[0];
unset($categories[0]);

// iterating $categories
foreach ($categories as $a => $b) {
$pid = $categories[$a]['parentid'];

// search for specific id in $dummy
$search = ArraySearchRecursive($pid,$dummy,'id', true);
if ($search) {
// remove the last entry
array_pop($search);
foreach ($search as $c => $d) {
// get the id's together
$search[$c] = '['.$d.']';
}
// eval'd the id's
eval("\$dummy".implode("", $search)."[child][]=\$categories[$a];");
}
}
// see $dummy result below
print_r($dummy);

$dummy result:
Array
(
[id] => 1
[title] => Root
[parent] =>
[parentid] => 0
[child] => Array
(
[0] => Array
(
[id] => 2
[title] => Fruit
[parent] => Root
[parentid] => 1
[child] => Array
(
[0] => Array
(
[id] => 3
[title] => Berry
[parent] => Fruit
[parentid] => 2
[child] => Array
(
[0] => Array
(
[id] => 5
[title] => Red
[parent] => Berry
[parentid] => 3
[child] => Array
(
[0] => Array
(
[id] => 115
[title] => Hot
[parent] => Red
[parentid] => 5
)

)

)

[1] => Array
(
[id] => 6
[title] => Blue
[parent] => Berry
[parentid] => 3
)

[2] => Array
(
[id] => 7
[title] => Black
[parent] => Berry
[parentid] => 3
)

)

)

[1] => Array
(
[id] => 4
[title] => Apple
[parent] => Fruit
[parentid] => 2
)

)

)

[1] => Array
(
[id] => 116
[title] => Car
[parent] => Root
[parentid] => 1
[child] => Array
(
[0] => Array
(
[id] => 118
[title] => Sedan
[parent] => Car
[parentid] => 116
)

[1] => Array
(
[id] => 119
[title] => Truck
[parent] => Car
[parentid] => 116
)

)

)

)

)

I’ll appreciate any comment or improvement to this code.

Thank you!

PS:
Thank you to Gijs Van Tulder and ‘ob at babcom dot biz’ for your wonderful codes.

Tagged with 
About sepedatua
I am nothing special, of this I am sure. I am a common man with common thoughts and I’ve led a common life. There are no monuments dedicated to me and my name will soon be forgotten, but I’ve loved another with all my heart and soul, and to me, this has always been enough.

Leave a Reply