Get last executed query in MySQL with PHP/CodeIgniter

Get last executed query in MySQL with PHP/CodeIgniter

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Retrieving the Last Executed Query in MySQL with CodeIgniter

In legacy PHP applications, particularly those running on CodeIgniter versions prior to 2.0, developers often needed to inspect the SQL string sent to the database immediately after execution. The requirement typically arises during debugging complex dynamic queries or when validating that a specific query structure was actually transmitted. However, accessing this information in modern MySQL environments requires navigating between the PHP session data and the server-side logs.

Accessing Query Data via last_query()

The most direct approach within the CodeIgniter framework is to utilize the database driver's internal state after an execution event. In standard CodeIgniter implementations, the $db object maintains a reference to the last query executed. You can retrieve this using the last_query() method provided by the active connection.

Consider a scenario where you have constructed a complex query involving multiple segments and variables:

$query1 = "SELECT * FROM users WHERE status = ?";
$query2 = " AND created_at > ?";
$variables = array('active', '2023-01-01');

// Execute the combined query
$this->db->query($query1 . $query2, $variables);

// Retrieve the last executed SQL
$sql = $this->db->last_query();
echo $sql; 

This method returns the raw SQL string exactly as it was passed to mysqli_stmt_prepare or the underlying MySQL driver. It is a synchronous operation, meaning you can only call this after the database connection has successfully processed the command. If no query has been run in that specific session context, attempting to fetch the last query may result in an empty string or throw an error depending on the specific version of the CodeIgniter engine and the underlying driver configuration.

The Limitation of Client-Side Inspection

It is crucial to understand that last_query() only exposes what your PHP application sent to the database server. It does not reveal what MySQL actually executed if any modifications occurred at the server level, such as triggers or stored procedures, though in standard SELECT/INSERT/UPDATE operations, the client string usually matches the server execution plan.

A common pitfall occurs when dealing with dynamic queries constructed from user input. While last_query() allows you to inspect the final string, it does not inherently validate safety against SQL injection unless your application layer has already sanitized inputs or used prepared statements effectively. In the example above, using placeholders (?) ensures that $variables are treated as data, not executable code, regardless of what is inside them.

Leveraging Server-Side Logging for Deep Analysis

If your goal extends beyond simple inspection to auditing or debugging performance bottlenecks, relying solely on PHP's last_query() output might be insufficient. You may need to know exactly how MySQL parsed the query, which tables were accessed, and what execution plan was chosen. For this, you must look at the server logs.

To capture every query executed against your database instance, including those triggered by external connections or application servers like Apache or Nginx, you should configure the MySQL general log. This is distinct from the slow query log, which only captures queries exceeding a specific runtime threshold.

Enable the general log by adding the following to your my.cnf or my.ini configuration file:

[mysqld]
general_log = 1
general_log_file = /var/log/mysql/query.log

Once enabled, MySQL records every query string to the specified file. This approach works identically on both Windows and Linux environments, provided the log path is valid and writable. You can then grep through /var/log/mysql/query.log or %TEMP%/query.log (on Windows) to find the specific timestamp corresponding to your PHP request.

grep "INSERT INTO" /var/log/mysql/query.log | tail -n 10

While this adds overhead to disk I/O, it is invaluable when analyzing complex multi-step transactions or when debugging issues where the application logs do not match database behavior. However, be cautious: logging every query can significantly impact performance on high-traffic sites. It is often better to keep this enabled only during development or staging phases.

Best Practices for Query Inspection

When deciding between client-side retrieval and server-side logging, weigh the trade-offs carefully. Using $this->db->last_query() keeps the inspection logic within your application code, making it easy to assert that a query ran before proceeding. It is zero-cost in terms of server resources once the connection is established.

Server-side logging provides an immutable record but introduces latency and storage considerations. If you need to debug why a specific dynamic query failed without modifying the code or configuration permanently, start with last_query(). If you suspect a systemic issue affecting multiple endpoints or require SQL audit trails for compliance, configure the general log temporarily. Always remember that inspecting queries should not replace proper input validation; the primary defense against security vulnerabilities remains parameterized queries and prepared statements.

Our network