I have a database structure as following:
+----+-----------+-----------+
| ID | Name | Parent_ID |
+----+-----------+-----------+
| 1 | 123a | 2 |
| 2 | Microsoft | 3 |
| 3 | Apple | 14 |
| 5 | IBM | 14 |
| 14 | Amazon | NULL |
+----+-----------+-----------+
I want to retrieve an array of all children of a certain ID.
So if I launch my function with ID = 14, i want to get the following result:
array (size=2)
0 =>
array (size=3)
'orgid' => string '3' (length=1)
'name' => string 'Apple' (length=5)
'parent_id' => string '14' (length=1)
1 =>
array (size=3)
'orgid' => string '5' (length=1)
'name' => string 'IBM' (length=3)
'parent_id' => string '14' (length=2)
This is the code I have so far:
function get_all_children_from_tree($orgparentid, $childrenArray = null)
{
$CI = get_instance();
$CI->load->model('organization/organization_model');
$allChildrenOrgs = $CI->organization_model->getAllChildrenForParent($orgparentid);
if (empty($childrenArray)) {
$childrenArray = [];
}
foreach ($allChildrenOrgs as $org)
{
$org_id = $org->org_id;
$org_name = $org->name;
$org_parent_id = $org->parent_id;
$arr = array('orgid' => $org_id, 'name' => $org_name, 'parent_id' => $org_parent_id);
array_push($childrenArray, $arr);
if (!empty($org_id)) {
get_all_children_from_tree($org_id, $childrenArray);
}
}
return $childrenArray;
}
Going depth first on the first child works great and gives me the correct result, but as soon as I enter the 2nd child of the root (IBM in this example), the resulting array is returned to its original value at the point where the recursive function is called. And thus all the children get expelled from the result.
For example, the result with my code and the example data as provided above will be:
array (size=5)
0 =>
array (size=3)
'orgid' => string '14' (length=1)
'name' => string 'Amazon' (length=6)
'parent_id' => NULL
4 =>
array (size=3)
'orgid' => string '5' (length=1)
'name' => string 'IBM' (length=3)
'parent_id' => string '14' (length=2)
If you are using MySQL 8+, you may completely handle this problem on the database using a recursive hierarchical CTE:
WITH RECURSIVE cte (id, name, parent_id) AS (
SELECT ID, Name, Parent_ID
FROM yourTable
WHERE ID = 14
UNION ALL
SELECT t1.ID, t1.Name, t1.Parent_ID
FROM yourTable t1
INNER JOIN cte t2 ON t1.Parent_ID = t2.ID
)
SELECT * FROM cte;
There are also ways to do this on earlier versions of MySQL, though in any case I would recommend that you handle this on the database itself, rather than in PHP.
You're right, i've decided to upgrade mysql in order to allow CTE support. Thanks a lot !! It's probably a lot faster in SQL than in PHP anyways.