CODEWITHSUNDEEP
phpmysqlcount
Andrewww
Andrewww

Reputation: 41

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

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!

Upvotes: 4

Views: 1916

Answers (6)

Jenson M John
Jenson M John

Reputation: 5689

Try 

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

Use total as your count for eg.

echo $result['total'];

Upvotes: 1

Ranjith
Ranjith

Reputation: 2819

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'];

}

Upvotes: 0

Shijin TR
Shijin TR

Reputation: 7766

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

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

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212412

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']

Upvotes: 0

Edwin Alex
Edwin Alex

Reputation: 5108

Try this,

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

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

Upvotes: 0

prodigitalson
prodigitalson

Reputation: 60413

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

Upvotes: 6

Related Questions