MySQL查询返回类别树 [英] Mysql query which returns category tree

问题描述

我正在从事类别管理.我在每行都有parentId的地方.类别可以是n级. 我可以递归调用php函数，该函数将返回类别树数组.

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.

• 首页
• 产品(编辑)(删除)
• 产品>产品1(编辑)(删除)
• 产品>产品2(编辑)(删除)
• 产品>产品2>产品2 1(编辑)(删除)
• 产品>产品2>产品2 2(编辑)(删除)
• 联系我们(编辑)(删除)

我希望mysql查询结果的顺序与上述相同.我不确定如何实现它.

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

请指导.

推荐答案

您可以使用存储的函数，该函数将递归地获取根路径并与父级连接.

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 ;

，然后使用

select getcategory(category_id);

在此功能中，我进行了健全性检查，以避免无限循环.

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

这篇关于MySQL查询返回类别树的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！