I wanted to build the infinite level of categories and subcategories on the fly from a MySQL database. So I am going to give an example on infinite multi level nested category with PHP and MySQL.

You may read also Infinite dynamic Multi-level nested category with Codeigniter and MySQL


MySQL 5.5, PHP 7.0.15, Apache 2.4

Example with Source Code

Creating MySQL Table

First create a category table in MySQL database because we want to create the multi level nested category from database table.

CREATE TABLE `category` (
  `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(50) COLLATE latin1_general_ci NOT NULL,
  `category_link` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `parent_id` int(10) unsigned NOT NULL DEFAULT '0',
  `sort_order` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `unique` (`category_name`)

Notice how we have created the structure of the category table.  This category table has parent category id that identifies the parent category for this child.

If there is no value other than 0 for this parent category id then we will assume that this category is the top level category.

The table has following five columns
category_id – autoincremented positive int value, the primary key as well
category_name – name or level of the viewable category, the unique key
category_link – url for this category
parent_id – who is the parent of this category. For root category, the parent_id is 0.
sort_order – the order – what comes next

Dumping Data

We have created table above, so now in order to test the completed application we need some data into it. Let’s put some data into it:

insert  into `category`(`category_id`,`category_name`,`category_link`,`parent_id`,`sort_order`) values 

The data in the category table after insertion are shown in the below image:

infinite multi level nested category

Fetching Data from Table

Now fetch the result using the following query. We are fetching columns category_id, category_name, category_link, parent_id and sort_order to construct our infinite multi level nested category using php.

FROM `cdcol`.`category`
ORDER BY parent_id, sort_order, category_name

Here is the final result after executing the above query:

infinite multi level nested category

Building Parent-Children Hierarchy

The following logic retrieves the results from the category table and puts the results into a multidimensional array.

The $category array contains two other arrays, categories holds every result from the category table query and the parent_cats array holds a list of all category ids that have children.

Next we use a while statement to run through the sql results and assign row to the arrays.

If the row parent id already exists in the parent_cats array it will be overwritten so there will only be one of each parent id listed.

//select all rows from the category table
$result = mysqli_query("SELECT
	category_id, category_name, category_link, parent_id, sort_order
	FROM category
	ORDER BY parent_id, sort_order, category_name");

//create a multidimensional array to hold a list of category and parent category
$category = array(
	'categories' => array(),
	'parent_cats' => array()

//build the array lists with data from the category table
while ($row = mysqli_fetch_assoc($result)) {
	//creates entry into categories array with current category id ie. $categories['categories'][1]
	$category['categories'][$row['category_id']] = $row;
	//creates entry into parent_cats array. parent_cats array contains a list of all categories with children
	$category['parent_cats'][$row['parent_id']][] = $row['category_id'];

Now build the category and subcategory function which will display the final results for infinite multi level nested category.

In the following function we assume that the parent category id has 0 value and it is the top level category and all other categories fall under that parent category.

Here we use the recursive function to build the infinite multi level nested category. The logic is simply checks if there is any child category then call the function recursively otherwise write the category under the parent or write as a parent category.

function buildCategory($parent, $category) {
		$html = "";
		if (isset($category['parent_cats'][$parent])) {
			$html .= "<ul>\n";
			foreach ($category['parent_cats'][$parent] as $cat_id) {
				if (!isset($category['parent_cats'][$cat_id])) {
					$html .= "<li>\n  <a href='" . $category['categories'][$cat_id]['category_link'] . "'>" . $category['categories'][$cat_id]['category_name'] . "</a>\n</li> \n";
				if (isset($category['parent_cats'][$cat_id])) {
					$html .= "<li>\n  <a href='" . $category['categories'][$cat_id]['category_link'] . "'>" . $category['categories'][$cat_id]['category_name'] . "</a> \n";
					$html .= buildCategory($cat_id, $category);
					$html .= "</li> \n";
			$html .= "</ul> \n";
		return $html;

Once you are done with the above function then you want to use use the above function. So use the below code wherever you want to display (mostly in the menu area) the infinite multi level category.

Usage Detail

Display the result using the following example:

echo buildCategory(0, $category);

Final Result

Final output in the browser

infinite multi level nested category

You got the idea how to build a infinite multi level nested category and subcategory.

Thanks for reading.


13 thoughts on “Infinite dynamic Multi-level nested category with PHP and MySQL

  1. Hello,
    Thanks for sharing, but this code don’t produce valid HTML format. The tag open for parent categories and never close. While the browser do show a correct structure, inspecting the source code generated by the function, show that the tags don’t close correctly.

  2. If I have to insert categories and subcategories from the page and update the database but only through dropdown list how it will happen.

  3. Thank you Soumitra. The code works nice for me. Do you know about some solution for bootstrap collapsed menu based on code? I’m trying to find a bootstrap based menu where parent/children can be navigated to url but also expanded upon clicking.

  4. nice tutorial thank you

    how can i remove from the very top of list menu which has parent_id=0 names.
    then i need display to anyplaces for parent_id=0 names..

Leave a Reply

Your email address will not be published. Required fields are marked *