Help with counting total items using hierarchical data structure

I'm having trouble coming up with a query to count the number of total
items each subcategory with the lowest cost (CPU item).

I'd like to be able to display the total in my pages like so using
PHP.

Automotive (4 items)
- Cars (2 items)
- Trucks (2 items)
- Vans (0 items)
Electronics (6 items)
- Digital Cameras (0 items)
- Portable Audio (3 items)
- Home Theatre & Projectors (0 items)
- TV, DVD & VCR Players (3 items)
- Sound Systems & Components (0 items)


At the moment I am using 4 seperate queries in loops and I know there
must be an more efficient and easier way.

For example, I'd like to count the number of items there are under the
Automotive category such as:

Automotive has 4 items within it (under Cars, Trucks & Vans)
1) 1995 Nissan Pickup
2) 2002 Acura RSX
3) 2005 Honda Civic
4) 2007 Dodge Durango


Also is it better, faster to run the query and store the results to a
seperate table (ie. hourly using cron) and retrieve the total from
that table when needed or is it quick enough to execute that query x
times depending on the number of categories on that page.


These are the 2 tables I have:

Categories table (Using hierarchical data structure)
+--------------+-----+------+------------------------------+
| category_id | lft | rgt | name |
+--------------+-----+------+------------------------------+
| 1 | 1 | 22 | Categories |
| 2 | 2 | 9 | Automotive |
| 3 | 3 | 8 | Cars |
| 4 | 4 | 7 | Trucks |
| 5 | 5 | 6 | Vans |
| 5 | 10 | 21 | Electronics |
| 6 | 11 | 20 | Digital Cameras |
| 7 | 12 | 19 | Portable Audio |
| 8 | 13 | 18 | Home Theatre & Projectors |
| 9 | 14 | 17 | TV, DVD & VCR Players |
| 10 | 15 | 16 | Sound Systems & Components |
+--------------+------+-----+------------------------------+

Items table
+----------+-------------+--------------------------+
| item_id | category_id | title |
+----------+-------------+--------------------------+
| 1 | 4 | 1995 Nissan Pickup |
| 2 | 3 | 2002 Acura RSX |
| 3 | 3 | 2005 Honda Civic |
| 4 | 4 | 2007 Dodge Durango |
| 5 | 7 | iPod Nano |
| 6 | 7 | Creative Zen |
| 7 | 9 | RCA 21" TV |
| 8 | 9 | Sony 36" TV |
| 9 | 9 | Sony 60" Projection TV |
| 10 | 9 | Toshiba 27" TV |
| 11 | 7 | iPod Mini |
+----------+-------------+--------------------------+

Re: Help with counting total items using hierarchical data structure

Neeper wrote:
> I'm having trouble coming up with a query to count the number of total
> items each subcategory with the lowest cost (CPU item).
>
> I'd like to be able to display the total in my pages like so using
> PHP.
>
> Automotive (4 items)
> - Cars (2 items)
> - Trucks (2 items)
> - Vans (0 items)
> Electronics (6 items)
> - Digital Cameras (0 items)
> - Portable Audio (3 items)
> - Home Theatre & Projectors (0 items)
> - TV, DVD & VCR Players (3 items)
> - Sound Systems & Components (0 items)
>
>
> At the moment I am using 4 seperate queries in loops and I know there
> must be an more efficient and easier way.
>
> For example, I'd like to count the number of items there are under the
> Automotive category such as:
>
> Automotive has 4 items within it (under Cars, Trucks & Vans)
> 1) 1995 Nissan Pickup
> 2) 2002 Acura RSX
> 3) 2005 Honda Civic
> 4) 2007 Dodge Durango
>
>
> Also is it better, faster to run the query and store the results to a
> seperate table (ie. hourly using cron) and retrieve the total from
> that table when needed or is it quick enough to execute that query x
> times depending on the number of categories on that page.
>
>
> These are the 2 tables I have:
>
> Categories table (Using hierarchical data structure)
> +--------------+-----+------+------------------------------+
> | category_id | lft | rgt | name |
> +--------------+-----+------+------------------------------+
> | 1 | 1 | 22 | Categories |
> | 2 | 2 | 9 | Automotive |
> | 3 | 3 | 8 | Cars |
> | 4 | 4 | 7 | Trucks |
> | 5 | 5 | 6 | Vans |
> | 5 | 10 | 21 | Electronics |
> | 6 | 11 | 20 | Digital Cameras |
> | 7 | 12 | 19 | Portable Audio |
> | 8 | 13 | 18 | Home Theatre & Projectors |
> | 9 | 14 | 17 | TV, DVD & VCR Players |
> | 10 | 15 | 16 | Sound Systems & Components |
> +--------------+------+-----+------------------------------+
>
> Items table
> +----------+-------------+--------------------------+
> | item_id | category_id | title |
> +----------+-------------+--------------------------+
> | 1 | 4 | 1995 Nissan Pickup |
> | 2 | 3 | 2002 Acura RSX |
> | 3 | 3 | 2005 Honda Civic |
> | 4 | 4 | 2007 Dodge Durango |
> | 5 | 7 | iPod Nano |
> | 6 | 7 | Creative Zen |
> | 7 | 9 | RCA 21" TV |
> | 8 | 9 | Sony 36" TV |
> | 9 | 9 | Sony 60" Projection TV |
> | 10 | 9 | Toshiba 27" TV |
> | 11 | 7 | iPod Mini |
> +----------+-------------+--------------------------+

