When developing applications with CodeIgniter, retrieving the last executed SQL query becomes the most useful features for debugging and performance tuning. Whether you’re trying to diagnose a bug, optimize performance, or log queries for later review, CodeIgniter makes it easy to access the most recent database query.
In this article, we’ll explore how to get the last executed query in both CodeIgniter 3 and CodeIgniter 4, with examples.
Why Retrieve the Last Executed Query?
Here are a few scenarios where getting the last executed query is helpful:
- Debugging incorrect or unexpected results.
- Profiling SQL performance issues.
- Logging queries for auditing purposes.
- Building custom query logs for admin or developer panels.
CodeIgniter 3: Getting the Last Query
CodeIgniter 3 provides a simple method from the database class:
$this->db->last_query();For Example:
public function getUser($id)
{
$query = $this->db->get_where('users', ['id' => $id]);
echo $this->db->last_query(); // Outputs the SQL query
return $query->row();
}Output:
SELECT * FROM `users` WHERE `id` = '1'You can also store it in a variable to use it for logging:
$last_query = $this->db->last_query();
log_message('debug', 'Last Query: ' . $last_query);CodeIgniter 4: Getting the Last Query
In CodeIgniter 4, the approach is slightly different. You can use the getLastQuery() method from the Query Builder object.
Example:
$db = \Config\Database::connect();
$builder = $db->table('users');
$query = $builder->where('id', 1)->get();
echo $db->getLastQuery(); // Outputs the last SQL queryOutput:
SELECT * FROM `users` WHERE `id` = 1getLastQuery() returns a CodeIgniter\Database\Query object, so you can also format it if needed:
echo $db->getLastQuery()->getQuery(); // returns query stringPro Tips
- Use this feature only in development mode or behind admin-only views.
- Avoid exposing raw SQL queries in production environments for security reasons.
- Combine it with
CodeIgniter\Debug\Toolbarfor enhanced SQL visibility in CI4.
Logging All Queries in CodeIgniter
You can also log all database queries automatically:
CodeIgniter 3:
In application/config/database.php, set:
$db['default']['save_queries'] = TRUE;Then access them:
print_r($this->db->queries); // array of all executed queriesCodeIgniter 4:
Use the Debug Toolbar, or manually:
$db = \Config\Database::connect();
$queries = $db->getQueries(); //returns an array of all queriesConclusion
Accessing the last executed SQL query in CodeIgniter is a powerful feature that can significantly speed up debugging and development. Whether you’re using CodeIgniter 3 or 4, the framework provides convenient tools to track your database interactions.
Make sure to leverage this feature wisely, especially when you’re optimizing queries or tracking down elusive bugs.
Do you use query logging in your CodeIgniter project? Share your tips or challenges in the comments below!