Recursive tree with css

[Dinky]

Hi.

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

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=InnoDB 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=InnoDB 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

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:

<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.

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