Count how many of each value from a field with MySQL and PHP

Question

I've seen that question several times but I can't find out how to display the result. I have a movie database and I would like the count of each genre of movie in my top menu in a single MySQL query so my menu would display like this:

Total Movies (300)
Drama (50)
Comedy (75)
Thriller (30)
...and so on...

I've found some MySQL query on this site but no one specify HOW to handle the counts after the SQL query. Something like this would probably work:

select genre, count(*) from movies group by genre

But how do I display the count for each value afterwards? Thank you very much!

Answer 1

Alias the count part so you have an easily accessible column name:

SELECT genre, count(*) AS nb_movies FROM movies GROUP BY genre

then you can access it like $row['nb_movies'].

Without the alias the aggregate column takes the name of the aggregate function call which produced it, so in your case it would be accessed like $row['count(*)'].

Answer 2

Try

select genre, count(*) AS total from movies group by genre

Use total as your count for eg.

echo $result['total'];