How to Create Dynamic Category Subcategory Tree using PHP and MySQL

Pradeep Maurya

6 years ago

Category and subcategory tree view structure gives an easy to use approach to list the parent and child categories. The category and their subcategory are effectively isolated by a tree structure. The categories tree view is constantly recommended to display a boundless level of categories and subcategories.

In this instructional exercise, we will show you industry standards to make dynamic category subcategory tree using PHP and MySQL. The recursive category tree is extremely helpful to list n level categories in a dropdown. The code help you to make n level category subcategory dropdown in PHP. The dynamic categories information will be recovered from the MySQL database and recorded in a parent-child category tree organize.

Complete Code


<?php
// Database configuration
$dbHost     = "localhost";
$dbUser = "root";
$dbPass = "";
$dbName     = "tutorialswebsite";

// Create database connection
$db = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}

?>

<html>
<head>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" media="screen">

 <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>
<script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<title>How to Create Dynamic Category Subcategory Tree using PHP and MySQL | tutorialswebsite.com</title>
<style>
.content {
  position: relative;
  margin: 60px auto;
  width: 100%;
  max-width: 640px;
  z-index: 1;
}
.clearfix{
    border-bottom: 1px dotted #ccc;
    margin-bottom: 5px;
}
.demo-title{
    background-color: #8cc63e;
    border-color:#8cc63e;
color:#fff;
padding:10px;
text-align:center;
}
.demo-title h4{
font-size: 22px;
    font-weight: 700;
    text-shadow: 2px 2px #00A058;
}
.select-boxes {
    width: 280px;
    text-align: center;
    margin: 0 auto;
}
select{
background-color: #F5F5F5;
    border: 1px double #584a41;
    color: #8cc63e;
    font-family: Georgia;
    font-weight: bold;
    font-size: 14px;
    height: 39px;
    padding: 7px 8px;
    width: 250px;
    outline: none;
    margin: 10px 0 10px 0;
}
</style>

<script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
</head>

<body style="background-repeat: no-repeat;">

<div class="demo-title"><h4>DEMO BY <span class="one">TUTORIALS</span><span class="two">WEBSITE</span>: How to Add Remove Input Fields Dynamically Using Jquery
 </h4></div>

<div class="col-md-12">
<div class="content">
 <div class="col-lg-12">
<?php
function cateSubcatTree($parent_id = 0, $sub_mark = ''){
    global $db;
    $query = $db->query("SELECT * FROM product_categories WHERE parent_id = $parent_id ORDER BY name ASC");
   
    if($query->num_rows > 0){
        while($row = $query->fetch_assoc()){
            echo '<option value="'.$row['id'].'">'.$sub_mark.$row['name'].'</option>';
            cateSubcatTree($row['id'], $sub_mark.'--');
        }
    }
}
?>
<div class="select-boxes">
<select name="cat-subcat">
    <?php cateSubcatTree(); ?>
</select>
</div>
</div>
</div>
</div>
</div>
</body>
</html>

100

101

102

103

104

105

106

<?php

// Database configuration

$dbHost = "localhost";

$dbUser = "root";

$dbPass = "";

$dbName = "tutorialswebsite";

// Create database connection

$db = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

// Check connection

if ($db->connect_error) {

die("Connection failed: " . $db->connect_error);

}

<html>

<head>

<title>How to Create Dynamic Category Subcategory Tree using PHP and MySQL | tutorialswebsite.com</title>

<style>

.content {

position: relative;

margin: 60px auto;

width: 100%;

max-width: 640px;

z-index: 1;

}

.clearfix{

border-bottom: 1px dotted #ccc;

margin-bottom: 5px;

}

.demo-title{

background-color: #8cc63e;

border-color:#8cc63e;

color:#fff;

padding:10px;

text-align:center;

}

.demo-title h4{

font-size: 22px;

font-weight: 700;

text-shadow: 2px 2px #00A058;

}

.select-boxes {

width: 280px;

text-align: center;

margin: 0 auto;

}

