Create Category tree with PHP and mysql

by  
Share:
Create Category tree with PHP and mysql

Every now and then you will come across to a situation where you have to make a recursive calls to get all your parent and childs in an array or in a dropdown or in a list format. In this tutorial you will learn how to create category tree with PHP and mysql. You can then use this category tree to display in a dropdown box or list.

View Demo

Creating a mysql table with parent and child columns

CREATE TABLE IF NOT EXISTS `category` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `parent` int(11) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Insert some dummy records. Parent columns with zero value denotes its the base/root category.
INSERT INTO `category` (`cid`, `name`, `parent`) 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);

Creating category Tree using recursion

So that the database is all set up, create a php function to recursively get all the category grouping them ito their parent category.

function fetchCategoryTree($parent = 0, $spacing = '', $user_tree_array = '') {

  if (!is_array($user_tree_array))
    $user_tree_array = array();

  $sql = "SELECT `cid`, `name`, `parent` FROM `category` WHERE 1 AND `parent` = $parent ORDER BY cid ASC";
  $query = mysql_query($sql);
  if (mysql_num_rows($query) > 0) {
    while ($row = mysql_fetch_object($query)) {
      $user_tree_array[] = array("id" => $row->cid, "name" => $spacing . $row->name);
      $user_tree_array = fetchCategoryTree($row->cid, $spacing . '  ', $user_tree_array);
    }
  }
  return $user_tree_array;
}

The working of this function is pretty simple. It will start its iteration with the parent category and will call the function itself if it has child category and again if childs has childs until the child has no childs (phew!!! quite a line it was). The value returned by the function is stored in the array and is carried forward in the recursive function. So finally at the end of the function we will have an array with all categories grouped under the subcategories. The $spacing parameter is just just to indent the child category.

Display the category tree in a dropdown list.

Now that the hard work is done. time to eat the fruit.

<?php 
$categoryList = fetchCategoryTree();
?>
<select>
<?php foreach($categoryList as $cl) { ?>
  <option value="<?php echo $cl["id"] ?>"><?php echo $cl["name"]; ?></option>
<?php } ?>
</select>

Displaying Category tree in list format

function fetchCategoryTreeList($parent = 0, $user_tree_array = '') {

    if (!is_array($user_tree_array))
    $user_tree_array = array();

  $sql = "SELECT `cid`, `name`, `parent` FROM `category` WHERE 1 AND `parent` = $parent ORDER BY cid ASC";
  $query = mysql_query($sql);
  if (mysql_num_rows($query) > 0) {
     $user_tree_array[] = "<ul>";
    while ($row = mysql_fetch_object($query)) {
	  $user_tree_array[] = "<li>". $row->name."</li>";
      $user_tree_array = fetchCategoryTreeList($row->cid, $user_tree_array);
    }
	$user_tree_array[] = "</ul>";
  }
  return $user_tree_array;
}

To display the category use this snippet below.
<ul>
<?php
  $res = fetchCategoryTreeList();
  foreach ($res as $r) {
    echo  $r;
  }
?>
</ul>

View Demo
Share to download the Source Codes for FREE!
We're glad to give free downloads, but we need your love to carry on making that.
Please support us by sharing the page.

Get the latest updates directly in your inbox for FREE: Subscribe here

Share your feedback / let me know your doubts regarding this tutorial in the comment box given below.

Share:
Hasan

Shahrukh Khan (Hasan)

A software engineer who's a die-hard coder, blogger, dreamer and mentor with years of expertise in web development. Know more...

Related Posts

Andrew on

thanks for the info. I am looking for optgroup for each parent category, how can I achieve this?

Reply
Shahrukh Khan on

You need to customize that a bit. use options tag instead of li tags and use optgroup tag insted of ul

Reply
HGN on

Hi Khan, can I ask you what does the “WHERE 1” means in the $sql query? Thanks!

Reply
Shahrukh Khan on

WHERE 1 is just a coding style, I use 1 because if I am making any further changes in the query (on the condition part) I will have to use AND before each condition. Its just a preference, you may or may not use it.

Reply
HGN on

Got it, thank you for answering!

janak vaghani on

good job boss-janak vaghani

Reply
Rameez on

bro. ,

how to insert in this table whth html form ?

Reply
Shahrukh Khan on

bro. do you want to store IDS or anything else?

Reply
Rameez on

can’t getting the way to store category of sub category..like this

main1
sub -main1
sub-sub-main1

Rameez on

brother,
i have to create, show and update also categories from front end. what will be the way to update with html form ?

Jazakumullaah .. for this good helping blog ..keep it up brother.

Shahrukh Khan on

Create a table say “tbl_category” with 3 fields ID, NAME and PARENT. the parent column default value will be zero. Now when you create html form give column for name (textfield) and a parent field (dropdown) with will have a option with “0” i.e. for parent and rest options with generated using a sql query result that will fetch all category list from “tbl_category”.

Victor on

