SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retrieve hierarchy from a table like this

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


    Code MySQL:
     "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:
    Code:
        Kitchenware > Knives > Meat Knives > Smear em meat
    For id=7 the hierrchy would be:
    Code:
        Kitchenware > Knives > Meat Knives
    For id=4 the hierarchy would be
    Code:
        Kitchenware > Knives > Butter Knives > Cut em all
    And so on. Any idea how to structure the sql to achieve this? The problem here is there are no fixed levels.

    Here are the table creates to make things easier for you.
    Code SQL:
    DROP TABLE IF EXISTS `products`;
    CREATE TABLE IF NOT EXISTS `products` (
      `id` INT(10) NOT NULL AUTO_INCREMENT,
      `name` VARCHAR(50) DEFAULT NULL,
      `description` VARCHAR(240) DEFAULT NULL,
      `type` tinyint(1) DEFAULT '0',
      `parent` INT(10) DEFAULT '0',
      `country` VARCHAR(2) DEFAULT NULL,
      `maxLevel` INT(1) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
     
    -- Dumping data for table test.products: 2 rows
    /*!40000 ALTER TABLE `products` DISABLE KEYS */;
    INSERT IGNORE INTO `products` (`id`, `name`, `description`, `type`, `parent`, `country`, `maxLevel`) 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);

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Please read this: http://sqllessons.com/categories.html
    Especially the part about 'The path to the root: the breadcrumb trail'

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I went through all of those and more. But thing here is, the levels are not fixed. I could have used something like the one on the page you pointed me to, if I knew for sure how many levels were there.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    No max number of levels?

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2007
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The maxLevel you see there is for something else. Even if there were max levels, why get all nulls out?

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Nordy View Post
    why get all nulls out?
    Because that way you can use a fixed query. You just handle the NULL values in your PHP script.
    Another possibility is to create the query dynamically, adding the number of needed joins, but to do so you need to know the node's level.

    Or you could load the entire table in an array in PHP, and write a recursive function to find the path from the root to the node.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •