What are best practices for retrieving and displaying hierarchical data from a database in PHP?

Retrieving and displaying hierarchical data from a database in PHP involves fetching parent-child relationships and displaying them in a structured format. One common approach is to use recursive functions to traverse the hierarchy and build a nested array or HTML list for display. 

// Function to retrieve hierarchical data from the database
function getHierarchy($parent_id = 0) {
    $result = [];
    
    // Query to fetch children of the parent node
    $query = "SELECT * FROM hierarchy_table WHERE parent_id = $parent_id";
    $children = mysqli_query($connection, $query);
    
    while ($child = mysqli_fetch_assoc($children)) {
        $child['children'] = getHierarchy($child['id']); // Recursive call to fetch children of children
        $result[] = $child;
    }
    
    return $result;
}

// Display hierarchical data in a nested HTML list
function displayHierarchy($data) {
    echo '<ul>';
    foreach ($data as $node) {
        echo '<li>' . $node['name'];
        if (!empty($node['children'])) {
            displayHierarchy($node['children']); // Recursively display children
        }
        echo '</li>';
    }
    echo '</ul>';
}

// Usage example
$hierarchy = getHierarchy();
displayHierarchy($hierarchy);

Keywords

SQL recursion tree structure nested sets parent-child relationships

Related Questions