Count number of returned rows for menu items
i have 2 tables: Categories,Oglasi
categories
category_id | category | parent
1 auto 0
2 games 0
3 bmw 1
4 cards 2
oglasi
oglas_id | category_id
1 3
2 4
What im trying to do is make a menu of this as:
parent name - category name ( number of items in this category)
Example : auto - bmw ( 1 )
Now i got it to make a tree menu of parent name-category name,but don't know how to connect all of this with the counted rows number. Here's my query and code to do this:
$kategorije = dbQuerySelect('SELECT a.category parent
, b.category child
FROM categories a
JOIN categories b
ON a.category_id = b.parent
ORDER BY a.category_id');
if ($kategorije)
{
$parent = '';
echo "<ul>";
foreach ($kategorije as $next) {
if ($next['parent'] != $parent) {
if (strlen($parent) > 0) {
echo " </ul>";
echo " </li>";
}
echo " <li>" . $next['parent'];
echo " <ul>";
}
echo " <li>" . $next['child'] . "</li>";
$parent = $next['parent'];
}
echo " </ul>";
echo " </li>";
echo "</ul>";
}
So in this case this returns:
parent | child
auto bmw
games cards
Can someone please help me to edit my code and return the number of rows in each 'subcategory' ? I tried a fe开发者_JAVA技巧w variations with COUNT in the query,but i just couldn't make it work.
SELECT a.category parent,
b.category child,
(select count(*) from oglasi where category_id = b.category_id) as count
FROM categories a
JOIN categories b
ON a.category_id = b.parent
ORDER BY a.category_id
With this you should get:
parent|child|count
auto bmw 1
精彩评论