php+mysql - categories and subcategories

Adam

hi guys, having some trouble with this, i cant get my ahead around it right now! i'm working with a db which contains a table for courses and a table for course categories.

the courses table contains details on the course etc. and has a field for the course category id.

the categories table is as follows:

catID | parent | name

i'm trying to create a directory that lists the parent categories (the categories that have a value of 0 in the parent field) and counts the number of courses belonging to that parent, including the courses that belong to a category that has the same category as a parent.

for example, a diploma in organic enterprise has a category value of 852. this corresponds to the organic enterprise category in the categories table. now the organic enterprise category has a parent id of 10, which corresponds to Agriculture, Horticulture & Forestry in the same table.

even just describing that seems rather senseless. is this a nightmare database schema or is it just me? overhauling isn't completely out of the picture, but i'd rather not have to...

suggestions?

seamus

Seems to be the time for it, you're looking to represent a tree view in a relational database.

If there are only two levels in the list, this is easy. That is, the list looks like the below:

|-parent1
|	|
|	|---- Subcategory 1
|	|---- Subcategory 2
|
|-parent2
	|
	|---- Subcategory 3
	|---- Subcategory 4

What you simply do is join the table to itself. So for example,

SELECT 
	c1.name as 'Parent Name',
	c1.catid as 'Parent ID',
	c.name as 'Category Name',
	c.catid as 'Category ID',
FROM
	categories c
JOIN
	categories c1
		ON c1.catid = c.parent
WHERE
	c.parent <> 0
ORDER BY 
	c1.catid ASC

This will produce output like this:

Parent Name	|	Parent ID	|	Category Name	|  Category ID
------------------------------------------------------------------------------
parent1		|		1	|	Subcategory 1	|	3		
------------------------------------------------------------------------------
parent1		|		1	|	Subcategory 2	|	4		
------------------------------------------------------------------------------
parent2		|		2	|	Subcategory 3	|	5		
------------------------------------------------------------------------------
parent2		|		2	|	Subcategory 4	|	6

This will of course only work if there are only two levels in your tree.

To get a count of how many subtrees a parent has, you do a left join the opposite way and include a COUNT() and GROUP BY:

SELECT 
	c.name as 'Parent Name',
	c.catid as 'Parent ID',
	count(c1.catid) as 'Num. SubCats',
FROM
	categories c
LEFT JOIN
	categories c1
		ON c.catid = c1.parent
WHERE
	c.parent = 0
GROUP BY 
	c.catid