Retrieve hierarchy from a table like this

[Nordy]

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? The problem here is there are no fixed levels.

Here are the table creates to make things easier for you.

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);

[guido2004]

Please read this: http://sqllessons.com/categories.html
Especially the part about 'The path to the root: the breadcrumb trail'

[Nordy]

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.

[guido2004]

No max number of levels?

[Nordy]

The maxLevel you see there is for something else. Even if there were max levels, why get all nulls out?

[guido2004]

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.