Archives for 

MySQL

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.

Move rows between tables, avoid duplicate and update auto increment value.

 Table “books” and “books_tmp” is identical. “books” table is production state. “books_tmp” is sandbox/testing/pre-production state. Data comes in csv files >> dumped to “books_tmp” >> checked, sorted, etc. >> good data moved to “books”, row by row. Before moved, “books_tmp” data is checked for duplicates in “books”, using ISBN (unique). Upon inserted into “books”, (auto […] Continue reading →