Selecting rows where a column is not null in the CodeIgniter query builder
where('col', 'IS NOT NULL') returns zero rows although the column has valuesShort answer
The query builder escapes values, so a value such as 'IS NOT NULL' becomes a quoted string, not SQL. Pass the whole condition as one custom string instead: where('col IS NOT NULL'). Because custom strings are not escaped for you, keep user input out of them or escape it by hand. Print the last query to confirm.
Fix it fast
1Find out which cause you have
Print the SQL the builder produced. The WHERE part shows whether IS NOT NULL reached the database as SQL or as a quoted string.
// CodeIgniter 3 echo $this->db->last_query(); // CodeIgniter 4 echo $db->getLastQuery();2Match what you see, then jump to its fix
- 1
The compiled SQL shows IS NOT NULL inside quotesIS NOT NULL was passed as a valueFix - 2
You build the string with concatenation, for example where("col IS NOT NULL AND owner = $id")A custom string mixes in user inputFix - 3
The compiled SQL changes your expression, for example around a function callCodeIgniter 3 rewrites the field name in a complex conditionFix
- 1
What does the error look like?
where('col', 'IS NOT NULL') returns zero rows although the column has valuesIS NOT NULL appears inside quotes in the compiled SQL
Cause 1 of 3
IS NOT NULL was passed as a value
1Check
In both versions every value given to where() is escaped, which turns a keyword passed as a value into a string literal. Look at how the call is written.
// does not do what you want
$this->db->where('col', 'IS NOT NULL');2Fix
Give where() the whole condition as one custom string. The CodeIgniter 4 guide itself uses this form for IS NULL in a query builder example.
// CodeIgniter 3
$this->db->where('col IS NOT NULL');
$query = $this->db->get('my_table');
// CodeIgniter 4
$builder = $db->table('my_table');
$builder->where('col IS NOT NULL');
$rows = $builder->get()->getResult();3Verify
Print the last query again. The WHERE part should read col IS NOT NULL without quotes around IS NOT NULL.
echo $this->db->last_query();Confirmed by codeigniter.com
Cause 2 of 3
A custom string mixes in user input
1Check
Values placed inside a custom string are not escaped by the builder. The guide says you must escape values and protect identifiers yourself, or the query is open to SQL injection.
grep -rn "IS NOT NULL" application/ app/2Fix
Keep the null test as a custom string, and pass the other conditions as separate where() calls so they stay escaped.
$this->db->where('col IS NOT NULL');
$this->db->where('owner', $id);3Verify
Print the last query. The owner value should appear escaped and quoted.
echo $this->db->last_query();Confirmed by codeigniter.com
Cause 3 of 3
CodeIgniter 3 rewrites the field name in a complex condition
1Check
The CodeIgniter 3 guide says identifiers are protected unless the third argument of where() is FALSE. This matters when the condition contains a function call or table alias that the builder cannot split.
$this->db->where('LOWER(email) IS NOT NULL');
echo $this->db->get_compiled_select();2Fix
Pass NULL as the value and FALSE as the third argument, so the builder leaves the string alone. You then own the quoting.
$this->db->where('LOWER(email) IS NOT NULL', NULL, FALSE);3Verify
Run the query and read the compiled SQL again.
echo $this->db->last_query();Confirmed by codeigniter.com
How do I stop it coming back?
- Print the compiled query whenever a where() returns nothing, before looking anywhere else.
- Use a custom string only for the null test and keep real values in separate where() calls.
- Read the escape rules once for the major version you use, because CI3 and CI4 differ in details.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
Related fixes
Back end
Android gets HTML instead of JSON from a PHP API
org.json.JSONException: Value <br of type java.lang.String cannot be converted to JSONObject
The response body is not JSON.
2 causes, each with a fixBack end
Apache returns 403 when a URL contains %3F
a request like /page%3Fname=test is refused while /page?name=test works
Recent Apache httpd blocks a rewrite when the request contains an encoded question mark (%3F) and the rewritten target has a literal ?.
2 causes, each with a fixBack end
Apache returns 500 with Premature end of script headers
500 Internal Server Error
Apache ran your script, but the script printed no valid HTTP header before its output.
3 causes, each with a fixBack end
Apache returns 503 or 504 because PHP-FPM did not answer in time
Error dispatching request
Apache sent the request to PHP-FPM and gave up waiting.
3 causes, each with a fixBack end
AWS SDK for PHP cannot get credentials from the instance profile metadata server
Error retrieving credentials from the instance profile metadata server.
The SDK tries the EC2 instance metadata service when it has no valid credentials from anywhere earlier in its chain.
4 causes, each with a fixBack end
AWS SDK for PHP rejects requests: the signature is from the future
Signature not yet current: {date} is still later than {date}
AWS compares the timestamp in your signed request with its own clock.
2 causes, each with a fix