How to Retrieve Recursive Categories in a Single MySQL Query?

Recursive Category Retrieval with a Single MySQL Query

In web applications, organizing content into hierarchical categories is common. A complex category structure, such as a tree, requires specialized handling for retrieval. This article explores the most efficient way to fetch recursive categories with a single MySQL query.

Building the category tree in PHP using references optimizes the process. A query retrieves categories and their parent IDs, which are stored in an associative array ($nodeList). The array is structured to create hierarchical references where each node's parent appears in its 'parent' key.

The tree construction occurs in the following code:

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

This loop iterates over the nodes, and if a node has no parent or the parent doesn't exist in the array, the node is added to the $tree array. Otherwise, the node is added as a child to its parent's 'children' array.

The result is a hierarchical tree structure stored in the $tree array, with each node containing children references. This single-query approach is significantly faster than using recursive MySQL queries, even for large category trees.

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