Product’s Nested Categories Display in PHP and MySQL

Nested Categories Fetched From Database

Introduction

Are you working on an E commerce Project or any other project which has the hierarchical structure of records in the database ?

One practical examples of such scenario is the categories of products in an eCommerce web application.

This article will walk you through the process of building and displaying nested categories using PHP and MySQL. I’ll cover everything from database schema creation to rendering a hierarchical tree in HTML, ensuring you have a comprehensive solution to implement in your projects.

Database Schema

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

--
-- Dumping data for table `categories`
--

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

The first step is to fetch all categories from the database. We’ll use a simple SQL query and PHP’s mysqli extension to achieve this.

<?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 Structure

With our categories retrieved, the next step is to organize them into a tree structure based on their parent_id relationships. We'll create a recursive function to handle this.

<?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); // $categories from step 1
?>

Step 3: Display the Hierarchical Structure

Now that we have our hierarchical structure, the final step is to render it as an HTML list. We’ll use another recursive function to generate the HTML.

<?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); // $categoryTree from step 2
?>

Conclusion

Try out the code and you should see the categories nested properly.