How to use IS NULL in query from php

Question

I am trying to run query from php. Part of the WHERE clause include the IS NULL.
In this case I am getting no records.
Example:

SELECT * FROM TABLE WHERE ITEM IS NULL"

no records returned. When tested directly in PL/SQL I got records.

SELECT * FROM TABLE WHERE ITEM ='cars'"

it's working fine records retreived.

Does PHP reject when using IS NULL in a query?

The error must be somewhere else. – Álvaro González Jan 17 '19 at 09:10 — Álvaro González, Jan 17 '19 at 09:10

score 0 · Accepted Answer · answered Jan 17 '19 at 08:43

0

Is it actually NULL or is it just empty?

SELECT * FROM TABLE WHERE ITEM =''

This query will check if your row field is empty

SELECT * FROM TABLE WHERE ITEM IS NULL

will check if the row field is null

This answer provides a really good insight of their difference:

answered Jan 17 '19 at 08:43

pr1nc3

IN PL/SQL SELECT * FROM TABLE WHERE ITEM ='' does not give records while SELECT * FROM TABLE WHERE ITEM IS NULL gives records. – Junior Developer Jan 17 '19 at 09:02
after '=' there should be 2 single quotes not 1 double quote. Also IS NULL is working only if the value in the field is null. If it is empty you need to use the 2 single quotes – pr1nc3 Jan 17 '19 at 09:22
1

I'm confused... Oracle handles empty strings in a [non-standard way](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=a788783879a41f5e818c62b1fa30d9fb) so `ITEM =''` will never match anything and `ITEM IS NULL` will match both cases. Additionally, it isn't clear who using PHP as client can change this semantics. – Álvaro González Jan 17 '19 at 10:44
1

As @ÁlvaroGonzález says, this answer is not correct for Oracle databases as [`''` and `NULL` are identical](https://stackoverflow.com/q/203493/1509264). Filtering on `WHERE ITEM = ''` is the same as `WHERE ITEM = NULL` and will never match any rows. – MT0 Jan 17 '19 at 11:49

score 0 · Answer 2 · answered Jan 17 '19 at 09:49

0

Managed to do a work around and added AND 1=1 and worked.

SELECT * FROM TABLE WHERE ITEM IS NULL AND 1=1

answered Jan 17 '19 at 09:49

Junior Developer

I insist: there must be something else going on. Question is tagged as *php* so you surely have code that's more complex than that. – Álvaro González Jan 19 '19 at 10:25

2 Answers2