Get all Child, Grandchild, etc. Nodes Under Parent Using PHP with MySQL Query Results

Original Issue:

Retrieving all child, grandchild, and subsequent descendant nodes associated with a parent node is a common task when working with hierarchical data structures. This problem arises in scenarios where database tables employ an adjacency list model for data organization.

Approach Using Recursion:

To address this issue, recursion proves to be an effective approach. Here's a detailed explanation of how recursion can be employed to achieve this goal:

1. Establishing a Base Function:

A recursive function is one that calls upon itself to solve a problem and is often used in scenarios involving hierarchical or nested data structures. In this instance, our base function will be named fetch_recursive.

2. Identifying the Criteria for Recursive Calls:

Within fetch_recursive, two primary conditions determine when recursive calls are made:

• Parent Node Found: When the current node being evaluated is the parent node we are interested in (based on the provided ID).
• Child Node Found: When the current node has a parent ID that matches the parent node ID.

3. Constructing the Result Array:

Each time a recursive call is made, the function will populate a result array with relevant data from the current node. This array will grow iteratively as the recursive calls traverse the tree structure.

4. Recursively Searching for Child Nodes:

If the current node has any child nodes (identified by the existence of a children property), another recursive call will be made to retrieve those child nodes. This process continues until all child nodes of the parent node are captured.

Additional Functionality:

1. Handling Grandchildren and Descendants:

The recursive nature of fetch_recursive ensures that it will automatically traverse the hierarchy and retrieve not only child nodes but also grandchildren and subsequent descendants.

2. Unifying Results:

After all recursive calls are complete, the function returns a single, comprehensive array containing all the descendant nodes under the specified parent node.

Code Implementation:

function fetch_recursive($src_arr, $currentid, $parentfound = false, $cats = array())
{
    foreach($src_arr as $row)
    {
        if((!$parentfound && $row['id'] == $currentid) || $row['parent_id'] == $currentid)
        {
            $rowdata = array();
            foreach($row as $k => $v)
                $rowdata[$k] = $v;
            $cats[] = $rowdata;
            if($row['parent_id'] == $currentid)
                $cats = array_merge($cats, fetch_recursive($src_arr, $row['id'], true));
        }
    }
    return $cats;
}

Usage:

To utilize the fetch_recursive function, simply pass the original data array (in this case, $data) and the ID of the node from which you want to retrieve the descendants. For instance, to retrieve all child, grandchild, and descendant nodes under node 3:

$list = fetch_recursive($data, 3);

This will populate the $list variable with an array containing all relevant nodes.