Untested:

SELECT COUNT(*) FROM categories c
LEFT JOIN items i
ON i.category_id = c.category_id
WHERE c.lft BETWEEN 2 AND 9

Re: Help with counting total items using hierarchical data structure

On 1 Aug, 06:27, Neeper wrote:
> I'm having trouble coming up with a query to count the number oftotalitemseach subcategory with the lowest cost (CPU item).
>
> I'd like to be able to display thetotalin my pages like sousing
> PHP.
>
> Automotive (4items)
> - Cars (2items)
> - Trucks (2items)
> - Vans (0items)
> Electronics (6items)
> - Digital Cameras (0items)
> - Portable Audio (3items)
> - Home Theatre & Projectors (0items)
> - TV, DVD & VCR Players (3items)
> - Sound Systems & Components (0items)
>
> At the moment I amusing4 seperate queries in loops and I know there
> must be an more efficient and easier way.
>
> For example, I'd like to count the number ofitemsthere are under the
> Automotive category such as:
>
> Automotive has 4itemswithin it (under Cars, Trucks & Vans)
> 1) 1995 Nissan Pickup
> 2) 2002 Acura RSX
> 3) 2005 Honda Civic
> 4) 2007 Dodge Durango
>
> Also is it better, faster to run the query and store the results to a
> seperate table (ie. hourlyusingcron) and retrieve thetotalfrom
> that table when needed or is it quick enough to execute that query x
> times depending on the number of categories on that page.
>
> These are the 2 tables I have:
>
> Categories table (Usinghierarchicaldatastructure)
> +--------------+-----+------+------------------------------+
> | category_id | lft | rgt | name |
> +--------------+-----+------+------------------------------+
> | 1 | 1 | 22 | Categories |
> | 2 | 2 | 9 | Automotive |
> | 3 | 3 | 8 | Cars |
> | 4 | 4 | 7 | Trucks |
> | 5 | 5 | 6 | Vans |
> | 5 | 10 | 21 | Electronics |
> | 6 | 11 | 20 | Digital Cameras |
> | 7 | 12 | 19 | Portable Audio |
> | 8 | 13 | 18 | Home Theatre & Projectors |
> | 9 | 14 | 17 | TV, DVD & VCR Players |
> | 10 | 15 | 16 | Sound Systems & Components |
> +--------------+------+-----+------------------------------+
>
> Itemstable
> +----------+-------------+--------------------------+
> | item_id | category_id | title |
> +----------+-------------+--------------------------+
> | 1 | 4 | 1995 Nissan Pickup |
> | 2 | 3 | 2002 Acura RSX |
> | 3 | 3 | 2005 Honda Civic |
> | 4 | 4 | 2007 Dodge Durango |
> | 5 | 7 | iPod Nano |
> | 6 | 7 | Creative Zen |
> | 7 | 9 | RCA 21" TV |
> | 8 | 9 | Sony 36" TV |
> | 9 | 9 | Sony 60" Projection TV |
> | 10 | 9 | Toshiba 27" TV |
> | 11 | 7 | iPod Mini |
> +----------+-------------+--------------------------+

This was answered yesterday in comp.lang.php