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