Category Hierarchy in PHP/MySQL

In this scenario, we aim to retrieve a hierarchical representation of categories and sub-categories from a MySQL database. Our objective is to structure the data in a nested format that mirrors the actual hierarchy within the database.

To achieve this, we leverage the adjacency list model, which enables us to generate the hierarchy in a single pass. We utilize a PHP script to process the data and construct the hierarchical representation.

The first step involves querying the database to retrieve the categories and sub-categories along with their respective parent-child relationships. Once the data is fetched, we create an array of references for quick access.

Next, we loop through the retrieved data and populate the references array. We identify the root categories (parent_id = 0) and add them to the list array. For sub-categories, we add them as children to the corresponding parent category in the references array.

To generate the hierarchical output, we define a recursive function, toUL(), which traverses the references array and constructs a nested HTML unordered list (UL). The function takes an array as input and creates a UL element.

Within the function, we iterate through the array and create a LI (list item) element for each category. If the category has children, we recursively call the toUL() function to construct the subtree. The function returns the constructed HTML representation of the hierarchy.

This method allows us to efficiently and accurately represent the category hierarchy in a nested form, which can be easily rendered as an HTML document or used for further processing.