MYSQL Query - Selecting the count

[Weirfire]

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.

[Weirfire]

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.

[palespyder]

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);

?>

This may or may not be the easiest way.

[mushroom]

"SELECT COUNT(DISTINCT Category) FROM MyTable "

[nfzgrld]

I usually do something like this:


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


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

[search_engine_optimizer]

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

[mnemtsas]

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

[Weirfire]

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

I bet you feel :o now

;)

[mnemtsas]

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