How do I use count?

[coding_noobie]

Am I doing something wrong with count? I’m trying to count the number of good and bad ratings but the results don’t match what I’m expecting

The query is:

<?php
  $query="SELECT * FROM ratings WHERE review_id=".$value['id'];
  $ratings=dbselect( $query,"dbExt" );
  $help=$ratings[0]['good'];
  $nohelp=$ratings[0]['bad']; ?>

var_dump outputs:

array(3) {
[0]=> array(5) {
["id"]=> string(1) "1"
["review"]=> string(6) "570579"
["date"]=> string(10) "1363952704"
["good"]=> string(1) "1"
["bad"]=> NULL }
[1]=> array(5) {
["id"]=> string(1) "4"
["review"]=> string(6) "570579"
["date"]=> string(10) "1363952704"
["good"]=> NULL
["bad"]=> string(1) "1" }
[2]=> array(5) {
["id"]=> string(1) "3"
["review"]=> string(6) "570579"
["date"]=> NULL
["good"]=> string(1) "1"
["bad"]=> NULL }
}

To display the results I’m using this:
Good - <?php echo count( $help ) ?>, Bad - <?php echo count( $nohelp ) ?>

But instead of saying the results as good 2 and bad 1 I get this result: Good -1, Bad - 0

[oddz]

Something like the below should work.

$query= "SELECT SUM(CASE WHEN good IS NOT NULL THEN 1 ELSE 0) good,SUM(CASE WHEN bad IS NOT NULL THEN 1 ELSE 0) bad FROM ratings WHERE review_id=".$value['id'];

[coding_noobie]

I’m not sure what I did wrong but I got this when I tried that:

SELECT SUM(CASE WHEN good IS NOT NULL THEN 1 ELSE 0) good,SUM(CASE WHEN bad IS NOT NULL THEN 1 ELSE 0) bad FROM ratings WHERE review_id=34512

(You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) good,SUM(CASE WHEN bad IS NOT NULL THEN 1 ELSE 0) bad FRO’ at line 1)

[s_molinari]

Try

$query= "SELECT SUM(CASE WHEN good IS NOT NULL THEN 1 ELSE 0) as good,SUM(CASE WHEN bad IS NOT NULL THEN 1 ELSE 0) as bad FROM ratings WHERE review_id=".$value['id'];

I am not sure how the rating system works, but if it is actually only one rating, you might want to think about using an integer and only one “rating” field. 1 = good. “null” = no rating. -1 = bad

Scott

[coding_noobie]

Thanks, I’ll give that a go. I have to actually store them as separate fields because only one user is allowed to to vote once so I need to keep their id, email etc.

[coding_noobie]

I changed it slightly and it now works, I’ve ended up with this:

<?php
$query= "SELECT sum(case when good = '1' then 1 else 0 end) good, sum(case when bad = '1' then 1 else 0 end) bad from ratings WHERE review_id=".$value['id'];
  $ratings=dbselect( $query,"dbExt" );
  $help=$ratings[0]['good'];
  $nohelp=$ratings[0]['bad']; ?>

The result is now this:
Good - <?php if( count( $help )<1 ) { ?>0<?php } ?><?php echo $help ?>, Bad - <?php if( count( $nohelp )<1 ) { ?>0<?php } ?><?php echo $nohelp ?>

I added a if clause because it looked funny if there was no number at all.

[s_molinari]

I was talking about using one field for the rating itself. With one field, let’s call it “rating”, it could look like this.

$query= "SELECT SUM(CASE WHEN rating < 0  THEN rating) as BadRating, SELECT SUM(CASE WHEN rating > 0  THEN rating) as GoodRating FROM ratings WHERE review_id=".$value['id'];

Also, I would imagine (but I could be totally wrong) that you need the average of the sums and not just the sums, to show a proper rating.

$query= "SELECT AVG(CASE WHEN rating < 0  THEN rating) as BadAverage, SELECT SUM(CASE WHEN rating > 0  THEN rating) as GoodAverage FROM ratings WHERE review_id=".$value['id'];

Scott

[coding_noobie]

Hmm I see what you’re saying about averages, I’m not sure about that although it might be an idea to do…

[oddz]

Yeah, I forgot the end portion good job figuring that out for yourself.

[coding_noobie]

Thanks, I have to admit I was quite pleased with myself!

[oddz]

Problem solving skills are more important than memorizing syntax.

[coding_noobie]

lol true