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 <a href="/go/56765">user contributed notes</a>.
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.
If you enjoyed this post, make sure you subscribe to our
Comments
Post new comment