Hi!
Why don’t you do ONE sql query and than process recursive function to build an array of category tree instead of multiple queries? You can decrease php-server load… it’s obvious, am I right? 😉

Reply
Shahrukh Khan on

I know the current one makes more query calls to mysql but that’s the best solution I found so far. If you have any better sql query please share with us.

Reply
Rasmi Ranjan Swain on

Hi,
im getting this error when i call function recursively

Warning: mysqli::mysqli() [mysqli.mysqli]: (08004/1040): Too many connections

Reply
Asif RAihan on

Thanks a lot

Reply
Orangedan on

How can I have root categories before child category? like this:

– Hardware
– Hardware > Printers
– Hardware > Printers > Inkjet printers
– Hardware > Printers > Laserjet Printers
– Hardware > Monitors
– Hardware > Monitors > LCD monitors
– Hardware > Monitors > TFT monitors

Reply
Shahrukh Khan on

I think that is you need to pass the name of the parent in the parameter as well…

Reply
Vtrembec on

Could you please show us in the code how to do this? Thank you in advance

– Hardware
– Hardware > Printers
– Hardware > Printers > Inkjet printers
– Hardware > Printers > Laserjet Printers
– Hardware > Monitors
– Hardware > Monitors > LCD monitors
– Hardware > Monitors > TFT monitors

Shahrukh Khan on

you mean sorting / order / or insertion.

charanjeet singh on

Hii

Shahrukh khan

i have to make an auto increment in price when the qty is increase could you help me to done this task in a simple way using php

Reply
charanjeet singh on

And also make an custom search function for a static web site in php as well as jquery .

Reply
Shahrukh Khan on

you want to do that with php or javascript

Reply
Roy on

i signup and get a code , with my referral code only two can join , more then two members cant join its condition . Then first signup person should come on left and second person on right , like this it should go chain process , please help me from this

Reply
Shahrukh Khan on

This is a very good problem. I can code it, but i would also like you to post your some code here. so I can know you are working on this as well and not just need a total work done by others.

Reply
Rahul on

is there any way to lower SQL queries? i mean if i have thousand of categories and sub categories then it will make thousands of queries on single time.
Single page = thousands of SQL queries
thousands of visitors = millions of SQL queries on single page .
Server Result = Always Down After processing few pages.

i am trying to make my own code within one or few SQL queries…

Reply
Shahrukh Khan on

Export the sql using mysqldump

Reply
kani on

how to add category as parent cat

Reply
Shahrukh Khan on

just provide the parent category ID in the parent ID

Reply
coder on

please fix this code to use mysqli i am trying but it keep breaking and please update the code id really appreciate it

Reply
Shahrukh Khan on

Ok sure, I will try to do it this weekend or the next. Stay Tuned

Reply
Pooja on

Can you help me how can I add data dynamically in your category table ?

Reply
Shahrukh Khan on

what exactly you want?

Reply
chandran on

Warning: mysqli_query() expects parameter 1 to be mysqli, null given in D:\xampp2\htdocs\category\functions.php on line 9

Reply
Shahrukh Khan on

make sure its not null

Reply
chandran on

how to convert mysql to mysqli

Reply
pooja on

I used your code it work well… Thank you

Please can you help me In Display the category tree in a dropdown list I need like, I show you in below

For Example In dropdown list Value like:

Hardware
Printers
Inject Printers
Laser Printers
Monitors
LCD Monitors
TFT Monitors

I need parent Hardware, Printers and Monitors child level are disabled , so you cant add new child for this two child.
How Can I do it please help me,
Thank you

Reply
Shahrukh Khan on

when you loop the values, just check if their parent values is not null and on options print disabled.

Reply
bill on

this code is posted in many sites with thesame code… dont know who really originally created this.
just wanna ask you question if u pls
1. if parent` int(11) NULL, how would u display it
2. the tables have no relationship, if you delete parent like 0 will display nothing but other data still there.

Shahrukh Khan on

1. you need to join the same table twice in the query to get the parent and child at the same time.
2. yes that is the drawback if its in the same table, if you want to can create two table like parent and child then your problem will be solved.

varun on

you can fetch the data in a associate array and display it using traversing each parent and child of that parent . no need to write two queries . you can achieve this by an associate array .

Reply
Shahrukh Khan on

hi. can you please paste the code for the community here.

Reply
Viren Mistry on

thanks for provide this example..It is very helpful for me.

but please help me, for when delete root category automatically delete it’s all sub-categories.

Reply
Shahrukh Khan on

its not safe to delete all category and subcategory in one go..but you can write php script to do so.

Reply
VAUTHIER on

I see $parent in the parameter and in the query…
But it is never affected, appart from 0 ? I don’t understand.

Reply
Shahrukh Khan on

its for passing the parent ID

Reply
Manojkiran on

please upgrade to php 7.2 or higher

Reply
Shahrukh Khan on

this is an old article, and yes it is always recommended to use the latest version of PHP

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.