Introduction

I am going to show you how to build infinite dynamic multi level nested category with PHP and MySQL server. Here I will show example of 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.

Related Posts:

Prerequisites

MySQL 8.0.17, PHP 7.4.3, Apache 2.4

Project Setup

It’s assumed that you have setup Apache and PHP in Windows system.

Now we will create a project root directory called php-mysql-infinite-level-nested-category under the Apache server’s htdocs folder.

I may not mention the project root directory in subsequent sections and I will assume that I am talking with respect to the project root directory.

Creating MySQL Table

First you need to create a category table under roytuts database in MySQL server because we want to create the multi level nested category from database table.

CREATE TABLE `category` (
  `category_id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(50) NOT NULL,
  `category_link` varchar(255) NOT NULL,
  `parent_id` int unsigned NOT NULL DEFAULT '0',
  `sort_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `unique` (`category_name`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Notice how I 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 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 – auto incremented 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

I have created table above, so now in order to test the application once it is finished you 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 
(1,'Home','',0,0),
(2,'Tutorials','#',0,1),
(3,'Java','java',2,1),
(4,'Liferay','liferay',2,1),
(5,'Frameworks','#',0,2),
(6,'JSF','jsf',5,2),
(7,'Struts','struts',5,2),
(8,'Spring','spring',5,2),
(9,'Hibernate','hibernate',5,2),
(10,'Webservices','#',0,3),
(11,'REST','rest',10,3),
(12,'SOAP','soap',10,3),
(13,'Contact','contact',0,4),
(14,'About','about',0,5);

Fetching Data from Table

Now fetch the data from the table using the following query. I am fetching columns category_id, category_name, category_link, parent_id and sort_order to construct the infinite multi level nested category using PHP and MySQL.

SELECT
  `category_id`,
  `category_name`,
  `category_link`,
  `parent_id`,
  `sort_order`
FROM `roytuts`.`category`
ORDER BY parent_id, sort_order, category_name;

Here is the final result after executing the above query:

infinite multi level nested category using php mysql

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.

$dbConn = mysqli_connect('localhost', 'root', 'root', 'roytuts') or die('MySQL connect failed. ' . mysqli_connect_error());

//select all rows from the category table
$result = mysqli_query($dbConn, "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 by hitting the URL http://localhost/php-mysql-infinite-level-nested-category/php-mysql-infinite-level-nested-category.php in the browser:

Hope, you got the idea how to build a infinite multi level nested category and subcategory.

Source Code

Download

Thanks for reading.

Tags:

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 *