Introduction

This tutorial shows how to find the entire path from root to child for nested categories and subcategories. We have a table in database 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. So we will update the column using Stored Procedure with the entire path.

Prerequisites

MySQL 5.6

Creating Table

Let’s say we have a table cat in MySQL database with the following structure.

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;

Dumping Data

We will dump some data so that we can test our code.

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

Creating Stored Procedure

We have null values for all rows in the column cat_path of the cat table.

Now below Stored Procedure will build the nested category path from root to child category and update the column cat_path in the table.

DELIMITER $$
USE `roytuts`$$
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 ;

Testing the Procedure

Execute below two statements separately one by one in the same order as written below.

Execute the Stored Procedure.

CALL nestedPath

Now select from table cat.

Select * FROM cat

Output

You will see below output. Note I have used Sqlyog to execute SQL statements.

find path in hierarchical categories

That’s all. Thanks for reading.

Tags:

Leave a Reply

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