+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Query Builder Conditions Not Being Applied (/showthread.php?tid=90861)
Query Builder Conditions Not Being Applied - johnhimmelberger - 05-14-2024
Subject: Query Builder Conditions Not Being Applied in CodeIgniter 4
Hi everyone,
I'm encountering an issue with the CodeIgniter 4 query builder where conditions are not being applied to my SQL queries. Here are the details:
Issue Description:
I'm trying to filter records in the suggestions table based on specific conditions (completed = 1 and deleted = 0). However, the QBWhere array remains empty, and the conditions are not reflected in the executed SQL query.
Expected SQL Query:
SELECT * FROM `suggestions` WHERE `completed` = 1 AND `deleted` = 0
Actual SQL Query (From Debugging):
SELECT * FROM `suggestions`
Code Example:
Here's the function I am using:
public function getSuggestions($filter, $page)
{
$db = \Config\Database::connect();
$builder = $db->table('suggestions');
switch ($filter) {
case 'completed':
$builder->where('completed', 1);
$builder->where('deleted', 0);
break;
case 'uncompleted':
$builder->where('completed', 0);
$builder->where('deleted', 0);
break;
case 'all':
$builder->where('deleted', 0);
break;
}
$limit = 4;
$offset = ($page - 1) * $limit;
$builder->limit($limit, $offset);
// Debugging: Print the SQL query
echo "SQL Query: " . $builder->getCompiledSelect() . "<br>";
// Execute the query
$query = $builder->get();
return $query->getResult();
}
Builder State Output:
CodeIgniter\Database\MySQLi\Builder Object
(
[resetDeleteData:protected] =>
[QBSelect:protected] => Array
(
)
...
[QBWhere:protected] => Array
(
)
...
)
Steps Taken:
Verified Conditions: Double-checked the conditions and their logic.
Debug Output: Printed the SQL query and builder state for debugging.
Minimal Example: Created a simplified version to isolate the issue.
Checked CodeIgniter Version: Ensured I'm using the latest version of CodeIgniter 4.
Question:
What could be causing the QBWhere array to remain empty, and why aren't the conditions being applied to the query builder? Has anyone encountered a similar issue or can provide insights on what might be wrong?
Additional Info:
CodeIgniter Version: 4.5.1
Database: MySQL 8.0.36-0ubuntu0.22.04.1
PHP Version: 8.1
Thank you in advance for your help!
RE: Query Builder Conditions Not Being Applied - kenjis - 05-14-2024
Remove the line: echo "SQL Query: " . $builder->getCompiledSelect() . "<br>";
See https://codeigniter.com/user_guide/database/query_builder.html#builder-getcompiledselect
If you want to see the query,
- use Debug Toolbar https://codeigniter.com/user_guide/testing/debugging.html#the-debug-toolbar
- or $db->getLastQuery() https://codeigniter.com/user_guide/database/queries.html#db-getlastquery
- or https://codeigniter.com/user_guide/testing/debugging.html#logging-all-sql-queries