115
votes

I have a sql statement in my model,

I then say

$query = $this->db->query($sql, array(fields, fields1);

if ($query) {
    return true:
} else {
    echo "failed";
    return false;
}

My query always fails, how do I get php to print the exact sql statement being sent to my database? And display that on my php view, page

15

15 Answers

147
votes

To display the query string:

print_r($this->db->last_query());    

To display the query result:

print_r($query);

The Profiler Class will display benchmark results, queries you have run, and $_POST data at the bottom of your pages. To enable the profiler place the following line anywhere within your Controller methods:

$this->output->enable_profiler(TRUE);

Profiling user guide: https://www.codeigniter.com/user_guide/general/profiling.html

230
votes

You can use this:

$this->db->last_query();

"Returns the last query that was run (the query string, not the result)."

Reff: https://www.codeigniter.com/userguide3/database/helpers.html

41
votes

You can display the ActiveRecord generated SQL:

Before the query runs:

$this->db->_compile_select(); 

And after it has run:

$this->db->last_query(); 
17
votes

if you need a quick test on your query, this works great for me

echo $this->db->last_query(); die;
14
votes

After trying without success to use _compiled_select() or get_compiled_select() I just printed the db object, and you can see the query there in the queries property.

Try it yourself:

var_dump( $this->db );

If you know you have only one query, you can print it directly:

echo $this->db->queries[0];
10
votes

There is a new public method get_compiled_select that can print the query before running it. _compile_select is now protected therefore can not be used.

echo $this->db->get_compiled_select(); // before $this->db->get();
9
votes

You can simply use this at the end..

echo $this->db->last_query();
2
votes

Neither last_query() or get_compiled_select() works for me, so a slight change of pedro's code works for me just fine. Do not include ->get() in your build, this must be before the ->get()

 echo $this->EE->db->_compile_select();
1
votes

I try to @Chumillas's answer and @chhameed's answer, but it not work,because the sql is wrong.So I found new approach,like this:

  • Insert echo $sql; flush(); exit; into before return $sql; _compile_select function of DB_active_rec.php
1
votes

Add this line right after the query you want to print.

Example:

$query = $this->db->query('SELECT * FROM table WHERE condition');

//Add this line.

var_dump($this->db->last_query());

exit();

or

echo $this->db->last_query();

0
votes

I'm using xdebug for watch this values in VSCode with the respective extension and CI v2.x. I add the expresion $this->db->last_query() in the watch section, and I add xdebugSettings node like these lines for get non truncate value in the launch.json.

{
  "name": "Launch currently open script",
  "type": "php",
  "request": "launch",
  "program": "${file}",
  "cwd": "${fileDirname}",
  "port": 9000,
  "xdebugSettings": {
    "max_data": -1,
    "max_children": -1
  }
},

And run my debuger with the breakpoint and finally just select my expresion and do click right > copy value.

0
votes

In CodeIgniter4, you do this:

$db = \Config\Database::connect();  
// your queries here
$query = $db->getLastQuery();
$sql = $query->getQuery();
echo $sql;
-1
votes

I read all answers here, but cannot get

echo $this->db->get_compiled_select();

to work, It gave me error like,

Call to protected method CI_DB_active_record::_compile_select() from context 'Welcome'in controllers on line xx

So i removed protected from the below line from file \system\database\DB_active_rec.php and it worked

protected function _compile_select($select_override = FALSE)
-1
votes

I had exactly the same problem and found the solution eventually. My query runs like:

$result = mysqli_query($link,'SELECT * FROM clients WHERE ' . $sql_where . ' AND ' . $sql_where2 . ' ORDER BY acconame ASC ');

In order to display the sql command, all I had to do was to create a variable ($resultstring) with the exact same content as my query and then echo it, like this:<?php echo $resultstring = 'SELECT * FROM clients WHERE ' . $sql_where . ' AND ' . $sql_where2 . ' ORDER BY acconame ASC '; ?>

It works!

-1
votes

use get_compiled_select() to retrieve query instead of replace it