How to Visualize Hierarchical Data (Categories) in PHP/MySQL?

Category Hierarchy in PHP/MySQL

When dealing with hierarchical data, such as categories, it's crucial to represent them in a structured manner. One common approach is using an adjacency list model, where each row represents a category and has a field indicating its parent category.

Obtaining the Hierarchy

Using PHP, you can retrieve the category data from MySQL and build the hierarchy in a single pass. The code below, inspired by Nate Weiner's "One Pass Parent-Child Array Structure":

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

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

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

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

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

This loop populates two arrays: $refs contains references to each category, while $list contains the top-level categories (with no parents).

Creating a Hierarchical Representation

To create a visual representation of the hierarchy, you can use a recursive function to generate a nested HTML list. Consider the following code:

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

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

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

    return $html;
}</code>

This function takes an array of categories as input and recursively builds a nested HTML list. The resulting HTML can then be outputted to display the category hierarchy.

The above is the detailed content of How to Visualize Hierarchical Data (Categories) in PHP/MySQL?. For more information, please follow other related articles on the PHP Chinese website!