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 how to build a dynamic infinite level of nested categories.

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


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

 

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

we have table so now we need 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);

 

infinite nested categories in php and mysql

Now fetch the result using the following query

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

 

infinite nested categories in php and mysql

The following logic retrieves the results from the category table and puts the results into a multidimensional array.
The $category contains two other arrays, categories holds every result from the category table query, 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.

//category and subcategory builder function, parent 0 is the root

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;
        }

 

Display the result using the following example

echo buildCategory(0, $category);

 

Final output in the browser

infinite nested categories in php and mysql

You got the idea how to build a infinite level of category and subcategory. Now you can apply CSS or customization as per your needs.

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

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 *