How to Retrieve All Child Nodes in a Hierarchical Data Structure with PHP and MySQL?

Retrieving Hierarchical Data with MySQL and PHP

Retrieving hierarchical data from a database can be challenging, especially when using an adjacency list data model. This question seeks to tackle this issue by creating a function that returns all child nodes, grandchildren, and so on, under a specified parent.

Building a Hierarchical Tree

First, the provided PHP code retrieves data from a MySQL table and stores it in an associative array. The adjacency list data model represents hierarchy by storing parent-child relationships in a single column (i.e., parent_id).

To transform this data into a tree structure, the buildtree() function traverses the array recursively, associating each node's ID with its data and creating a nested array with children as elements.

Fetching Nodes Under a Parent

The fetch_recursive() function is designed to retrieve all child nodes under a specified parent. It iterates through the tree structure, starting at the specified parent ID. If a node's parent ID matches the specified parent ID, it adds the node's data to the result array and proceeds to explore child nodes.

Example Usage

To demonstrate the functionality, the following PHP code builds a tree and retrieves child nodes under a specific ID:

<?php
$data = [
    ['id' => 1, 'name' => 'Electronics', 'parent_id' => 0],
    ['id' => 2, 'name' => 'Televisions', 'parent_id' => 1],
    ['id' => 3, 'name' => 'Portable Electronics', 'parent_id' => 1],
    // ... additional data
];

$tree = buildtree($data);
$child_nodes = fetch_recursive($tree, 3);

foreach ($child_nodes as $node) {
    echo $node['name'] . '<br>';
}

The above is the detailed content of How to Retrieve All Child Nodes in a Hierarchical Data Structure with PHP and MySQL?. For more information, please follow other related articles on the PHP Chinese website!