Mysql query that returns a category tree

Question

I am working on category management. Where I have parentId with each line. Categories can be n-tiers. I can recursively call the php function, which will return an array of the tree tree.

Now the problem: in the admin panel, I need a page with a list of categories, as shown below. that is, which category is under which.

• home
• Products (Change) (Delete)
• Products> Product 1 (Change) (Delete)
• Products> Product 2 (Change) (Delete)
• Products> Product 2> Product 2 1 (Change) (Delete)
• Products> Product 2> Product 2 2 (Change) (Delete)
• Contact us (Change) (Delete)

I want to get the mysql query result in the same order as shown above. I am not sure how I can achieve this.

SELECT * FROM tbl_categories ORDER BY ??? 

Please guide.

Answer 1

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

There are two ways to solve the problem:

• Use the external logic of the application (outside the database) to make recursive calls that will open the children of each category and build a tree in the application.

• Use one of the algorithms to store tree data in a relational database. One of these algorithms is called Modified Preorder Tree Traversal or simply MPTT.

Assuming we use the lft and rgt to maintain indexes on the left and right when crawling, when you insert a new category, you need:

• Get information about the parent category by identifier: SELECT lft,rgt FROM tbl_categories WHERE categoryId=5 Assume, as an example, the parent category had lft=7 and rgt=10 (in this case, she already has one child)

• Make room for a new entry - shift all entries to 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 in descending order. Since lft and rgt must be unique, it is recommended to set the UNIQUE restriction for them, and then, in order to prevent duplication of key errors, a descending order is required when updating.

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

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

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

Answer 2

you can use a stored function that will recursively extract 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 with

 select getcategory(category_id); 

Guerre in this function I did a sanity check to avoid an endless cycle.

Answer 3

You cannot achieve this in a single mysql query. Well, you can achieve this by doing a few queries. The algorithm is as follows: First, create a dataset object that you populate by retrieving data from the database. Create a method that takes a parent identifier as a parameter and returns its child nodes, if present, and returns -1 if it does not have a child. Step 1: select all rows that do not have a parent (root) node. Step 2: Iterate through this result. For example, if prod1 and prod2 are the initial returned nodes in the result set. Iterating over this RS, we get prod1, and we insert a row into our DataSET obj. Then we send id prod1 to the getCHILD method to get its child, and then again repeat the returned result set and call the getCHILD method again until we get the lowest node.