How to Convert Database Results into a Multidimensional Array Using a Closure Table?

Converting Database Results to Multidimensional Array

In the realm of data management, it's often necessary to organize and present hierarchical data structures in a user-friendly and readily digestible format. To achieve this, the "Closure table" approach can be an effective solution. This technique entails maintaining a separate table to store relationships between hierarchical elements, known as the Closure table.

By utilizing a Closure table, it becomes possible to efficiently retrieve subtrees and generate hierarchical data arrangements. For instance, consider the following example:

Table Categories:

ID         Name
1          Top value
2          Sub value1

Table CategoryTree:

child     parent     level
1          1         0
2          2         0  
2          1         1  

The objective is to obtain the hierarchical data as a multidimensional array. The desired output would resemble the following:

 array (

 'topvalue' = array (
                     'Subvalue',
                     'Subvalue2',
                     'Subvalue3)
                     );

 );

To achieve this, we can leverage SQL queries and row processing techniques. Consider the following pseudocode:

while ($rowData = fetch()) {
  $row = new RowObject($rowData);
  $nodes[$row["tsn"]] = $row;
  if (array_key_exists($row["_parent"], $nodes)) {
    $nodes[$row["_parent"]]->addChildRow($row);
  } else {
    $top = $row;
  }
}
return $top;

Here, we define classes for rows and rowsets, where a rowset represents an array of rows, and a row contains an associative array of row data along with a rowset for its children. We also define methods within rows and rowsets to dump their data as a plain array recursively.

Finally, we can use this entire system as follows:

// Get an instance of the taxonomy table data gateway 
$tax = new Taxonomy();

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

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

This process allows for flexible and customizable manipulation of hierarchical data, providing an elegant and organized approach to presenting complex relationships.

The above is the detailed content of How to Convert Database Results into a Multidimensional Array Using a Closure Table?. For more information, please follow other related articles on the PHP Chinese website!