Question:
I’d like to be able to display the number of entries in each category next to the category name. How can I do that?
Answer:
{exp:query sql="SELECT count(exp_category_posts.entry_id) AS count,
exp_category_posts.cat_id, exp_categories.cat_name
FROM exp_categories, exp_category_posts, exp_weblog_titles
WHERE exp_category_posts.cat_id = exp_categories.cat_id AND parent_id='0'
AND exp_weblog_titles.weblog_id = '1'
AND exp_weblog_titles.entry_id = exp_category_posts.entry_id
GROUP BY exp_categories.cat_name"}
<a href="{path=recipes/comments}C{cat_id}/">{cat_name}</a> ({count} )
<br />
{/exp:query}
You will need to update this line:
AND exp_weblog_titles.weblog_id = “1”
to reflect the correct weblog id.
You can add ORDER BY count desc to display categories sorted by count.
Note: Remove linebreaks.
Alternatively, you can also use this query together with the {exp:weblog:categories} tag, making the query a lot smaller:
{exp:weblog:categories weblog="weblog1"}
<a href="{path=weblog/index}">{category_name}</a>
{exp:query sql="SELECT count(exp_category_posts.entry_id) AS count
FROM exp_category_posts WHERE exp_category_posts.cat_id = {category_id} "}
({count} entries)
{/exp:query}
{/exp:weblog:categories}
Category:Tricks Category:Categories Category:Archives Category:Templates