How can I convert database results into a multidimensional array to represent a hierarchical data structure?

Converting Database Results to Arrays

Introduction

This discussion focuses on retrieving a hierarchical data structure from a database and converting it into a multidimensional array in a single query. The goal is to obtain an array representing the tree structure of the data.

Solution

To extract a tree structure from a database, a closure table is commonly employed. A closure table records the relationships between ancestors and descendants within a hierarchy, enabling efficient querying of descendants.

SQL Query

Given the primary key of a node, querying its descendants using SQL can be achieved using the following steps:

1. Join the closure table with the primary table to find all descendants.
2. Optionally join another instance of the closure table to include parent information.
3. Limit the number of levels to retrieve based on the depth parameter.
4. Order the results by their distance from the root node.

Post-Processing

The SQL result is then processed in the following manner:

1. Rows are sorted into subsets based on their hierarchical relationship.
2. Each row is converted into a row object, which includes a collection of child rows.
3. A rowset class is used to represent a collection of rows, each containing its children.
4. The resulting tree is constructed by starting from the root node and recursively adding its children.

Code Example

In PHP, using the Zend Framework, the following code demonstrates the process:

// Get taxonomy table instance
$tax = new Taxonomy();

// Fetch tree starting at Rodentia (id 180130) to a depth of 2
$tree = $tax->fetchTree(180130, 2);

// Dump the array
var_export($tree->toArrayDeep());

Output

The output is a multidimensional array representing the tree structure of the data, such as:

array (
  'tsn' => '180130',
  'completename' => 'Rodentia',
  '_parent' => '179925',
  '_children' => [
    // Child rows...
  ],
)

The above is the detailed content of How can I convert database results into a multidimensional array to represent a hierarchical data structure?. For more information, please follow other related articles on the PHP Chinese website!