PHP Recursive Function - How To Avoid Using A Global Variable

Question

I have created a function that returns an array of data for the descendants of a chosen Parent.

First it checks for Children of the Parent whose UUID we have fed to it, it then checks for further Children of those Children and so on down the family tree, eventually returning an array of all users in the chosen Parents lineage.

    Example of my DB Table
    **id, name, uuid, parent**
    1, John, 0001, none
    2, Steve, 0002, 0001
    3, Mark, 0003, 0001
    4, Kevin, 0004, 0002
    5, Adam, 0005, 0003

    function checkForChildren($uuid, $conn){
        global $familyArray;
    
        $sql = "SELECT id, uuid, name FROM people WHERE parent = '".$uuid."'";
        $result = mysqli_query($conn, $sql);
        
        /*-- Data has been found --*/
        if (mysqli_num_rows($result) > 0){
            foreach ($result as $row){
                $familyArray[]= 
                [
                    'id' => $row['id'],
                    'uuid' => $row['uuid'],
                    'name' => $row['name']
                ];                
                checkForChildren($row['uuid'],$conn);
            }
        }
        return $familyArray;
    }

The code works fine with $familyArray as a global variable, but I have come to understand that using global variables isn't good practice.

Without $familyArray being global, the function no longer returns the full family lineage, only the direct descendants of the parent $uuid we are originally feeding into the function.

Does anybody have any idea of ways to make this work without the array being global?

Thanks in advance.

Answer 1

You could return array result from the recursive call and array merge them at the parent level call.

<?php

function checkForChildren($uuid, $conn){
    $familyArray = [];

    $sql = "SELECT id, uuid, name FROM people WHERE parent = '".$uuid."'";
    $result = mysqli_query($conn, $sql);

    foreach ($result as $row){
        $familyArray[] = [
            'id' => $row['id'],
            'uuid' => $row['uuid'],
            'name' => $row['name']
        ];                
        $familyArray = array_merge($familyArray,checkForChildren($row['uuid'],$conn));
    }

    return $familyArray;
}

Update:

I would suggest you to have a look at the with recursive cte queries to avoid roundtrips via DB query calls to DB server.