Re: Help with counting total items using hierarchical data structure
- From: gosha bine <stereofrog@xxxxxxxxx>
- Date: Tue, 31 Jul 2007 10:34:28 +0200
On 31.07.2007 09:18 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)
Getting the count a subitems is trivial with nested sets:
count of subnodes for some node = (rgt - lft) div 2
The problem is that "items" are not part of the tree (may I ask why?)
So, you need to get all categories first that are below the given category and then get items from those categories:
SELECT COUNT(*)
FROM items
INNER JOIN categories
ON items.category_id = categories.category_id
WHERE
categories.lft BETWEEN $CURRENT_LEFT AND $CURRENT_RIGHT
(untested)
BTW, this is a rather sql question than php, you'd probably get better answers in the mysql group.
--
gosha bine
makrell ~ http://www.tagarga.com/blok/makrell
php done right ;) http://code.google.com/p/pihipi
.
- References:
- Prev by Date: Help with counting total items using hierarchical data structure
- Next by Date: Re: PHP in html
- Previous by thread: Help with counting total items using hierarchical data structure
- Index(es):
Relevant Pages
|
