How to print SQL statement in codeigniter model

Question

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

chhameed · Answer 1 · 2017-07-18T07:34:08.967

229

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

edited Jul 18 '17 at 07:34

answered May 27 '11 at 11:59

chhameed

4,270
4
24
44

score 146 · Accepted Answer · edited Feb 28 '19 at 13:26

146

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

edited Feb 28 '19 at 13:26

Community

1
1

answered May 26 '11 at 16:49

Novo

3,601
4
32
45

8

when i do print_r($query); nothing prints out at all – Technupe May 26 '11 at 16:50
1

Use the CI built in profiler, more info [here](http://codeigniter.com/user_guide/general/profiling.html) – Novo May 26 '11 at 16:51
2

all i want to do is print out the sql statement as passed to the database, sory but the profiler isn't helping much neither – Technupe May 26 '11 at 16:52
Visit the user guide link I posted, if it is not working, tell us what happens instead. – Novo May 26 '11 at 16:58
well i figured my problem, oracle isn't accepting my date format, ive tried every format i think, it will only accept SYSDATE – Technupe May 26 '11 at 17:16
This seems to output the result of the query, rather than the query itself. – Owain Reed Feb 25 '15 at 13:48
thanks to share this idea. – Amranur Rahman Feb 10 '21 at 16:43

score 41 · Answer 3 · answered May 26 '11 at 21:02

41

You can display the ActiveRecord generated SQL:

Before the query runs:

$this->db->_compile_select();

And after it has run:

$this->db->last_query();

answered May 26 '11 at 21:02

pedro

494
4
9

4

when i use $this->db->_compile_select(); i get Fatal error: Call to protected method CI_DB_active_record::_compile_select() from – Angelin Nadar Dec 19 '12 at 13:41
The profiler thing is no good for me, does not show the query I want, its over complicated to just get the SQL... This works for me:- echo $this->EE->db->_compile_select(); – Laurence Cope Oct 07 '13 at 13:30
3

In CI3, Use `$this->db->get_compiled_select()` instead. – Nick Tsai Nov 30 '17 at 02:55

score 17 · Answer 4 · answered Jan 13 '16 at 14:05

17

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

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

answered Jan 13 '16 at 14:05

Programmer

287
3
14

score 14 · Answer 5 · answered Apr 02 '14 at 19:26

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

score 10 · Answer 6 · answered May 16 '13 at 06:22

10

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

answered May 16 '13 at 06:22

Naveed

1,161
10
22

4

Fatal error: Call to undefined method CI_DB_mysql_driver::get_compiled_select() – itskawsar Sep 25 '13 at 08:53

score 9 · Answer 7 · edited Dec 15 '15 at 06:38

9

You can simply use this at the end..

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

edited Dec 15 '15 at 06:38

Saty

21,683
7
29
47

answered Aug 01 '15 at 10:10

Muhammad Sadiq

1,081
11
11

score 2 · Answer 8 · edited Aug 01 '15 at 06:52

2

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

edited Aug 01 '15 at 06:52

Saty

21,683
7
29
47

answered Oct 07 '13 at 13:34

Laurence Cope

353
5
17

score 1 · Answer 9 · answered Oct 26 '13 at 14:42

1

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

answered Oct 26 '13 at 14:42

Chanble

11
2

larp · Answer 10 · 2019-04-03T02:29:08.993

1

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

edited Apr 03 '19 at 02:29

answered Nov 07 '18 at 03:10

larp

679
10
18

score 0 · Answer 11 · answered Feb 15 '20 at 09:21

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.

score -1 · Answer 12 · answered Mar 15 '16 at 09:43

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!

This is not the CodeIgniter way. – mickmackusa Apr 06 '20 at 21:21 — mickmackusa, Apr 06 '20 at 21:21

score -1 · Answer 13 · edited Jan 26 '18 at 16:23

-1

use get_compiled_select() to retrieve query instead of replace it

edited Jan 26 '18 at 16:23

YakovL

5,213
10
46
71

answered Jan 26 '18 at 11:18

user2049634

1

I'd suggest to show how to use the function you propose in this particular case, not just mention it. Best regards – YakovL Jan 26 '18 at 16:24

score -2 · Answer 14 · answered Jun 24 '15 at 10:07

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)

How to print SQL statement in codeigniter model

14 Answers14

Linked