select{

background-color: #F5F5F5;

border: 1px double #584a41;

color: #8cc63e;

font-family: Georgia;

font-weight: bold;

font-size: 14px;

height: 39px;

padding: 7px 8px;

width: 250px;

outline: none;

margin: 10px 0 10px 0;

}

</style>

</head>

<div class="demo-title"><h4>DEMO BY <span class="one">TUTORIALS</span><span class="two">WEBSITE</span>: How to Add Remove Input Fields Dynamically Using Jquery

</h4></div>

<?php

function cateSubcatTree($parent_id = 0, $sub_mark = ''){

global $db;

$query = $db->query("SELECT * FROM product_categories WHERE parent_id = $parent_id ORDER BY name ASC");

if($query->num_rows > 0){

while($row = $query->fetch_assoc()){

echo '<option value="'.$row['id'].'">'.$sub_mark.$row['name'].'</option>';

cateSubcatTree($row['id'], $sub_mark.'--');

}

<?php cateSubcatTree(); ?>

</select>

</div>

</body>

</html>

Create Database Table

To insert categories and subcategories, a table should be made in the database. The below SQL makes a categories table in the MySQL database.


CREATE TABLE IF NOT EXISTS `product_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1:Active, 0:Inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

CREATE TABLE IF NOT EXISTS `product_categories` (

`id` int(11) NOT NULL AUTO_INCREMENT,

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

`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,

`created` datetime NOT NULL,

`modified` datetime NOT NULL,

`status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1:Active, 0:Inactive',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

The parent_id table column determines the parent or child category. In the event if parent_id is 0, it will be a parent category. Else, it will be a child category.

Create Database Configuration


<?php
// Database configuration
$dbHost     = "localhost";
$dbUser = "root";
$dbPass = "";
$dbName     = "tutorialswebsite";

// Create database connection
$db = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

// Check connection
if ($db->connect_error) {
    die("Connection failed: " . $db->connect_error);
}

?>

<?php

// Database configuration

$dbHost = "localhost";

$dbUser = "root";

$dbPass = "";

$dbName = "tutorialswebsite";

// Create database connection

$db = new mysqli($dbHost, $dbUser, $dbPass, $dbName);

// Check connection

if ($db->connect_error) {

die("Connection failed: " . $db->connect_error);

}

The cateSubcatTree() function creates a n level category subcategory dropdown option for categories tree.


<?php
function cateSubcatTree($parent_id = 0, $sub_mark = ''){
    global $db;
    $query = $db->query("SELECT * FROM product_categories WHERE parent_id = $parent_id ORDER BY name ASC");
   
    if($query->num_rows > 0){
        while($row = $query->fetch_assoc()){
            echo '<option value="'.$row['id'].'">'.$sub_mark.$row['name'].'</option>';
            cateSubcatTree($row['id'], $sub_mark.'--');
        }
    }
}
?>

<?php

function cateSubcatTree($parent_id = 0, $sub_mark = ''){

global $db;

$query = $db->query("SELECT * FROM product_categories WHERE parent_id = $parent_id ORDER BY name ASC");

if($query->num_rows > 0){

while($row = $query->fetch_assoc()){

echo '<option value="'.$row['id'].'">'.$sub_mark.$row['name'].'</option>';

cateSubcatTree($row['id'], $sub_mark.'--');

}

$parent_id – Optional. Specify the parent category ID to get the child categories.
$sub_mark – Optional. Sub Mark that will display at the beginning of the child category name.

Dropdown for Category Subcategory in PHP

We will use cateSubcatTree() function to generate dynamic categories tree dropdown in PHP with MySQL.


<select name="cat-subcat">
    <?php cateSubcatTree(); ?>
</select>

<?php cateSubcatTree(); ?>

</select>

Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request

[sociallocker]

Download Source Code View Demo

[/sociallocker]

Pradeep Maurya

Pradeep Maurya is the Professional Web Developer & Designer and the Founder of “Tutorials website”. He lives in Delhi and loves to be a self-dependent person. As an owner, he is trying his best to improve this platform day by day. His passion, dedication and quick decision making ability to stand apart from others. He’s an avid blogger and writes on the publications like Dzone, e27.co