I would recommend that you do not keep the Category_Count as a column in
your table. However, you might consider creating a view which dynamically
grabbed the Count() of products for each category.
Example:
CREATE VIEW Category_List AS
SELECT Cat.Category_ID, Cat.Category_Name, Cat.Category_Parent,
(SELECT COUNT(Prod.Product_ID)
FROM Products Prod
WHERE Prod.Category_ID = Cat.Category_ID) As Category_Count
FROM Categories Cat
Then, you might be able to use <cfloop> to iterate through result set to
total the hierarchal data for each child. I don't have the time to hash
this out, but I hope this helps you out ^_^
--
SCOTT VAN VLIET
SENIOR ANALYST
SBC SERVICES, INC
Tel: 858.886.3878
Fax: 858.653.6763
Email: [EMAIL PROTECTED]
-----Original Message-----
From: Jason Davis [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, March 19, 2002 12:20 PM
To: CF-Talk
Subject: Meet my tables
Meet my Products table:
CREATE TABLE [Products] (
[Product_id] [int] NOT NULL ,
[Product_title] [varchar] (200) NULL ,
[Category_id] [int] NOT NULL ,
)
Each product has a category, meet my categories table:
CREATE TABLE [Categories] (
[Category_id] [int] NOT NULL ,
[Category_parent] [int] NULL ,
[Category_title] [varchar] (200) NULL ,
[Category_count] [int] NULL DEFAULT (0)
)
As you can see, each category has a Category_parent
which symbolize the above category (categories are sorted hirarchly).
Can I do some kind of a loop that will run for each Category_id,
and store in Category_count the number of Products available in that
category, and all of the hirearchy categories beneath?
i.e - if I have the following categories:
(1) Printers
(2) Laser (category_parent=1)
(3) Ink (category_parent=1)
and the following products:
HP LaserJet (category_id = 2)
HP Deskjet (category_id = 3)
Canon BJ200D (Category_id = 3)
category count will be:
(1) Printers => 3 (1+2)
(2) Laser => 1
(3) Ink => 2
Help!?
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
