displaying a category total subcategories

Hi

I want to know how to display the total subcategories for a category like this

category1 (total x subcats)
category2 (total x subcats)
etc....

to elaborate more see how they did it here in devscripts
http://www.devscripts.com/topicview.php?tId=1

they show a list of avaialable scripts category and in () the total of the scripts correspponding to that category.

Any idea.

Thanks

Still no ideas

Assuming a category table and a subcategory table, you might try something like the following:

SELECT category.name, count(subcategory.id) where subcategory.category_id=category.id GROUP BY category.name

However your tables are built, a GROUP BY clause is probably going to be the easiest way to do this without having a bunch of nested queries. Check out mysql.com for the usage of GROUP BY, which can be a smidgin tricky.

Thanks dhouston , that worked.

But this select statement shows me only the category which has a correspondent subcategory , but not category which doesn`t have a subcat.

How do I display also these categories with no subcats.

At the moment lets assume the following table structures:

Category table
catid
catname

data in category table
1 windows
2 graphics
3 internet

subcat table
subcatid
subcatname
catid

data in subcat table
1 windows95 1
2 xp 1
3 photoshop 2
4 fireworks 2
5 psp 2

as u can c category 'internet' doesn`t have a subcat , but still i want it to show in the list , how can i modify your select statement where clause to do that.

at the moment it shows:
Graphics 3
Windows 2

I want it to show:
Graphics 3
Windows 2
Internet

Thanks

Ah, maybe instead of a simple WHERE, you should use a LEFT JOIN (or is it a RIGHT JOIN?), which can be used to give you the results not found in the query. Again, check mysql.com for the syntax and the specifics on usage. Basically, you can do a left join where the right half of the join is NULL in order to get the results that don't match the original restriction. Failing that, you may just need to do two queries, one to get categories that do have subcategories and one to get the rest. You could push each type of query onto an array along with the number of subcats for each and sort the array by key prior to display.

That sounds about right, dhouston...

Try this, kupilot:



That should give you the whole list. If you need just one item, just add a WHERE...

Thanks a lot guys , that worked just fine , i was wondering when this Join and Left Join would be usefull

Just a correction the SELECT statement is

SELECT category.name, COUNT(subcat.subid) AS totsub
FROM category
LEFT JOIN subcat ON (category.id = subcat.catid)
GROUP BY category.name