How to Construct a Category Hierarchy in PHP and MySQL?

Category Hierarchy in PHP and MySQL

In a typical database structure, categories are organized in a hierarchical manner, with parent categories containing child categories. Retrieving this hierarchy from a MySQL database can be a challenging task. This article explores how to effectively construct a category hierarchy using PHP and MySQL.

Adjacency List Model

The adjacency list model represents hierarchical data by storing parent-child relationships in a single table. Each row in the table contains the category's ID and its parent's ID. This model allows for efficient retrieval of the category tree using a single SQL query.

Generating the Hierarchy

To generate the category hierarchy, we utilize the following PHP code:

<code class="php">$refs = array();
$list = array();

$sql = "SELECT item_id, parent_id, name FROM items ORDER BY name";
$result = $pdo->query($sql);

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

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

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

This code iterates through the result set, creating references to each category in an array. It uses parent-child relationships to build a multidimensional array that represents the hierarchy.

Creating an Output List

To display the hierarchy, we employ a recursive function:

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

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

    $html .= '</ul>' . PHP_EOL;

    return $html;
}</code>

This function takes the multidimensional array and generates an HTML unordered list representing the category hierarchy. It recursively traverses the hierarchy, creating nested lists as needed.

The above is the detailed content of How to Construct a Category Hierarchy in PHP and MySQL?. For more information, please follow other related articles on the PHP Chinese website!