How to Retrieve Recursive Categories from a MySQL Database with a Single Query?

Retrieving Recursive Categories with a Single MySQL Query

In the context of organizing website content, recursive categories allow for hierarchical structures with multiple levels. To efficiently retrieve such categories from a MySQL database, a single query is typically employed.

MySQL offers the CONNECT_BY_ROOT operator, which enables recursive selection of data. Consider the following query:

SELECT category_id, name, parent
FROM categories
CONNECT BY ROOT parent
ORDER BY LEVEL

This query retrieves all categories in the table, connecting them hierarchically based on the parent column. The LEVEL function assigns a level to each row, indicating its position within the hierarchy, with the top-level category having a level of 1.

To build the recursive tree structure in PHP, convert the query results into an array, where each category is represented as a node with a children property. The following example illustrates the process:

$nodeList = [];
$tree = [];

$query = mysql_query("SELECT category_id, name, parent FROM categories ORDER BY parent");
while ($row = mysql_fetch_assoc($query)) {
    $nodeList[$row['category_id']] = array_merge($row, ['children' => []]);
}

foreach ($nodeList as $nodeId => &$node) {
    if (!$node['parent'] || !array_key_exists($node['parent'], $nodeList)) {
        $tree[] = &$node;
    } else {
        $nodeList[$node['parent']]['children'][] = &$node;
    }
}
unset($node);
unset($nodeList);

// Return the tree structure
return $tree;

This PHP code constructs a multidimensional array reflecting the hierarchical relationships among categories. The top-level categories are placed in the $tree array, and their children are nested within their respective children arrays.

This approach utilizes references to nodes, which minimizes memory usage compared to creating new node objects. It also avoids recursive SQL queries, making it more efficient for large tree structures.

The above is the detailed content of How to Retrieve Recursive Categories from a MySQL Database with a Single Query?. For more information, please follow other related articles on the PHP Chinese website!