CODEWITHSUNDEEP
phpsqlnull
Dan Sebastian
Dan Sebastian

Reputation: 539

SQL: in calculation, I want the result to be 0, not null

I have a SQL query in PHP that recalculates the average rating when the user cancels his vote:

$delete_vote = $conn->prepare("UPDATE table SET 
                    votes = votes - 1, 
                    total_value = total_value - :recall_vote, 
                    average = (total_value * 10) / (total_votes * 10)
                    WHERE id=id");

The problem is with the recalculation of the average vote. Even if I set the default value for the column 'total_value' and for 'average' as zero, if the query in question has only one vote, and that vote is being recalled, the average value will be set to null instead of 0, as I wish it to be. One possible way of solving this would be to retrieve these two values from the database, check if they're null, and then have them changed to 0--but that's a bit of a hassle. So instead, I want to know if there's a simpler solution.

Another thing I've tried is to add a zero to the calculation, hoping that the null would convert to 0:

$vote_count = $conn->prepare("UPDATE table SET 
                total_votes = (0 + total_votes) + 1

That doesn't work either. Is there a simple solution to this?

Upvotes: 0

Views: 133

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I totally do not understand your where clause -- which would almost always evaluate to true.

The correct syntax to use is the ANSI standard COALESCE() or CASE which are available in almost all databases. You could write this as:

UPDATE table
    SET votes = votes - 1, 
        total_value = total_value - :recall_vote, 
        average = COALESCE((total_value * 10) / NULLIF(total_votes * 10, 0), 0)
    WHERE id = $id -- something other than `id`

I think I would be inclined to be explicit:

UPDATE table
    SET votes = votes - 1, 
        total_value = total_value - :recall_vote, 
        average = (CASE WHEN total_votes > 0
                        THEN total_value / total_votes
                        ELSE 0
                   END)
    WHERE id = $id -- something other than `id`

By the way, why are you multiplying by 10 in both the numerator and denominator? Seems unnecessary.

Upvotes: 1

Guido Hendriks
Guido Hendriks

Reputation: 5776

Since you're using PHP, I'm assuming you're using MySQL with it. But I think every RDBMS has a similar function... In MySQL you can define a fallback value for when a value is NULL. If the value isn't NULL, it will return the original value:

IFNULL(something, 0)

Besides that, slightly offtopic maybe; I usually try to avoid denormalisation like you did by saving the average - which in most cases can be calculated when querying the database. But this depends on the situation.

Upvotes: 1

Related Questions