I am using MySQL 5.7 so understand Common Table Expressions are unavailable, but am trying to find a way to build a hierarchy based off a column based input.
For example, my table consists of the following...
| Region | Office | Person |
|---|---|---|
| Region 1 | Office 1 | Employee 1 |
| Region 1 | Office 1 | Employee 2 |
| Region 1 | Office 2 | Employee 1 |
| Region 2 | Office 1 | Employee 1 |
| Region 2 | Office 2 | Employee 1 |
| Region 2 | Office 2 | Employee 2 |
https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=049349ecdbf3369026e009dcb08b3c14
Originally I had asked (in a different question that was closed) for direction around how this could be achieved with dynamically generated IDs and ParentIDs, but it seems this isn't possible without extensive sub queries. Thinking about this a different way, could this be achieved in PHP to build an array instead?
Assuming the result from the DB is as per the table...
<?php
$dbResponse = array(
array(
"Region" => "Region 1",
"Office" => "Office 1",
"Employee" => "Employee 1"
),
array(
"Region" => "Region 1",
"Office" => "Office 1",
"Employee" => "Employee 2"
),
array(
"Region" => "Region 1",
"Office" => "Office 2",
"Employee" => "Employee 1"
),
array(
"Region" => "Region 2",
"Office" => "Office 1",
"Employee" => "Employee 1"
)
); // etc. etc. ...........
// Transformation here
How could it be transformed to produce this desired output?
$newOutput = array(
array(
"Item" => "Region 1",
"Children" => array(
"Item" => "Office 1",
"Children" => array(
"Item" => array("Employee 1", "Employee 2")
)
),
array(
"Item" => "Office 2",
"Children" => array(
"Item" => array("Employee 1")
)
),
),
array(
"Item" => "Region 2",
"Children" => array(
"Item" => "Office 1",
"Children" => array(
"Item" => array("Employee 1")
)
)
)
);
The simplest way to translate the nesting into an array would be -
$newOutput = [];
foreach ($dbResponse as $row) {
$newOutput[$row['Region']][$row['Office']][] = $row['Employee'];
}
which would look like -
Array
(
[Region 1] => Array
(
[Office 1] => Array
(
[0] => Employee 1
[1] => Employee 2
)
[Office 2] => Array
(
[0] => Employee 1
)
)
[Region 2] => Array
(
[Office 1] => Array
(
[0] => Employee 1
)
[Office 2] => Array
(
[0] => Employee 1
[1] => Employee 2
)
)
)
But if you really need to achieve the structure suggested in your question, then you could do something like -
$newOutput2 = [];
// initialise 2 counters
$r = $o = 0;
$previousRow = null;
foreach($dbResponse as $row) {
if (is_null($previousRow)) {
// no counter manipulation required
} elseif ($previousRow['Region'] == $row['Region'] && $previousRow['Office'] != $row['Office']) {
$o++;
} elseif ($previousRow['Region'] != $row['Region']) {
$r++;
$o = 0;
}
$newOutput2[$r]['Item'] = $row['Region'];
$newOutput2[$r]['Children'][$o]['Item'] = $row['Office'];
$newOutput2[$r]['Children'][$o]['Children']['Item'][] = $row['Employee'];
$previousRow = $row;
}
which would look like -
Array
(
[0] => Array
(
[Item] => Region 1
[Children] => Array
(
[0] => Array
(
[Item] => Office 1
[Children] => Array
(
[Item] => Array
(
[0] => Employee 1
[1] => Employee 2
)
)
)
[1] => Array
(
[Item] => Office 2
[Children] => Array
(
[Item] => Array
(
[0] => Employee 1
)
)
)
)
)
[1] => Array
(
[Item] => Region 2
[Children] => Array
(
[0] => Array
(
[Item] => Office 1
[Children] => Array
(
[Item] => Array
(
[0] => Employee 1
)
)
)
[1] => Array
(
[Item] => Office 2
[Children] => Array
(
[Item] => Array
(
[0] => Employee 1
[1] => Employee 2
)
)
)
)
)
)
UPDATE For your example with Teams added to the hierarchy you can add another level of depth to the loop -
$newOutput3 = [];
// initialise 3 counters
$r = $o = $t = 0;
$previousRow = null;
foreach($dbResponse as $row) {
if (is_null($previousRow)) {
// no counter manipulation required
} elseif ($previousRow['Region'] == $row['Region'] && $previousRow['Office'] == $row['Office'] && $previousRow['Team'] != $row['Team']) {
$t++;
} elseif ($previousRow['Region'] == $row['Region'] && $previousRow['Office'] != $row['Office']) {
$o++;
$t = 0;
} elseif ($previousRow['Region'] != $row['Region']) {
$r++;
$o = $t = 0;
}
$newOutput3[$r]['Item'] = $row['Region'];
$newOutput3[$r]['Children'][$o]['Item'] = $row['Office'];
$newOutput3[$r]['Children'][$o]['Children'][$t]['Item'] = $row['Team'];
$newOutput3[$r]['Children'][$o]['Children'][$t]['Children']['Item'][] = $row['Employee'];
$previousRow = $row;
}