How To Create Parent Child Category Tree Using PHP & MySQLi

In this tutorial we are going to explain how to create nth level category tree using PHP and MySQLi. We have write a very simple php function and call recursivily.

 

First we have created a database table with parent child mapping of categories:

 CREATE TABLE `tbl_categories` (

   `id` INT(11) NOT NULL AUTO_INCREMENT,

   `name` VARCHAR(100) NOT NULL DEFAULT '0',

   `parent_id` INT(11) NOT NULL DEFAULT '0',

   PRIMARY KEY (`id`)

 )

 ENGINE=InnoDB;

 

 Insert dummy categories:

 

 INSERT INTO `tbl_categories` (`id`, `name`, `parent_id`) VALUES
(1, 'Hardware', 0),
(2, 'Software', 0),
(3, 'Movies', 0),
(4, 'Clothes', 0),
(5, 'Printers', 1),
(6, 'Monitors', 1),
(7, 'Inkjet printers', 5),
(8, 'Laserjet Printers', 5),
(9, 'LCD monitors', 6),
(10, 'TFT monitors', 6),
(11, 'Antivirus', 2),
(12, 'Action movies', 3),
(13, 'Comedy Movies', 3),
(14, 'Romantic movie', 3),
(15, 'Thriller Movies', 3),
(16, 'Mens', 4),
(17, 'Womens', 4),
(18, 'Shirts', 16),
(19, 'T-shirts', 16),
(20, 'Shirts', 16),
(21, 'Jeans', 16),
(22, 'Accessories', 16),
(23, 'Tees', 17),
(24, 'Skirts', 17),
(25, 'Leggins', 17),
(26, 'Jeans', 17),
(27, 'Accessories', 17),
(28, 'Watches', 22),
(29, 'Tie', 22),
(30, 'cufflinks', 22),
(31, 'Earrings', 27),
(32, 'Bracelets', 27),
(33, 'Necklaces', 27),
(34, 'Pendants', 27);

 

Category Parent Child Tree Function:

 <?php

function categoryParentChildTree($parent = 0, $spacing = '', $category_tree_array = '') {
    global $dbConnection;
    $parent = $dbConnection->real_escape_string($parent);
    if (!is_array($category_tree_array))
        $category_tree_array = array();

    $sqlCategory = "SELECT id,name,parent_id FROM tbl_categories WHERE parent_id = $parent ORDER BY id ASC";
    $resCategory=$dbConnection->query($sqlCategory);
   
    if ($resCategory->num_rows > 0) {
        while($rowCategories = $resCategory->fetch_assoc()) {
            $category_tree_array[] = array("id" => $rowCategories['id'], "name" => $spacing . $rowCategories['name']);
            $category_tree_array = categoryParentChildTree($rowCategories['id'], '    '.$spacing . '- ', $category_tree_array);
        }
    }
    return $category_tree_array;
}  ?>

 index.php

<?php
    require_once 'db_connection.php';
    require_once 'functions.php';
   
    $categoryList = categoryParentChildTree();
    foreach($categoryList as $key => $value){
        echo $value['name'].'<br>';
    }  ?>

 

 If you have any problem regarding this tutorial configuration please feel free to comment we love to answer your queries.