Nested categories

[robcrozier]

Hi everyone, just wondering if anyone can point me in the right direction with this one.  Basically what i want to do is create a store with multiple (unlimited) subcategories.  At the minute i have a DB table that stores all details about each category including it;s ID and parents ID.

 

What i want to do is print the categories out in a nice nested list - subcategories being nested below their parent categories.

 

What's the best way to go about this other than recursively querying the database?

 

Cheers!

[premiso]

<?php
create table categories (
catid INT(11) NOT NULL auto_increment,
parentid INT(11) NOT NULL default '0',
catname varchar(50) NOT NULL,
catseoname varchar(50) NOT NULL,
disporder INT(3) NOT NULL default '0',
primary key(catid)
);
*/

mysql_connect("localhost", "root", "");
mysql_select_db("cat");

/*
my test data
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('0', 'Test1', 'test1', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('1', 'Test1-1', 'test1-1', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('1', 'Test1-2', 'test1-2', '1');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('5', 'Test2-1', 'test2-1', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('0', 'Test2', 'test2', '1');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('5', 'Test2-3', 'test2-2', '2');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('6', 'Test2-2-2', 'test2-2-2', '1');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('6', 'Test2-2-1', 'test2-2-1', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('5', 'Test2-3', 'test2-2', '2');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('7', 'Test2-2-2-1', 'test2-2-2-1', '1');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('7', 'Test2-2-2-2', 'test2-2-2-2', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('7', 'Test2-2-2-3', 'test2-2-2-3', '0');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('3', 'Test1-2-1', 'test1-2-1', '1');
insert into categories (`parentid`, `catname`, `catseoname`, `disporder`) VALUES ('3', 'Test1-2-2', 'test1-2-2', '0');

*/

function retrieveCategoryList($parentid=false) {
if ($parentid === false) {
	$query = mysql_query("SELECT catid, parentid, catname, catseoname, disporder FROM categories WHERE parentid = 0 ORDER BY disporder, catname");
}else {
	$query = mysql_query("SELECT catid, parentid, catname, catseoname, disporder FROM categories WHERE parentid = " . $parentid . " ORDER BY disporder, catname");
}
$numRows = mysql_num_rows($query);

$catArray = "none";
if ($numRows > 0) {
	$catArray=array();
	while ($row = mysql_fetch_assoc($query)) {
		$catArray[$row['catname']]['id'] = $row['catid'];
		$catArray[$row['catname']]['parentid'] = $row['parentid'];
		$catArray[$row['catname']]['catseoname'] = $row['catseoname'];
		$catArray[$row['catname']]['catname'] = $row['catname'];
		$catArray[$row['catname']]['disporder'] = $row['disporder'];
		$catArray[$row['catname']]['subcats'] = retrieveCategoryList($row['catid']);
	}
}

return $catArray;
}

function displayCats($cat=false, $charDisp=0) {
if (!$cat) {
	if ($charDisp != 0) {
		$cats = retrieveCategoryList($charDisp);
	}else {
		$cats = retrieveCategoryList();
	}
	foreach ($cats as $cat) {
		displayCats($cat);
	}
}else {
	echo str_repeat("_", $charDisp) . $cat['catname'] . "<br />";
	if ($cat['subcats'] != "none") {
		foreach ($cat['subcats'] as $value) {
			displayCats($value, $charDisp+1);
		}
	}
}
}

displayCats(false, 6);

echo "<pre>";
// Lets just pull out a set of categories.
print_r(retrieveCategoryList());
?>

 

You would want to look up on recursion, but there is an example. The above returns an array for you to use how you want to.