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:

• Infinite dynamic Multi-level nested category with Codeigniter and MySQL

Prerequisites

MySQL 8.0.17 – 8.0.26, PHP 7.4.3 – 7.4.27, Apache 2.4 (Optional)

Project Setup

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

Now I will create a project root directory called php-mysql-infinite-level-nested-category anywhere in the physical drive of the system or if you are using Apache HTTP server then you can create 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.

MySQL Table

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

CREATE TABLE IF NOT EXISTS `category` (
  `category_id` int UNSIGNED COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `category_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_link` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_id` int UNSIGNED  COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `sort_order` int COLLATE utf8mb4_unicode_ci 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 I 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

Sample Data for MySQL

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:

Build 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 I will 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, I 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, I 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