MySQL query help

[Anon]

I need help with this query.

I have a table from which I selectively retrieve info based on a column called (CatId).

Many records can have the same catid, and there are 16 unique catid's.

What I want is to get the total records for each catid.

I know I can issue:

SELECT COUNT(*) FROM tbl_name WHERE catid= '1' ;

and then repeat the query 16 times to get the totals for each catid, each time incrementing the catid by 1. But that is a lot of trips to the database.

Is it possible to issue one query and obtain the totals for the individual catid groups?

Thanks.

Richie.

[[deleted]]

SELECT catid, COUNT(*) FROM table GROUP BY catid;

[Anon]

I think it's the solution:
select catid, count(*) from table_name group by catid order by catid;

of course you not need order catid's but if you wanna 🙂)

regards

[Anon]

Thanks guys. Now I'm having trouble accessing individual counts.

I want to use php to display the count for each category. I tried the following, but it doesn't do what I want

----------------begin--------------------

$query = "select catid, count(*) from music.post group by catid";

$result = mysql_db_query($db,$query,$connection) or die("Cannot run query");

mysql_close ($connection);

$cat_count = mysql_fetch_array($result);

-----------------end------------------

Next I tried accessing the counts like this:

$cat_count[0]; $cat_count[1]; $cat_count[2]; etc, but the result it displays is wrong.

Any ideas? Thanks.

Richie.

[[deleted]]

sigh

Didn't you read the manual?

if you did, you'd know that

mysql_fetch_array()

fetches ONE row from the results of your query.

Tip: read the manual.