Mysql query which returns category tree

Question

I am working on category management. Where i have parentId with each row. Categories can be n-level. I can recursively call php function which will return category tree array.

Now Issue is: In admin panel i want category listing page as shown below. i.e. which category is under which.

• Home
• Products (Edit) (Delete)
• Products > Product 1 (Edit) (Delete)
• Products > Product 2 (Edit) (Delete)
• Products > Product 2 > Product 2 1 (Edit) (Delete)
• Products > Product 2 > Product 2 2 (Edit) (Delete)
• Contact Us (Edit) (Delete)

I want mysql query result in same order as shown above. I am not sure how can i achieve it.

SELECT *
FROM tbl_categories
ORDER BY ???

Please guide.

Answer 1

There is no single SQL query that can bring You results ordered the way You expect based on this table structure.

There are two ways of solving the issue:

1. Use external application logic (outside of DB) to make recursive calls that will discover children of each category and build the tree in application.

2. Use one of the algorithms for storing tree data in a relational database. One of such algorithms is called Modified Preorder Tree Traversal or simply MPTT.

Assuming we use columns lft and rgt to maintain left / right indexes in traversal, when you insert a new category you will need to:

1. Get parent category info by Id: SELECT lft,rgt FROM tbl_categories WHERE categoryId=5 Lets assume for a matter of example, that the parent category had lft=7 and rgt=10 (in this case it has one child already)

2. Make room for a new entry - shift all records by 2 (1 for lft and 1 for rgt):

   UPDATE tbl_categories SET rgt=rgt+2 WHERE rgt>=10 ORDER BY rgt DESC

   UPDATE tbl_categories SET lft=lft+2 WHERE lft>=10 ORDER BY lft DESC

Note here ORDER descending. As lft and rgt are supposed to be unique, it is advised to make a UNIQUE constraint on them, and then descending order in update is needed to prevent duplicate key errors.

1. Set lft=<former parent rgt> and rgt=<former parent rgt +1> and insert a new record...

   INSERT INTO tbl_categories SET categoryName="New Child",parentCategoryId=5,lft=11,rgt=12,...

You can find more detailed examples with code if you search for MPTT PHP MySQL. There are quite a few tutorials on this subject.

Answer 2

you can use a stored function that will recursively fetch the root path and concatenate with your parent.

DELIMITER $$
DROP FUNCTION IF EXISTS `get_category`$$
CREATE FUNCTION `get_category`(cat_id int) RETURNS VARCHAR(255)
READS SQL DATA
BEGIN
DECLARE c_id INT;
DECLARE p_id INT;
DECLARE count INT;
DECLARE cat_name VARCHAR(255);
DECLARE cat_path VARCHAR(255);
set c_id = cat_id;
SELECT parent_category_id, category_name INTO p_id, cat_name FROM categories WHERE category_id = c_id;
set c_id = p_id;
set cat_path=cat_name;
set count=0;
WHILE (c_id IS NOT NULL) DO 

SELECT parent_category_id, category_name INTO p_id, cat_name FROM categories WHERE category_id = c_id;
set c_id = p_id;
set cat_path = concat_ws('>',cat_name,cat_path);
set count = count + 1;
IF count=10 THEN
  SET c_id = NULL;
END IF;
 END WHILE;
RETURN cat_path;
END
$$
DELIMITER ;

and then call using

select getcategory(category_id);

Herre in this function i have made a sanity check so as to avoid infinite looping..