[SOLVED] Category count listing

[hannibal]

Hi,

 

Seen this so many times, but not sure exactly how it is done, and what is the right/wrong way:

 

For example:

 

CAT NAME (No.Of Items with that Category_id)

 

Category 1 (2)

Category 2 (12)

Category 3 (4)

Category 4 (168)

Category 5 (19)

 

How would one count the number of items within a category?

 

SELECT Cat_Name,Cat_QTY FROM CAT_TABLE

 

Where Cat_QTY is the number of items in ITEM_TABLE with category_id='x'.

 

Method 1:

When a new item is added or removed, the Cat_QTY is updated accordingly?

 

So, I am adding an Item, I get the Cat_QTY value and increment it by one and then update hte CAT_TABLE. Seems long winded.

 

Method 2:

Or would there be a script running in the background to determine the number if items in each category?

It would count all items in the item table with category_id='1' and then updating the category table accordingly for each category_id.

 

---------

 

I have tried search for this answer but I don't know what to search for "Summary table count"? The results do not bering back any relating info.

 

Thanks in advance,

Jamie.

 

 

[Barand]

SELECT c.cat_name, COUNT(*) as items

FROM cat_table c INNER JOIN item_table i ON c.category_id = i.category_id

GROUP BY cat_name

[hannibal]

Thank you for the prompt reply.

 

If you have 100 different categories, would that be ok in terms of processing required? Would it not be to intensive for the page loading, as well as other request made? I am not sure what counts as an intensive ODBC request.

 

Thanks agian.

[Barand]

Shouldn't cause a load problem. Forget your methods 1 and 2 with cat_count fields and summary tables. Just run that query when you need to display the counts

[hannibal]

Ok, we'll see how I get on.

 

Thanks for your replies.

 

Resolved.

 

Jamie.