How to Build and Display Nested Category Trees with PHP & MySQL

Introduction

Are you working on an e‑commerce project or any system that requires hierarchical category management in a database?

This article guides you through building and displaying nested categories using PHP and MySQL, covering database schema creation, data retrieval, tree construction, and HTML rendering.

Database Schema

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    parent_id INT DEFAULT NULL
);
-- Insert sample data
INSERT INTO `categories` (`id`, `category_name`, `parent_id`) VALUES
(1, 'Glasses', 0),
(2, 'Pads', 0),
(3, 'Knee Pads', 2),
(4, 'Metal', 3),
(5, 'Plastic', 3),
(6, 'Black Metal', 4),
(7, 'Hard Metal', 6),
(8, 'White Metal', 4);

Step 1: Retrieve Categories from the Database

Use a concise SQL query with PHP's mysqli extension to fetch all category records.

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}
$sql = "SELECT id, category_name, parent_id FROM categories";
$result = $mysqli->query($sql);
$categories = [];
while ($row = $result->fetch_assoc()) {
    $categories[] = $row;
}
$mysqli->close();
?>

Step 2: Build the Hierarchical Tree

Based on the parent_id relationship, a recursive function assembles the categories into a tree.

<?php
function buildCategoryTree(array $elements, $parentId = 0) {
    $branch = [];
    foreach ($elements as $element) {
        if ($element['parent_id'] == $parentId) {
            $children = buildCategoryTree($elements, $element['id']);
            if ($children) {
                $element['children'] = $children;
            }
            $branch[] = $element;
        }
    }
    return $branch;
}
$categoryTree = buildCategoryTree($categories);
?>

Step 3: Render the Tree as HTML

Another recursive function converts the tree into nested <ul> / <li> lists.

<?php
function renderCategoryTree($categoryTree) {
    echo '<ul>';
    foreach ($categoryTree as $category) {
        echo '<li>' . $category['category_name'];
        if (!empty($category['children'])) {
            renderCategoryTree($category['children']);
        }
        echo '</li>';
    }
    echo '</ul>';
}
renderCategoryTree($categoryTree);
?>

Conclusion

Running the code produces a clear, correctly nested category structure displayed as an HTML list.