Tag: sql

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 query

Output:

SELECT * FROM `users` WHERE `id` = 1

getLastQuery() returns a CodeIgniter\Database\Query object, so you can also format it if needed:

echo $db->getLastQuery()->getQuery(); // returns query string

Pro 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\Toolbar for 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 queries

CodeIgniter 4:

Use the Debug Toolbar, or manually:

$db = \Config\Database::connect();
$queries = $db->getQueries(); //returns an array of all queries

Conclusion

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!