title: Categories with sub PHP / MYSQL
body: <p>I have some troubles with subcategories in my code.</p>
  
  <p>My expectation:</p>
  
  <blockquote>
    <ul>
    <li>Bakery
    <ul>
    <li>Canned food</li>
    <li>Dairy Products</li>
    <li>Meat
    <ul>
    <li>subcategory</li>
    <li>subcategory</li>
    <li>...</li>
    <li>subcategory</li>
    </ul></li>
    <li>Sweets and Snacks
    <ul>
    <li>subcategory</li>
    <li>subcategory</li>
    <li>...</li>
    <li>subcategory</li>
    </ul></li>
    </ul></li>
    </ul>
  </blockquote>
  
  <p>MYSQL Table schema:</p>
  
  <p>Categories:</p>
  
  <p>id
  category
  name
  url
  type</p>
  
  <pre><code>$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 =&gt; $temp) 
      {
          $textbuilder .= '&lt;li&gt;&lt;a href="/products/'.$faq_categ[$id]['url'].'/all" title=""&gt;'.$faq_categ[$id]['title'].'&lt;/a&gt;';
          if (array_key_exists("items", $faq_categ[$id])) 
          {
              foreach ($faq_categ[$id]['items'] as $id2 =&gt; $temp)
              {
                  $textbuilder .= '&lt;small&gt;&lt;a href="/products/'.$faq_categ[$id]['url'].'/'.$faq_categ[$id]['items'][$id2]['url'].'" title=""&gt;٠'.$faq_categ[$id]['items'][$id2]['name'].'&lt;/a&gt;&lt;/small&gt;';
              }
          }
          $textbuilder .= '&lt;/li&gt;';
      }
  }
  </code></pre>
  
  <p>The result: <a href="http://www.picupload.us/images/454result.png" rel="nofollow">http://www.picupload.us/images/454result.png</a></p>
  
  <p>Thanks for your time,
  Fox Sank</p>
  
  <p>EDIT:</p>
  
  <p>Here is the table</p>
  
  <pre><code>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');
  </code></pre>