MYSQL Query - Selecting the count

I have a database of several rows where CATEGORY is one of the fields. I would like to write a query where it counts the number of items for each category. Is there a way of doing this without writing the query out each time for the required category?

$Result = mysql_query("SELECT COUNT(*) FROM MyTable WHERE Category = '$Category'") is what I have at the moment.

Any help would be appreciated.

Also, how would I go about extracting the COUNT(*) into PHP.

I've tried

$Row = mysql_fetch_object($Result);
$Count = $Row -> COUNT;

but it doesnt seem to like it.

 

OK I've sorted out the extraction by changing the query to SELECT COUNT(*) AS counter and using $ROW -> counter to select the info.

So all I'm interested in now is finding out a more efficient way of executing a query for a count on all the categories.

 

I do it the same way as the PHP manual

<?php

$Result = mysql_query("SELECT * FROM MyTable WHERE Category = '$Category'")

$num_rows = mysql_num_rows($result);

?>

PHP:

This may or may not be the easiest way.

 

"SELECT COUNT(DISTINCT Category) FROM MyTable "

 

I usually do something like this:

$catcnt = mysql_fetch_row(mysql_query("SELECT COUNT(*) AS count FROM categories"));

Code (markup):

Once finished "$catcnt[0]" contains the result.

 

The other option can be like this:
SELECT COUNT(Category) as counter FROM MyTable group by Category

 

select count(id) as category_count,category from table_name group by category

You can do an inner join on a category name table if you have one of those, something like:

select count(tablename.id) as category_count,category_name from table_name inner join categories on table_name.category_id=categories.category_id group by category_name

 

I cant believe you replied to a 465 day old thread Mark. lol

I bet you feel now

 

465 days eh? Teach me not to look at the date.

 

I'm hearing that doing a select query is a better way to count elements in a database than running the records through a loop and counting the hits for the particular value you are looking for - would that be a fair conclusion? and would you imagine that this is a fairly widely held belief?

Thanks -- how old is that?

- Deck