从这样的表中检索层次结构 [英] Retrieve hierarchy from a table like this

问题描述

我将MySql与PHP结合使用，并将所有数据存储在这样的表中:

I'm using MySql with PHP and have all my data in a table like this:

"id"    "name"         "description"               "level"  "parent"    "country"   "maxLevel"
"1"     "Kitchenware"   "Kitchenware description"   "1"       "0"         "US"        "0"
"2"     "Knives"        "All our knives"            "2"       "1"         "US"        "0"
"3"     "Butter Knives" "All Butter Knives"         "3"       "2"         "US"        "0"
"4"     "Cut em all"    "Cut em all"                "4"       "3"         "US"        "0"
"5"     "Cull em all"   "Cull em all"               "4"       "3"         "US"        "0"
"6"     "Smear em all"  "Smear em all"              "4"       "3"         "US"        "0"
"7"     "Meat Knives"   "All Meat Knives"           "3"       "2"         "US"        "0"
"8"     "Cut em meat"   "Cut em meat"               "4"       "7"         "US"        "0"
"9"     "Cull em meat"  "Cull em meat"              "4"       "7"         "US"        "0"
"10"    "Smear em meat" "Smear em meat"             "4"       "7"         "US"        "0"

由此，如果我有例如:id = 10，sql将如何显示项目的层次结构?

From this, If I had for eg: id = 10, how would the sql be to display the hierarchy for an item?

因此对于id = 10，层次结构将是:

So for id = 10, the hierarchy would be:

Kitchenware > Knives > Meat Knives > Smear em meat

对于id = 7，层次结构为:

For id=7 the hierrchy would be:

Kitchenware > Knives > Meat Knives

对于id = 4，层次结构应为

For id=4 the hierarchy would be

Kitchenware > Knives > Butter Knives > Cut em all

以此类推.知道如何构造sql来实现这一目标吗?

And so on. Any idea how to structure the sql to achieve this?

推荐答案

尝试此存储过程

CREATE PROCEDURE updatePath(in itemId int)
BEGIN
    DECLARE cnt int default 0;
    CREATE temporary table tmpTable 
    (
       `id` int, `name` varchar(15), `parent` int, path varchar(500)  
     )engine=memory select id, name, parent, name AS 'Path' from tbl where id = itemId;
    select parent into cnt from tmpTable;

    while cnt <> 0 do
       Update tmpTable tt, tbl t set tt.parent = t.parent, 
              tt.path = concat(t.name, ' > ', tt.path)
       WHERE tt.parent = t.id;
       select parent into cnt from tmpTable;
    end while;
    select * from tmpTable;
    drop table tmpTable;
END//

查询1 :

call updatePath(10)

SQL字段 :

| ID |            NAME | PARENT |                                                       PATH |
----------------------------------------------------------------------------------------------
| 10 | "Smear em meat" |      0 | "Kitchenware" > "Knives" > "Meat Knives" > "Smear em meat" |

希望这会有所帮助

这篇关于从这样的表中检索层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！