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!

prodigitalson · Accepted Answer · 2013-04-10 12:59:19Z

6

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(*)'].

answered Apr 10, 2013 at 12:59

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrewww Over a year ago

Ok 6 answers and almost the same for every one, still don't get it though, sorry... :( what would give me the count of every different genre? if I display it by using $row['nb_movies'], it will not give me the count for "Comedy" AND "Thriller" AND every other genre? Do I absolutely need to do a WHILE to get the result like Ranjith suggested?

prodigitalson Over a year ago

Yes you need to jsut loop through the results. I mean you could create a super complex query to give each genre/count as a column i think but thats silly. jsut loop. If you want the total for all genres combined than thats a different query OR snce you need to loop through and get to get the total for each genre any way you can jsut add them up as you go to get the grand total.

Jenson M John · Accepted Answer · 2013-04-10 13:02:41Z

1

Try

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

Use total as your count for eg.

echo $result['total'];

answered Apr 10, 2013 at 13:02

Jenson M John

5,7295 gold badges32 silver badges48 bronze badges

Comments

Edwin Alex · Accepted Answer · 2013-04-10 12:59:45Z

0

Try this,

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

Now you can access like $result['total_genre_movies']

answered Apr 10, 2013 at 12:59

Edwin Alex

5,0944 gold badges31 silver badges51 bronze badges

Comments

Mark Baker · Accepted Answer · 2013-04-10 13:00:15Z

0

It's easier if you alias the result of count(*)

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

Then you can access it as $row['total'] when you fetch the result into $row in exactly the same way you'd reference $row['genre']

answered Apr 10, 2013 at 13:00

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

Comments

Shijin TR · Accepted Answer · 2013-04-10 13:00:29Z

0

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

Now you can access like $result['genre_count']

answered Apr 10, 2013 at 13:00

Shijin TR

7,80911 gold badges64 silver badges126 bronze badges

Comments

Ranjith · Accepted Answer · 2013-04-10 13:01:03Z

0

Try this,

$result = mysql_query("select genre, count(*) as genre_count from movies group by genre");

while($row = mysql_fetch_array($result)) {

   $genre       = $row['genre'];
   $genre_count = $row['genre_count'];

}

answered Apr 10, 2013 at 13:01

Ranjith

2,8393 gold badges24 silver badges42 bronze badges

Collectives™ on Stack Overflow

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

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related