I encountered a problem while fetching database field value which is basically a null value
I tried
SELECT * FROM table WHERE field=''; //this means null in php
and
SELECT * FROM table WHERE field IS NULL; //this means null in mysql db
Both queries are producing different result.
What is the difference b/w both null values and how can be the null values different?
When querying the database, definitions from PHP are thrown out the window. In SQL, "" represents an empty string.
In PHP programming, the difference between null, zero, false, and empty string easily becomes ambiguous because of PHP's == vs. ===. Oracle, MSSQL, and MySql (sometimes?) are a lot more strict in this respect.
PHP, as a high-level language, allows loose comparisons, as described by PHP type comparison tables.
==, an empty string and null are equivalent. But they are indeed different values in PHP, and you can tell them apart using ===, is_string(), and is_null().
x IS NULLorx IS NOT NULL. This is just how it's defined.x = NULLshould return false (for anyx) per SQL standard, but has been broken in some SQL implementations. These semantics are useful for joins. Sadly, I don't have an answer of how to turn "passing a null" intoIS NULLmagically -- but this is trivial with prepared statements (oh, you are using those, no?) and a condition to select "which" query. However, I have not run into a case where I've needed to support NULL as such and am tempted to argue a [blank] not-NULL field.null == ''but only because the==operator does type casting.null === ''isfalse.