Multiple Level Categories

discostudio

Ok here goes,

I have a category table with the following columns:

cat_id
parent_id
cat_name

As you can see multiple level cateories.

ok so what I need to is extract the categories from the database in alphabetical order so imaging I have categories as follows:

Accessories
Accessories :: Parts
Accessories :: Parts :: PCB's
Accessories :: Parts :: Plugs
Accessories :: Leads
Amps & Speakers
Amps & Speakers :: Amplifiers
Amps & Speakers :: Amplifiers :: 4 Ohms
Amps & Speakers :: Amplifiers :: 8 Ohms

How would I extract them from the database in perfect alphabetical order as shown above.

Here's the code I am working with:

<SELECT NAME="cat_id">
<?
$sql = mysql_query("SELECT * FROM `categories` WHERE `parent_id` = '0'
ORDER BY `cat_name`; ");
while ($r = mysql_fetch_array($sql)) {
?>
<OPTION VALUE"<?php echo $r['cat_id']; ?>"><?php echo $r['cat_name']; ?></OPTION>
<?php } ?>
</SELECT>

So that would only give me the following:

Accessories
Amps & Speakers

What do I do inside that loop or what kind of table join should I use, bearing in mind that the levels could be infinate, and I need them in alphabetical order as I will have over 400 categories.

Can anyone help please?

discostudio

Anybody? - There must be a solution.

discostudio

... I have been on this for weeks, I know there are loads of real experts on here, someone must have a clue?

thorpe

IMO a better method of implimenting catigories / sub catigories is to use nested sets. there is a fairly in depth tutorial here.

discostudio

I have found the following on another forum and can see how this works when there is only one level.

SELECT child.id AS childid, child.parent_id AS childpid, child.name
 AS cname, parent.name AS pname, parent.id AS pid, 
parent.parent_id AS parentid FROM categories parent LEFT JOIN 
categories child on parent.id=child.parent_id

+------------------+------------------+
| pname            | cname            |
+------------------+------------------+
| 4x4              | NULL             |
| ATV              | atv new          |
| ATV              | ATV Parts & Acc  |
| ATV              | Sport ATVS       |
| atv new          | NULL             |
| ATV Parts & Acc  | NULL             |
| Fietsen          | NULL             |
| General Products | NULL             |
| Jipes            | Land Rover       |
| Jipes            | Mitsubishi       |
| Land Rover       | NULL             |
| Misc.            | NULL             |
| Mitsubishi       | NULL             |
| Other            | 4x4              |
| Other            | General Products |
| Other            | Misc.            |
| Sport ATVS       | NULL             |
| Sportbike Parts  | NULL             |
| Sportbikes       | Fietsen          |
| Sportbikes       | Sportbike Parts  |
| Sportbikes       | test             |
| Stuff            | NULL             |
| test             | NULL             |
+------------------+------------------+

discostudio

Thing is, with mine there could be an infinate number of category levels...

discostudio

function getCatDir($catName, $cat_father_id, $catId, $link=FALSE){

global $sessId, $db, $config, $glob;

// get category array for cat dir
$query = "SELECT cat_id, cat_name, cat_father_id FROM ".$glob['dbprefix']."CubeCart_category ORDER BY cat_id DESC";
$catArray = $db->select($query);

$dirArray[0] = $config['dirSymbol'].$catName;

for ($i=0; $i<=count($catArray); $i++) {

	if(isset($catArray[$i]['cat_id']) && $catArray[$i]['cat_id']==$cat_father_id) {

		if($link == TRUE){

			$dirArray[$i+1] = $config['dirSymbol']."<a href='".$GLOBALS['rootRel']."index.php?ccUser=".$sessId."&amp;catId=".$catArray[$i]['cat_id']."&amp;act=viewCat' class='txtLocation'>".$catArray[$i]['cat_name']."</a>";

		} else {

		$dirArray[$i+1]= ">>".$catArray[$i]['cat_name'];

		}

		$cat_father_id = $catArray[$i]['cat_father_id'];

	}

}

krsort($dirArray);
reset($dirArray);

$dir = "";

foreach($dirArray as $key => $value){
 	$dir.= $value;
}

return $dir;
}

That is how Cube Cart does it, but they can only order by the subcategory, cos that function finds the parents of any category.

This, I suppose is not the best way to do it, neither does it do what I want to, but I'm just finding different approaches.