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

Could you add a dump of your categories table data?

Leven
– Leven

2012-04-18 19:09:12 +00:00
Commented Apr 18, 2012 at 19:09 — Leven
– Leven, Commented Apr 18, 2012 at 19:09

Vaishu · Accepted Answer · 2012-04-20 10:31:05Z

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>

Leven · Accepted Answer · 2012-04-20 08:56:02Z

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

Collectives™ on Stack Overflow

Categories with sub PHP / MYSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related