TalkPHP - Categories and sub categories

Hi.

Im new here, so hello to eveybody :)

Im at my wits end. I can make a perfectly working recursion script that output my categories and sub categories with no problem.

But making the categories be outputted in an un-ordered list (CSS) doesn't work.

DATABASE

Code:

CREATE TABLE `categories` (

  `id` int(11) NOT NULL auto_increment,

  `sortorder` int(11) NOT NULL,

  `status` enum('1','0') NOT NULL,

  `type` int(11) NOT NULL,

  `parent` int(11) NOT NULL default '0',

  `picture` varchar(150) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=106 DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



CREATE TABLE `categories_description` (

  `id` int(11) NOT NULL auto_increment,

  `category_id` int(11) NOT NULL,

  `country_id` int(11) NOT NULL,

  `status` enum('1','0') NOT NULL,

  `name` varchar(70) NOT NULL,

  `description` text NOT NULL,

  `seo_title` text NOT NULL,

  `seo_description` text NOT NULL,

  `seo_keywords` text NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=106 DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The structure basically uses:

ID : Unique identification
PARENT : Referer to the parent category

The rest is just various data that I use for other things.

Recursion function

PHP Code:


		
			
class Categories {

    public function categories_tree($country_id='',$parent='0',$spacer='') {

        $db = new Db();

        $sql = $db->query("SELECT c.*,cd.name FROM categories AS c,categories_description AS cd WHERE c.id=cd.category_id AND c.parent='$parent' AND cd.country_id='$country_id' ORDER BY c.parent,c.type,c.sortorder");

        $num = $db->count_rows($sql);

        $spacer .= '- ';

        while($row = $db->fetchArray($sql)) {

            if($num==0 || $row['parent']=='0') {

                $spacer = '';

            }

            echo $spacer.'<a href="productlisting.php?cid='.$row['id'].'">'.$row['name'].'</a><br />';

            $this->categories_tree($country_id,$row['id'],$spacer);

        }

    }

}

All this will output e.g.:

Category 1
- Sub Category 2
- Sub Category 3
- - Sub Category 4
Category 5
- Sub Category 6

What I need is:

HTML Code:

<ul id="categories" class="treeview">

        <li><span>Category 1</span>

                <ul>

                        <li><span>Sub Category 2</span></li>

                        <li><span>Sub Category 3</span>

                                <ul>

                                        <li><span>Sub Category 4</span></li>

                                </ul>

                        </li>

                </ul>

        </li>

        <li><span>Category 5</span>

                <ul>

                        <li><span>Sub Category 6</span></li>

                </ul>

        </li>

</ul>

This code will not work.

Because the ending </li> and </ul> will be missing the right spots.

PHP Code:


		
			
class Categories {

    public function categories_tree($country_id='',$parent='0',$spacer='') {

        $db = new Db();

        $sql = $db->query("SELECT c.*,cd.name FROM categories AS c,categories_description AS cd WHERE c.id=cd.category_id AND c.parent='$parent' AND cd.country_id='$country_id' ORDER BY c.parent,c.type,c.sortorder");

        $num = $db->count_rows($sql);

        $spacer .= '- ';

        while($row = $db->fetchArray($sql)) {

            if($num==0 || $row['parent']=='0') {

                $spacer = '';

            }

            echo '<ul><li><span>'.$spacer.'<a href="productlisting.php?cid='.$row['id'].'">'.$row['name'].'</a></span>';

            //echo $spacer.'<a href="productlisting.php?cid='.$row['id'].'">'.$row['name'].'</a><br />';

            $this->categories_tree($country_id,$row['id'],$spacer);

            echo '</li></ul>';

        }

    }

}