CODEWITHSUNDEEP
phpmysqlsqlnull
stack
stack

Reputation: 10228

MySQL: What happens when something is equal with NULL?

I have a CASE WHEN function in my query. Something like this:

CASE WHEN h.user_id = :user_id THEN '1' 
     ELSE '0'
END paid

And I pass :user_id like this:

$sth->bindValue(":user_id", $user_id, PDO::PARAM_INT);

Well, Sometimes $user_id is NULL. Already I heard when something is equal with NULL in the query, something bad will happen. Now I want to know, what happens when $user_id is NULL?

I tested it, but all fine, I don't see any problem. When $user_id is NULL, $result['paid'] is 1 or that CASE..WHEN breaks?

EDIT:

All I want to do it, when h.user_id = :user_id then paid = 1. But sometimes :user_id is null. How can I escape null ?

Upvotes: 2

Views: 123

Answers (2)

Shadow
Shadow

Reputation: 34231

If the passed parameter is truly a null value, then the result of the case expression should be 0 because null will not be equal with any of the field values, so the else branch have to kick in. There is no need to escape null values.

In order to appropriately pass null value from php to mysql using PDO see this SO topic

Upvotes: 1

Darwin von Corax
Darwin von Corax

Reputation: 5246

The problem you're encountering is because, according to the standard, comparing anything to null gives a result of null, which eventually gets coerced to false. Thus your expression, as written, will always return 0 when either :user_id or h.user_id is null.

Fortunately, this situation arises often enough that MySQL provides a bit of syntactic sugar called the "spaceship" or "NULL-safe equal" operator, which looks like <=>. It is used exactly like the normal = operator, except that it returns false (not null) when comparing a null to a non-null, and true when comparing two null.

The expression then becomes

CASE WHEN h.user_id <=> :user_id THEN '1' 
     ELSE '0'
END paid

Upvotes: 6

Related Questions