Im looking to create a ul list of categories and its sub categories (with count) related to the users search.
Example. if you was to go to ebay and search mac book pro, on the left it would show
Computers/Tablets & Networking (20 items)
-Laptop & Desktop Accessories (5 items)
-Laptops & Netbooks (15 items)
I have 2 tables:
Categories Table
id | category_name | parent_id
--------------------------------------
1 | computers | 0
2 | apple | 2
3 | microsoft | 2
4 | accessories | 0
5 | mouse | 4
6 | keyboards | 4
7 | printers | 4
#############################################################
And Products Table
id | product_name | category_id
--------------------------------------
1 | macbook pro | 2
2 | macbook air | 2
3 | surface pro | 3
4 | ipad | 2
5 | backlit keyboard | 6
6 | mini keyboard | 6
7 | 3 in 1 printer | 7
some sql and php to disply:
computers (4)
-apple (3)
-microsoft (1)
accessories (3)
-keyboards (2)
-printers (1)
-(dont show mouse because no mouses in products table)
I have spent the last few hours searching but havent found what im looking for.
Regards
Shane
I think you can get what you want just using with rollup. The problem is that the sum will appear after the base rows:
select cp.category_name as parent_name, c.category_name, count(*) as numcategories
from products p join
categories c
on p.categoryid = c.id join
categories cp
on c.parentid = cp.id
group by cp.category_name, c.category_name with rollup
To get it before, try this:
select parent_name, category_name, numcategories
from (select cp.category_name as parent_name, c.category_name, count(*) as numcategories
from products p join
categories c
on p.categoryid = c.id join
categories cp
on c.parentid = cp.id
group by cp.category_name, c.category_name with rollup
) t
where category_name is not null
order by category_name,
category_name is null desc;
@Gordon Linoff using the second statment:
$result = mysqli_query($db,"select parent_name, category_name, counter from (select cp.category_name as parent_name, c.category_name, count(*) as counter from adverts p join categories c on p.category_id = c.id join categories cp on c.parent_id = cp.id group by cp.category_name, c.category_name with rollup) t where category_name is not null order by parent_name, counter DESC");
$count = mysqli_num_rows($result);
if($count >= 1){
while($row = mysqli_fetch_array($result)) {
if (empty($rowo['category_name'])) {
$category_view = ("<strong>". $rowo['parent_name']. "</strong> (". $rowo['counter']. ")");
}else{
$category_view = ("". $rowo['category_name']. " (". $rowo['counter']. ")");
}
echo("<li>". $category_view ."</li>");
} // while loop
} // counter >= 1
Antiques (3)
-Fabric/ Textiles (2)
-Ethnographic Antiques (1)
Art (5)
-Photographs (4)
-Drawings (1)
Thanks again Gordon.