Count instances in a column

[JonnyT]

Hi. I have a rating system that will put either the word 'awesome', 'good', 'okay', 'disappointing' or 'boring' into a column that I define in the php code as $rate_id from the table $tbl_name.

What I want to happen is for an SQL query to count how many times each keyword appears in the column $rate_id, and have these results put into different php variables. Making it easy for me to put <?php echo($result_awesome); ?> etc. later on in the page.

I've tried Google-ing an answer, but have failed to find a successful one.

Any help is appreciated :)

[Old Pedant]

Well, the best way would be this:

SELECT ratename, COUNT(*)
FROM yourtable
GROUP BY ratename
ORDER BY ragename

That will get you one record per ratename, so you'd just loop the records storing the name/count pairs into a PHP associative array, perhaps, and then grabbing the counts, by name, in whatever order you wanted on the PHP page.

In other words, later in the page you'd just do something like
echo $rate['good'];


There *IS* a way to get all the values back in a single row, if you really think that's the only way you know how to use the PHP.

Oh, w.t.h. Like this:

SELECT SUM(IF(ratename='awesome',1,0)) AS awesome,
SUM(IF(ratename='good',1,0)) AS good,
SUM(IF(ratename='okay',1,0)) AS okay,
SUM(IF(ratename='disappointing',1,0)) AS disappoionting,
SUM(IF(ratename='boring',1,0)) AS boring
FROM table

But I'm pretty sure the first version will execute a lot quicker.

(We won't talk about the fact that you probably should have just stored the ratings as *numbers* [eg., 1 through 5] in the DB, so that you could later do such things as easily develop an average rating and/or maybe show a number of stars. It's usually a mistake to store enumeration values like this as strings in a DB.)

[Old Pedant]

Incidentally, probably the reason googling found nothing is indeed because you used strings instead of numbers for the ratings.

[JonnyT]

Thanks for your help - Works great.

The rating will show the total amount of votes per each individual rating rather than showing stars with averages etc. - I have my reasons :p