28

I am looking for a way to see generated string of the query but without executing it.

Note that the query hasn't been executed before. (I do not want $this->db->last_query();)

I hope there be a method with a name like $this->db->echo_query_string($table_name = ''); to be used exactly like $this->db->get($table_name = ''); BUT THE ONLY DIFFERENCE BE THAT get() executes the code, but echo_query_string() just echoes the string of query without execution.

Mohammad Naji
  • 5,372
  • 10
  • 54
  • 79
  • 1
    A simple - but probably not so effective - way I would do is slightly modify the query to make an erroneous query such that the system displays the error along with the simple expected error & it will not be executed. – maan81 Sep 27 '14 at 15:27

6 Answers6

29

You can see the compiled query by either of these functions

/* SELECT */ $this->db->_compile_select();
/* INSERT */ $this->db->_insert();
/* UPDATE */ $this->db->_update();
stealthyninja
  • 10,343
  • 11
  • 51
  • 59
Stewie
  • 3,103
  • 2
  • 24
  • 22
  • Thank you. Does that work just for select queries? Isn't there anything exactly instead of `get()`? I hope it be able it to have an optional $database_name like `get()` – Mohammad Naji Jan 25 '12 at 21:01
  • For insert its $this->_insert() for update its $this->_update() But these two require few arguments – Stewie Jan 25 '12 at 21:04
  • Is there a document about describing the methods you mentioned? I want to know exactly the parameters they accept. I think it hardly has such a doc. Should I know more details by only looking at the code? – Mohammad Naji Jan 25 '12 at 21:07
  • 22
    In the newest CodeIgniter version `$this->db->_compile_select()` is protected, and cannot be called. You need to use `$this->db->get_compiled_select()` (which may or not be in the latest stable version, it's in their dev version). – gen_Eric Jan 25 '12 at 21:22
  • 2
    You also need to use `get_compiled_insert` and `get_compiled_update` instead. – gen_Eric Jan 25 '12 at 21:22
  • @Rocket Thank you very much. Do you mean version 2.0.3 that is currently the most stable version? – Mohammad Naji Jan 25 '12 at 21:28
  • 1
    @smhnaji: I think `_compile_select` still works in 2.0.3, but that's not the most stable. The most stable version is 2.1.0 which removes `_compile_select`. They added `get_compiled_select`, but that's not in the stable version, it's only in the [development version](https://github.com/EllisLab/CodeIgniter). – gen_Eric Jan 25 '12 at 21:33
18

You don't need to change any file in codeigniter because it already provides a method to do that.

Using

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

will produce

select * from some_table...

And this is it.

ssube
  • 47,010
  • 7
  • 103
  • 140
BrCoder08
  • 391
  • 2
  • 2
  • why -ve votes? Answer seemed perfect to me. Most probably down votes are not for discouragement so what's missing is comment. – Sami Dec 26 '13 at 21:27
  • 24
    -ve votes because the asker doesn't want to run the actual query. Just wants what the query will look like. last_query() gets the last query sent to the DB. – scotsqueakscoot Dec 27 '13 at 16:33
  • 18
    Down votes are because the question specifically said NO last_query(), and to top it all off - the "answer" starts with "tsc, tsc". Arrogant attitude, and wrong answer. Hard not to down vote, really. – d-wade Aug 06 '14 at 15:06
  • 3
    `$this->db->last_query()` is good to see the query AFTER execution. OP wants to see the compiled query before and without executing it. – sotoz Oct 09 '15 at 07:36
  • The op asked to show the SQL string without running the sql against the database. You can only use $this->db->last_query() after running get(), which will run the query against the database. – PrestonDocks Jan 11 '17 at 09:25
  • This post is the correct answer to the wrong question. Upvoters are not considering the impact on future researchers. Just because "it worked for you" doesn't mean that it is a correct answer to the posted question. – mickmackusa Jul 29 '19 at 21:58
11

I added this little method in DB_active_rec.php

function return_query()
{
    return $this->_compile_select();
}

Usage 

$this->db->select('id,user_name')->from('user')->where('id',1);

$string =   $this->db->return_query();
echo $string;

Result

SELECT `id`, `user_name` FROM (`user`) WHERE `id` = 1

In this way you are bound to use 

$this->db->from()

Instead of 

$this->db->get()

Which runs the query

Muhammad Raheel
  • 19,823
  • 7
  • 67
  • 103
5

You can use some public methods to get SQL queries

Get a SELECT query

$sql = $this->db->get_compiled_select();

Get a INSERT query

$sql = $this->db->get_compiled_insert();

Get a UPDATE query

$sql = $this->db->get_compiled_update();

Get a DELETE query

$sql = $this->db->get_compiled_delete();
Samuel Dauzon
  • 10,744
  • 13
  • 61
  • 94
4

As of version 3 of Codeigniter, please refer to this url and also to this.

  • echo $this->db->update_string(); OR echo $this->db->get_compiled_update();
  • echo $this->db->insert_string(); OR $this->db->get_compiled_insert();
  • echo $this->db->get_compiled_delete();
  • echo $this->db->get_compiled_select();
Akash lal
  • 314
  • 6
  • 18
Amitabh Das
  • 393
  • 1
  • 6
  • 16
1

From CI 3.1.11 The below code will help you

$this->db->get_compiled_select()

Form more details visit https://codeigniter.com/userguide3/database/query_builder.html#selecting-data

Sam
  • 131
  • 1
  • 12