How do I get the count of all products in a category and its subcategories (and sub-subcategories)?
The category table looks something like this:
id -- name -- parent_id
1 -- Men -- 0
2 -- Women -- 0
3 -- Shirts -- 1
4 -- Half-sleeve -- 3
5 -- Full-sleeve -- 3
Relationship table:
Product_id -- Category Id
1 -- 2
2 -- 2
3 -- 4 ....
I can easily get the number of products in any category and its closest subcategories. But if there are more than two levels, things get messy.
So my question is how to get the count of all products in Men and its subcategories. Or shirts and its subcategories?
Any ideas, thanks.
UPDATE:
I know there is a Nested Set Model, but I cannot change the structure right now.
source to share
If possible I have checked Hierarchical Data Management in MySQL .
It's hard to get your head up at first, but it makes things easier.
If you cannot do this, you will need to execute a recursive function like:
$prods = 0;
function getProdsInCat($cat)
{
global $prods;
$prods += mysql_result(mysql_query(SELECT COUNT(`Product_id`) FROM `prod_to_cat` WHERE `Category Id` = '".$cat."'),0);
$moreCats = mysql_query("SELECT `cat_id` FROM `cats` WHERE `parent_id` = '".$cat."'");
while($cats = mysql_fetch_assoc($moreCats)
{
getProdsInCat($cats['cat_id']);
}
}
source to share
Assuming you can add an extra column to the category table.
The specified column will have a category path.
id -- name -- parent_id path
1 -- Men -- 0 0/
2 -- Women -- 0 0/
3 -- Shirts -- 1 0/1
4 -- Half-sleeve -- 3 0/1/3
5 -- Full-sleeve -- 3 0/1/3
Thus, finding all subcategories becomes one of the queries:
SELECT id as CatId FROM categories WHERE path LIKE '0/1/%';
And getting an account of all the products within a category and its children is pretty easy too:
SELECT count(p.id) as Total
FROM products as p
JOIN categories as c ON p.category_id = c.id
WHERE c.path like '0/1/%';
Quite an efficient query.
This article provides more information: Additional Trees and Hierarchies in SQL
source to share