How to Retrieve Hierarchical Category Data in PHP/MySQL with Just One Database Pass?

Category Hierarchy in PHP/MySQL

In PHP/MySQL, it is highly efficient to store categories and subcategories in a hierarchical structure using an adjacency list model. To retrieve this hierarchical data effectively, we can employ an approach that requires just one database pass.

One-Pass Data Retrieval Algorithm

Let's create a $refs array and a $list array, analogous to what has been presented in the provided answer.

<code class="php">$refs = [];
$list = [];

$sql = "SELECT category_id, parent_id, name FROM categories ORDER BY name";

$result = $pdo->query($sql);

foreach ($result as $row) {
    $ref = &$refs[$row['category_id']];

    $ref['parent_id'] = $row['parent_id'];
    $ref['name'] = $row['name'];

    if ($row['parent_id'] == 0) {
        $list[$row['category_id']] = &$ref;
    } else {
        $refs[$row['parent_id']]['children'][$row['category_id']] = &$ref;
    }
}</code>

This algorithm efficiently builds a hierarchical data structure. The $refs array holds references to all categories, and the $list array contains references to the top-level categories (those with no parent).

Recursive List Generation

To output the hierarchical structure as an HTML list, a recursive function like the following can be utilized:

<code class="php">function toUL(array $array)
{
    $html = '<ul>';

    foreach ($array as $value) {
        $html .= '<li>' . $value['name'];
        if (!empty($value['children'])) {
            $html .= toUL($value['children']);
        }
        $html .= '</li>';
    }

    $html .= '</ul>';

    return $html;
}</code>

This function recursively builds an HTML list, efficiently representing the hierarchical data obtained from the database.

The above is the detailed content of How to Retrieve Hierarchical Category Data in PHP/MySQL with Just One Database Pass?. For more information, please follow other related articles on the PHP Chinese website!