Infinite dynamic Multi-level nested category with PHP and MySQL

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


Create MySQL table

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

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 fr 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

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`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

we have created table 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 (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);

The actual data in the category table is shown in the below image:

infinite multi level nested category

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.

SELECT
  `category_id`,
  `category_name`,
  `category_link`,
  `parent_id`,
  `sort_order`
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

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.

Display the result using the following example

echo buildCategory(0, $category);

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.

Soumitra Roy Sarkar

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials Twitter Facebook  Google Plus Linkedin Or Email Me

6 Replies to “Infinite dynamic Multi-level nested category with PHP and MySQL”

  1. 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 *