Posted by gosha bine on 07/31/07 08:34

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

[Back to original message]