MySql handling null return values

Question

I have a simple query as follows:

SELECT sum( people) as total
FROM event
WHERE event.id = 4;

The event id 4 is not in the table (intentionally) so the query returns NULL. How would I modify the query to deal with NULL values?

I am trying to use this with a php script so if the id is non existent the php script should fail.

Peter Bowers · Accepted Answer · 2015-03-22 21:11:48Z

2

When you pull it into PHP if the value of your summed column is null then you have a null...

$row = $result->fetch_assoc();
if ($row['total'] === null)
    ...

answered Mar 22, 2015 at 21:11

Peter Bowers

3,1031 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robert Over a year ago

let me try your solution

Tech Savant · Accepted Answer · 2015-03-22 21:08:18Z

1

You can just do it in PHP. I have my database classes return false if the query did not select any records. You can check that a value was returned from your query statement, if there was, you can count the number of rows and make sure they are greater than 0.

Try this in your query..

select * from (SELECT sum( people) as total FROM event WHERE 
event.id = 4) as subsel where subsel.total is not null

That should make it so it returns no rows.

answered Mar 22, 2015 at 21:08

Tech Savant

3,7671 gold badge21 silver badges40 bronze badges

2 Comments

Robert Over a year ago

if it returns null it still counts as a row so that wont work

Robert Over a year ago

mysqli. What you are doing is returning an empty set in my problem it still returns a row with the value null

Collectives™ on Stack Overflow

MySql handling null return values

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related