CODEWITHSUNDEEP
phpsqlcodeigniter
Technupe
Technupe

Reputation: 4961

How to print SQL statement in codeigniter model

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

Upvotes: 131

Views: 401111

Answers (17)

sarath
sarath

Reputation: 343

$query->getCompiledSelect() worked for me.

You can check system\Database\BaseBuilder.php and see which function returns sql query as string. Found this in there and it worked for me.

 /**
     * Get SELECT query string
     *
     * Compiles a SELECT query string and returns the sql.
     *
     * @param boolean $reset TRUE: resets QB values; FALSE: leave QB values alone
     *
     * @return string
     */
    public function getCompiledSelect(bool $reset = true): string
    {
        $select = $this->compileSelect();
    
        if ($reset === true)
        {
            $this->resetSelect();
        }
    
        return $this->compileFinalQuery($select);
    }

and in system\Database\Query.php there is getQuery(). Either of this might be useful.

/**
     * Returns the final, processed query string after binding, etal
     * has been performed.
     *
     * @return string
     */
    public function getQuery(): string
    {
        if (empty($this->finalQueryString))
        {
            $this->finalQueryString = $this->originalQueryString;
        }

        $this->compileBinds();

        return $this->finalQueryString;
    }

Upvotes: 0

hitesh israni
hitesh israni

Reputation: 1752

In codeigniter 4 I use below code to echo the query. This comes handy especially to see incorrect database queries

use CodeIgniter\Events\Events; 

// listen to the event before $builder->get() or $model->find()

Events::on('DBQuery', function($query){
    log_message('info', $query);
});

This event is triggered whenever a new query has been run, whether successful or not.

Refer official doc on DBQuery Event here.

Upvotes: 1

Loreto Gabawa Jr.
Loreto Gabawa Jr.

Reputation: 2056

In CodeIgniter4, you do this:

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

Upvotes: 5

uescamilla
uescamilla

Reputation: 47

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.

Upvotes: 0

larp
larp

Reputation: 1067

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();

Upvotes: 2

Otto
Otto

Reputation: 4190

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

Upvotes: 167

user2049634
user2049634

Reputation: 41

use get_compiled_select() to retrieve query instead of replace it

Upvotes: 1

chhameed
chhameed

Reputation: 4456

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

Upvotes: 242

Mark Janssen
Mark Janssen

Reputation: 1

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!

Upvotes: -2

Programmer
Programmer

Reputation: 307

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

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

Upvotes: 17

Muhammad Sadiq
Muhammad Sadiq

Reputation: 1155

You can simply use this at the end..

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

Upvotes: 9

Laurence Cope
Laurence Cope

Reputation: 422

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();

Upvotes: 2

viral
viral

Reputation: 3743

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)

Upvotes: 0

Christian Dechery
Christian Dechery

Reputation: 876

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];

Upvotes: 14

Chanble
Chanble

Reputation: 11

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

Upvotes: 1

Naveed
Naveed

Reputation: 1221

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();

Upvotes: 15

pedro
pedro

Reputation: 504

You can display the ActiveRecord generated SQL:

Before the query runs:

$this->db->_compile_select();

And after it has run:

$this->db->last_query();

Upvotes: 42

Related Questions