Number of Items in a Category

if we have category(x) displayed on a page, how do i get x?

[a2web]

Hello All!

Please can anyone tell me how can I display the number of items (or subcategories) found in a category? This is a common feature used often in sites (mostly e-commerce or forums).

Example: You have a database table say for 'famous_quotes'. Each row contains the fields 'quote', 'author' and 'category'. There are many different quotes in the table, but some of them share the same value for 'category'.

I can display all rows. I can also display DISTINCT 'category names' but I 'm having trouble displaying how many different rows fall under thesame category. something like:

funny (3) religious(38)
Wisdom (14) science(19)
business (6)

how do i get the numbers in brackets is what i'm asking?
Thanks guys!

[coopster]

Welcome to WebmasterWorld, a2web.

SQL has an aggregate function named COUNT. You COUNT them.

SELECT 
 category, 
 COUNT(category) AS count 
 FROM famous_quotes 
 GROUP BY category 
;

[a2web]

thanx cooper,
indeed, i have come across this sql function in the course of finding the solution and used it in my code. the problem is how to get the actual count for each category displayed while looping the output?
what d'you think?

[dmmh]

$cat = mysql_real_escape_string($_GET['category']);

$query = 'SELECT COUNT(*) as count FROM famous_quotes WHERE famous_quotes = '$cat'";
$result = mysql_query($query);
$row = mysql_fetch_array($result);
$num_items_in_category = $row['count'];

[a2web]

It works!
i was not familiar with the sql command statement COUNT __ AS. using this as well as sorting the data into an array has helped. thanx alot guys for your help.

[dmmh]

welcome :)