CODEWITHSUNDEEP
phpmysqlsql-order-bycategories
VibhaJ
VibhaJ

Reputation: 2256

Mysql query which returns category tree

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.

enter image description here

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

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.

Upvotes: 6

Views: 4280

Answers (3)

satdev86
satdev86

Reputation: 810

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..

Upvotes: 2

poncha
poncha

Reputation: 7866

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.

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13465

You cannot achieve this in a single mysql query. Well you can achieve this, by executing multiple queries. The algorithm goes like this : Initially create a data set object which you will populate, by fetching data from the data base. Create a method which takes the parent id as parameter and returns its child nodes if present, and returns -1, if it doesnt have a child. Step1: Fetch all the rows, which doesn't have a parent(root) node. Step2: Iterate through this result. For example if prod1 and prod2 are the initial returned nodes, in the resultset. Iterating this RS we get prod1, and we insert a row in our DataSET obj. Then we send the id of prod1 to getCHILD method, to get its child, and then again we iterate the returned resultset, and again call the getCHILD method, till we dont get the lowest node.

Upvotes: 0

Related Questions