Explain Codes LogoExplain Codes Logo

How to print SQL statement in a CodeIgniter model

sql
database-debugging
query-logging
profiler-class
Alex KataevbyAlex Kataev·Oct 31, 2024
TLDR

Unmask your SQL queries in CodeIgniter using $this->db->last_query(). Invoke it immediately after your query to print the accurate SQL command.

$query = $this->db->get('your_table'); echo $this->db->last_query(); // Outputs: SELECT * FROM your_table

This chunk of code echoes the last SQL operation after fetching data from your_table, providing you the actual SQL command used.

Advanced troubleshooting techniques

For a more granular insight into your SQL queries, CodeIgniter's Profiler Class comes to the rescue. It not only unearths SQL commands but also provides detailed benchmarks you can use for performance optimization.

Enable the profiler in your Controller using this code:

$this->output->enable_profiler(TRUE); // Ninja mode activated!

Once you do this, you’ll see a complete performance report at the bottom of your page - including the queries run and their execution time.

If you’re dealing with a sea of queries and want to isolate just one of them, you can access the queries array directly:

echo $this->db->queries[0]; // First one to the scene of the crime!

Peeping into the DB's secrets

Ever wanted a sneak peek into your database’s mind? With var_dump($this->db), you get a tour of the db object's 'queries' property, exposing the record of all SQL commands run during the request.

Should you not see any output, it’s time to double-check your database setup and SQL logic for unnoticed errors or typos that may be masquerading.

Digging deeper into the DB's inner workings

Analyzing queries

For a critical bug investigation, you may need to trace the steps leading up to the query execution. Use echo $this->db->get_compiled_select('your_table'); to examine the query before its execution.

Query logging

In a production setting, it can be more fruitful to log queries rather than printing them on the page. For this, integrate the log_message function:

log_message('error', 'Query failed: ' . $this->db->last_query()); // This is less fun than expected!

This approach means logging data directly to a file, allowing for simpler inspection without disrupting user experience.

Hoisting the database veil

For learning or debugging purposes, var_export($query->result_array(), true) could be useful to see the results of a query. Also, $this->db->version() tells you the version of your database - knowledge that might help tackle those tricky issues.