Building and Displaying Nested Category Trees with PHP and MySQL

Introduction This article shows how to manage product categories in an e‑commerce system by creating a hierarchical structure stored in MySQL and displayed with PHP.

Database schema A simple categories table is defined with id , category_name , and parent_id columns, followed by sample data illustrating multiple levels of nesting.

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL,
    parent_id INT DEFAULT NULL
);

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 PHP uses the mysqli extension to connect to the database, execute a SELECT query, and load all rows into an array.

<?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 hierarchy A recursive function buildCategoryTree walks the flat list, grouping items whose parent_id matches the current node, and attaches child arrays under a children key.

<?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); // from Step 1
?>

Step 3: Render hierarchy Another recursive function renderCategoryTree outputs the tree as nested <ul> / <li> HTML elements.

<?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); // from Step 2
?>

Conclusion Running the provided scripts produces a clear, correctly nested category list that can be directly used in e‑commerce sites to display product classifications.