CodeIgniter 4 where() builds a different condition than you meant
A query with where() and orWhere() returns rows that should have been filtered outShort answer
The query builder joins every where() with AND and every orWhere() with OR, with no brackets. Mixed conditions then bind in the wrong order. Wrap the OR part in groupStart() and groupEnd(). If you pass a raw string to where(), CodeIgniter does not escape it, so escape values yourself or use whereIn() for lists.
Fix it fast
1Find out which cause you have
Print the SQL the builder produced. Read the WHERE part and look for a missing bracket or a quote in the wrong place.
echo $builder->getCompiledSelect();2Match what you see, then jump to its fix
- 1
Rows from other users or other statuses appear in the resultAND and OR are mixed without bracketsFix - 2
A syntax error or an empty result when the value has a quote in it, such as O'BrienA custom string contains a value that was not escapedFix - 3
The WHERE has IN (...) built with implode, and breaks when the list is empty or contains textA list of values was glued into the string by handFix
- 1
What does the error look like?
A query with where() and orWhere() returns rows that should have been filtered outA SQL syntax error that appears only when a value contains a quote character
Cause 1 of 3
AND and OR are mixed without brackets
1Check
Look at the compiled WHERE. An expression such as a AND b OR c is not the same as a AND (b OR c).
$builder->where('status', 'active')->where('name', 'Joe')->orWhere('id >', 50);
echo $builder->getCompiledSelect(false);2Fix
Open a group with groupStart() before the OR conditions and close it with groupEnd(). Each groupStart() needs its own groupEnd().
$builder->where('status', 'active')
->groupStart()
->where('name', 'Joe')
->orWhere('id >', 50)
->groupEnd();3Verify
Print the compiled query again and confirm the OR part sits inside brackets.
echo $builder->getCompiledSelect();Confirmed by codeigniter.com
Cause 2 of 3
A custom string contains a value that was not escaped
1Check
In a custom string CodeIgniter does not escape the values for you. The manual warns that skipping the escape step could lead to SQL injection.
grep -rn "->where(\"" app/2Fix
Escape every user value with $builder->db->escape() before putting it into the string, or avoid the string and use the array or key-value forms of where().
$name = $builder->db->escape($input);
$where = "name={$name} AND status='boss' OR status='active'";
$builder->where($where);3Verify
Test with a value that has an apostrophe and read the compiled SQL.
echo $builder->getCompiledSelect();Confirmed by codeigniter.com
Cause 3 of 3
A list of values was glued into the string by hand
1Check
Search the code for implode used inside a where string.
grep -rn "implode" app/Models app/Controllers2Fix
Pass the array to whereIn(); it produces WHERE field IN ('a', 'b', 'c') and adds the quotes for you.
$names = ['Frank', 'Todd', 'James'];
$builder->whereIn('username', $names);3Verify
Check the compiled SQL shows quoted values.
echo $builder->getCompiledSelect();Confirmed by codeigniter.com
How do I stop it coming back?
- Prefer the array and key-value forms of where(); they are escaped for you.
- Treat every raw where string as code review material: no request data goes in without escape().
- Print getCompiledSelect() while writing a query that mixes OR with AND.
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Back end
Selecting rows where a column is not null in the CodeIgniter query builder
IS NOT NULL appears inside quotes in the compiled SQL
The query builder escapes values, so a value such as 'IS NOT NULL' becomes a quoted string, not SQL.
3 causes, each with a fixBack end
CodeIgniter 4: try/catch does not catch a failed insert in the model
DatabaseException
CodeIgniter throws a DatabaseException on a failed query only when DBDebug is true, and inside a transaction it does not throw unless you ask.
3 causes, each with a fixBack end
Call to undefined function mysql_pconnect() after a PHP upgrade
Call to undefined function mysql_connect()
The old mysql_* functions belonged to the ext/mysql extension, which PHP removed in version 7.0.
2 causes, each with a fixBack end
CodeIgniter cannot connect to the MySQL database server
Unable to connect to your database server using the provided settings.
CodeIgniter only reports that its connection failed.
4 causes, each with a fixBack end
Laravel insert fails with 'Field user_id doesn't have a default value'
The insert works when the column is removed or made nullable
Error 1364 means Laravel sent an INSERT without a value for a NOT NULL column that has no default, and MySQL strict mode refuses it.
3 causes, each with a fixBack end
Laravel migration cannot drop a column that has a foreign key
Error on rename of '%s' to '%s' (errno: %d - %s)
MySQL refuses to drop a column while a foreign key constraint still uses it.
2 causes, each with a fix