php - Retrieve hierarchy from a table like this -

i'm using mysql php , have data in table 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" 

from this, if had eg: id = 10, how sql display hierarchy item?

so id = 10, hierarchy be:

kitchenware > knives > meat knives > smear em meat 

for id=7 hierrchy be:

kitchenware > knives > meat knives 

for id=4 hierarchy be

kitchenware > knives > butter knives > cut em 

and on. idea how structure sql achieve this?

try stored procedure

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 'path' tbl id = itemid;     select parent cnt tmptable;      while cnt <> 0        update tmptable tt, tbl t set tt.parent = t.parent,                tt.path = concat(t.name, ' > ', tt.path)        tt.parent = t.id;        select parent cnt tmptable;     end while;     select * tmptable;     drop table tmptable; end// 

query 1:

call updatepath(10) 

sql fiddle:

| id |            name | parent |                                                       path | ---------------------------------------------------------------------------------------------- | 10 | "smear em meat" |      0 | "kitchenware" > "knives" > "meat knives" > "smear em meat" | 

hope helps