Категории с дополнительным PHP / MYSQL

У меня есть некоторые проблемы с подкатегориями в моем коде.

Мои ожидания:

• пекарня
  • Консервированные продукты
  • молочные продукты
  • Мясо
    • подкатегория
    • подкатегория
    • …
    • подкатегория
  • Конфеты и закуски
    • подкатегория
    • подкатегория
    • …
    • подкатегория

Схема таблицы MYSQL:

Категории:

id категории имя тип URL

$res = mysql_query("SELECT `id`, `name`, `url` FROM `categories` WHERE `type`='category' ORDER BY `name` ASC") or die(mysql_error()); while ($arr = mysql_fetch_array($res)) { $faq_categ[$arr['id']]['title'] = $arr['name']; $faq_categ[$arr['id']]['url'] = $arr['url']; } $res = mysql_query("SELECT `id`, `name`, `category`, `url` FROM `categories` WHERE `type`='subcategory' ORDER BY `name` ASC") or die(mysql_error()); while ($arr = mysql_fetch_array($res)) { $faq_categ[$arr['category']]['items'][$arr['id']]['name'] = $arr['name']; $faq_categ[$arr['category']]['items'][$arr['id']]['url'] = $arr['url']; } if (isset($faq_categ)) { foreach ($faq_categ as $id => $temp) { $textbuilder .= '<li><a href="/products/'.$faq_categ[$id]['url'].'/all" title="">'.$faq_categ[$id]['title'].'</a>'; if (array_key_exists("items", $faq_categ[$id])) { foreach ($faq_categ[$id]['items'] as $id2 => $temp) { $textbuilder .= '<small><a href="/products/'.$faq_categ[$id]['url'].'/'.$faq_categ[$id]['items'][$id2]['url'].'" title="">٠'.$faq_categ[$id]['items'][$id2]['name'].'</a></small>'; } } $textbuilder .= '</li>'; } } 

Результат: http://www.picupload.us/images/454result.png

Спасибо за ваше время, Фокс Санк

РЕДАКТИРОВАТЬ:

Вот таблица

 CREATE TABLE IF NOT EXISTS `categories` ( `id` int(10) NOT NULL auto_increment, `category` int(10) default NULL, `name` varchar(255) NOT NULL, `url` varchar(255) NOT NULL, `type` varchar(255) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ; INSERT INTO `categories` (`id`, `category`, `name`, `url`, `type`) VALUES (1, 1, 'Bakery', 'brakery', 'category'), (2, 2, 'Dairy Products', 'dairy-products', 'category'), (3, 3, 'Sweets and Snacks', 'sweets-and-snacks', 'category'), (4, 3, 'Corn puffs', 'corn-puffs', 'subcategory'), (5, 3, 'Biscuits', 'biscuits', 'subcategory'), (6, 3, 'Cakes', 'cakes', 'subcategory'), (7, 3, 'Pretzels', 'pretzels', 'subcategory'), (8, 4, 'Canned food', 'canned-food', 'category'), (9, 5, 'Meat', 'meat', 'category'), (10, 5, 'Salami', 'salami', 'subcategory'), (11, 5, 'Sausages', 'sausages', 'subcategory'), (12, 5, 'Ham', 'ham', 'subcategory'), (13, 5, 'Delicatessen', 'delicatessen', 'subcategory'), (14, 5, 'Frankfurters', 'frankfurters', 'subcategory'), (15, 5, 'Polony', 'polony', 'subcategory'), (16, 5, 'Smoked', 'smoked', 'subcategory'), (17, 5, 'Pate', 'pate', 'subcategory'); 

Related of "Категории с дополнительным PHP / MYSQL"

 CREATE TABLE IF NOT EXISTS `categorylist` ( `id` int(5) NOT NULL auto_increment, `cname` varchar(25) collate utf8_unicode_ci default NULL, `pid` int(5) NOT NULL, PRIMARY KEY (`id`), KEY `pid` (`pid`) ) ; INSERT INTO `categorylist` (`id`, `cname`, `pid`) VALUES (1, 'Entertainment', 0), (2, 'movies', 1), (3, 'actor', 2), (4, 'actress', 2), (5, 'Drama', 1), (7, 'sports', 0), (8, 'comedian', 2), (9, 'political', 0); <?php include "header.php"; include "dbconn.php"; $qry="SELECT * FROM categorylist"; $result=mysql_query($qry); $arrayCategories = array(); while($row = mysql_fetch_assoc($result)){ $arrayCategories[$row['id']] = array("pid" => $row['pid'], "name" => $row['cname']); } //createTree($arrayCategories, 0); function createTree($array, $currentParent, $currLevel = 0, $prevLevel = -1) { foreach ($array as $categoryId => $category) { if ($currentParent == $category['pid']) { if ($currLevel > $prevLevel) echo " <ul> "; if ($currLevel == $prevLevel) echo " </li> "; echo '<li id="'.$categoryId.'" onclick=child(this.id);><span>'.$category['name'].'</span>'; if ($currLevel > $prevLevel) { $prevLevel = $currLevel; } $currLevel++; createTree ($array, $categoryId, $currLevel, $prevLevel); $currLevel--; } } if ($currLevel == $prevLevel) echo " </li> </ul> "; } ?> <div id="content" class="general-style1"> <?php if(mysql_num_rows($result)!=0) { ?> <ul> <li id="0" class="root"><span>Categories</span> <?php createTree($arrayCategories, 0); ?> </li> </ul> <?php } ?> </div> 

 (9, 5, 'Meat', 'meat', 'category'), 

 (9, 9, 'Meat', 'meat', 'category'), 

 (10, 5, 'Salami', 'salami', 'subcategory'), (11, 5, 'Sausages', 'sausages', 'subcategory'), (12, 5, 'Ham', 'ham', 'subcategory'), (13, 5, 'Delicatessen', 'delicatessen', 'subcategory'), (14, 5, 'Frankfurters', 'frankfurters', 'subcategory'), (15, 5, 'Polony', 'polony', 'subcategory'), (16, 5, 'Smoked', 'smoked', 'subcategory'), (17, 5, 'Pate', 'pate', 'subcategory'); 

 (10, 9, 'Salami', 'salami', 'subcategory'), (11, 9, 'Sausages', 'sausages', 'subcategory'), (12, 9, 'Ham', 'ham', 'subcategory'), (13, 9, 'Delicatessen', 'delicatessen', 'subcategory'), (14, 9, 'Frankfurters', 'frankfurters', 'subcategory'), (15, 9, 'Polony', 'polony', 'subcategory'), (16, 9, 'Smoked', 'smoked', 'subcategory'), (17, 9, 'Pate', 'pate', 'subcategory');