|
Posted by Neeper on 07/31/07 07:18
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)
I'm not very good with complex joins so 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 |
+----------+-------------+--------------------------+
[Back to original message]
|