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.