Polling: Count & ENUM: Need to get NULL

[Anon]

I created a table with 2 columns: 'prod_id' and 'rank' which is defined as enum('good','bad','ugly')

Now when I do a count on 'rank' for a specific 'prod_id', I can get the total votes for each 'rank'. But it doesn't display '0' for 'rank' types which don't exist. Is there any way to display a '0' in this situation? Or is there a better way to do this?

The sql I use is:

select rank, count(rank) from poll where prod_id = $prod_id group BY RANK

Thanks,
Doug

[[deleted]]

NULL is nothing, so you it isn't something you can group by.

Instead you'll have to make NULL into something new for grouping, using the IF statement:

select rank, count(rank) ,if (rank is NULL,0,rank) as newrank from poll where prod_id = $prod_id group BY newrank

[Anon]

When I do Vincent's suggested sql, I now get a 3rd column, 'newrank', but the NULL values aren't listed. For example, if there is only 1 prod_id row in the poll table, and it has a value of 'good' for 'rank' I get:

rank count(rank) newrank
good 1 good

What I would like to get is:

rank count(rank) newrank
good 1 good

bad 0 bad

ugly 0 ugly

Is this possible or should I not make 'rank' an ENUM column type?

Thanks, Doug

[Anon]

I was able to get it working with the following. I wonder if there is a more efficient way to do this...

$i=0;
while ($row = mysql_fetch_array($result))
{
$points[$i] = $row[Count];
$desc[$i] = $row[rank];
$i++;
}

$size = sizeof($points);

if ($size == 3)
{
$good = $points[0];
$bad = $points[1];
$ugly = $points[2];
}
elseif ($desc[0] == 'bad' && $size == 1)
{
$good = 0;
$bad = $points[0];
$ugly = 0;
}
elseif ($desc[0] == 'bad' && $size == 2)
{
$good = 0;
$bad = $points[0];
$ugly = $points[1];
}
elseif ($desc[0] == 'ugly')
{
$good = 0;
$bad = 0;
$ugly = $points[0];
}
elseif ($desc[0] == 'good' && $size == 1)
{
$good = $points[0];
$bad = 0;
$ugly = 0;
}
elseif ($desc[0] == 'good' && $size == 2 && $desc[1] == 'bad')
{
$good = $points[0];
$bad = $points[1];
$ugly = 0;
}
else
{
$good = $points[0];
$bad = 0;
$ugly = $points[1];
}

[[deleted]]

Doug, you can't get what's not there.

If there is only one record, you can only get the values of that one record.

If you want results for data that does not appear in the table, you must use a where statement to get that value specifically.

[Anon]

In my example below, I have shown that it's possible to get the values that don't exist though deduction. That is, by knowing the size of the array, the order of the values in the ENUM column, and at least one positive value, it's possible to determine the other NULL values.

[[deleted]]

Like I said, you can only get "non existing" data if you look for it specifically.

In your example, the database is still only reporting about those values that exist in the database.
You then proceed to look in that data to find the values that are missing, so you can fill them in yourself.

My point was that the database can't give you this information directly.