Retrieve hierarchy from a table like this

Question

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"

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

So for id = 10, the hierarchy would be:

Kitchenware > Knives > Meat Knives > Smear em meat

For id=7 the hierrchy would be:

Kitchenware > Knives > Meat Knives

For id=4 the hierarchy would be

Kitchenware > Knives > Butter Knives > Cut em all

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

You may may want to check this action out http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/ — Mike, May 06 '13 at 04:01
Haha, yeah I will never fully understand what's on that page .... but I use the hell out of it ;) — Mike, May 06 '13 at 04:03
I'd love to see how this could be applied for this question. If anyone can post an answer using `one` query, I will upvote the crap out of it. — Dave Chen, May 06 '13 at 04:08
Honestly this query might become too expensive as your data size increase. Instead, I would suggest you make another Hierarchy table that defines how your hierarchy is like. Thus making your query as easy as something like ***SELECT * FROM Hierarchy WHERE id < "any number"*** — LightYearsBehind, May 06 '13 at 04:09
I doubt this will be more expensive than using multiple queries. Any explanation on why it might be expensive? — Dave Chen, May 06 '13 at 04:11
This is how you can use one query to build an ascending hierarchy http://stackoverflow.com/questions/2441821/getting-all-parent-rows-in-one-sql-query perfect way to build breadcrumbs — Mike, May 06 '13 at 04:27

Meherzad · Accepted Answer · 2013-05-06T05:21:27.357

Try this 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 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//

Query 1:

call updatePath(10)

SQL FIDDLE:

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

Hope this helps

I'm a little new to all this. Why do I get `Table 'tmpTable' already exists: call updatePath(2)` when I try out your fiddle? — Norman, May 06 '13 at 05:11
Forgot to add `drop table tmpTable;` at the end of proc.. Have updated the fiddle and answer. — Meherzad, May 06 '13 at 05:21

score 3 · Answer 2 · answered May 06 '13 at 03:58

Try this:

I would setup a SQL table like this:

CREATE TABLE `table` (
    `id` int(11),
    `name` varchar(32),
    `description` varchar(32),
    `level` int(11),
    `parent` int(11),
    `country` varchar(32),
    `maxLevel` int(11)
);

Here is the test data to insert:

INSERT INTO `table` VALUES
("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");

Finally, here is the code to test:

<?php
$mysqli=new Mysqli("127.0.0.1","root","DATABASE_PASSWORD","DATABASE_NAME");

function getHierarchy($id) {
    global $mysqli;

    $final="";

    $query="SELECT `name`,`parent` FROM `table` WHERE `id`=";
    $result=$mysqli->query($query.$id);
    $result=$result->fetch_assoc();

    while($result['parent']!=0) {
        $result=$mysqli->query($query.$id);
        $result=$result->fetch_assoc();
        $id=$result['parent'];
        $final=$result["name"]." > ".$final;
    }

    $final=substr($final,0,-3);
    return $final;
}

echo getHierarchy(10)."<br>\n";
echo getHierarchy(7)."<br>\n";
echo getHierarchy(4)."<br>\n";

$mysqli->close();
?>

Prints out:

Kitchenware > Knives > Meat Knives > Smear em meat
Kitchenware > Knives > Meat Knives
Kitchenware > Knives > Butter Knives > Cut em all

Retrieve hierarchy from a table like this

2 Answers2

SQL FIDDLE:

Linked