Categories with sub PHP / MYSQL

Question

I have some troubles with subcategories in my code.

My expectation:

• Bakery
  • Canned food
  • Dairy Products
  • Meat
    • subcategory
    • subcategory
    • ...
    • subcategory
  • Sweets and Snacks
    • subcategory
    • subcategory
    • ...
    • subcategory

MYSQL Table schema:

Categories:

id category name url type

$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>';
    }
}

The result: http://www.picupload.us/images/454result.png

Thanks for your time, Fox Sank

EDIT:

Here is the table

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

Answer 1

I try this code, its works.. you will try this...

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>

Answer 2

Your data is wrong:

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

Should be:

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

And

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

Should be:

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