This tutorial shows how to find the entire path from root to child for nested category and subcategory. We have a tables in such a way it contains unlimited level of categories and we have another column in the same table which holds the entire path for the nested categories.
Create a table

CREATE TABLE `cat` (
  `cat_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(50) DEFAULT NULL,
  `p_id` int(11) DEFAULT '0',
  `cat_path` tinytext,
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

 

Dump some data into the table

insert  into `cat`(`cat_id`,`cat_name`,`p_id`,`cat_path`)
values (1,'Java',0,NULL),
    (2,'PHP',0,NULL),
    (3,'Frameworks',0,NULL),
    (4,'JSF',3,NULL),
    (5,'Struts',3,NULL),
    (6,'Spring',3,NULL),
    (7,'Hibernate',3,NULL),
    (8,'Webservices',0,NULL),
    (9,'REST',8,NULL),
    (10,'SOAP',8,NULL),
    (11,'Database',0,NULL),
    (12,'MySQL',11,NULL),
    (13,'Oracle',11,NULL),
    (14,'Tutorials',0,NULL),
    (15,'Scripts',14,NULL),
    (16,'JavaScript',15,NULL),
    (17,'JQuery',15,NULL);

 

Create procedure which will update the field cat_path using the category path

DELIMITER $$
USE `cdcol`$$
DROP PROCEDURE IF EXISTS `nestedPath`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `nestedPath`()
BEGIN
    DECLARE path_count, p_count INT;
    SELECT COUNT(*) INTO p_count FROM cat WHERE p_id = 0;
    UPDATE cat SET cat_path = NULL;
    UPDATE cat a, cat b SET a.cat_path = b.cat_name WHERE b.p_id = 0 AND a.p_id = b.cat_id;
    SELECT COUNT(*) INTO path_count FROM cat WHERE cat_path IS NULL;
    WHILE path_count > p_count DO
        UPDATE cat a, cat b SET a.cat_path = CONCAT(b.cat_path, '->', b.cat_name)
        WHERE b.cat_path IS NOT NULL AND a.p_id = b.cat_id;
        SELECT COUNT(*) INTO path_count FROM cat WHERE cat_path IS NULL;
    END WHILE;
    END$$
DELIMITER ;

 

Usage Example

CALL nestedPath
Select * FROM cat

 

Output

find path in hierarchical categories

That’s all. Thanks for your reading. Please do not forget to leave a comment.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

Leave a Reply

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