Getting raw SQL query built from ActiveRecord in CodeIgniter 1.7

Question

I'm trying to debug some code in my first serious CodeIgniter app and I can't seem to find where I can simply get the raw SQL that my ActiveRecord code just generated.

$where  = 'DAY(`datetime_start`) = '. date('d',$day) .' AND ';
$where .= 'MONTH(`datetime_start`) = '. date('m',$day) .'';

$this->db->from('events')->where($where);
$result = $this->db->get();

hipertracker · Accepted Answer · 2011-07-13 09:27:46Z

45

Before the query runs:

$this->db->_compile_select();

And after it has run:

$this->db->last_query();

answered Jul 13, 2011 at 9:27

hipertracker

2,45727 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Browne Over a year ago

_compile_select() is marked as protected in more recent versions of CodeIgniter (it likely only wasn't declared protected before for PHP4 compatibility). So you could just set the limit to 1 to avoid executing a big query and then call last_query(), or you could use reflection but the first option is preferable.

Matt Browne Over a year ago

Also: It looks like get_compiled_select() will be available in a future version of CodeIgniter; it's already in the dev branch (github.com/EllisLab/CodeIgniter/blob/develop/system/database/…). I copied that method to DB_active_rec and it worked fine, even in conjunction with this library: github.com/NTICompass/CodeIgniter-Subqueries

bkorte · Accepted Answer · 2009-10-02 04:21:57Z

10

Of course, I found it 2 minutes after posting, courtesy of Phil Sturgeon.

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

answered Oct 2, 2009 at 4:21

bkorte

7311 gold badge6 silver badges13 bronze badges

Comments

John McCollum · Accepted Answer · 2009-10-03 22:30:30Z

8

Also, you can put the following in your controller:

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

You'll get queries and a lot more.

answered Oct 3, 2009 at 22:30

John McCollum

5,1524 gold badges37 silver badges50 bronze badges

Comments

Ukuser32 · Accepted Answer · 2016-01-19 11:12:35Z

1

For anyone finding this old post and wondering what this is in the latest v3 the function is $this->db->get_compiled_select().

answered Jan 19, 2016 at 11:12

Ukuser32

2,1893 gold badges23 silver badges33 bronze badges

Comments

Coanda · Accepted Answer · 2025-06-25 10:16:21Z

In Codeigniter 3, the last query can be obtained via:

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

It is only possible to get the last query when the option save_queries is enabled in application/config/database.php:

/*
|   ['save_queries'] TRUE/FALSE - Whether to "save" all executed queries.
|               NOTE: Disabling this will also effectively disable both
|               $this->db->last_query() and profiling of DB queries.
|               When you run a query, with this setting set to TRUE (default),
|               CodeIgniter will store the SQL statement for debugging purposes.
|               However, this may cause high memory usage, especially if you run
|               a lot of SQL queries ... disable this to avoid that problem.
*/

Source: Codeigniter 3 Query Helper Methods

Babak Bandpay · Accepted Answer · 2012-03-13 15:20:39Z

-2

You can make something like this until CI's next version is released

private function get_query_string()
{
    return  'SELECT ' . 
            implode( ' , ' , $this->db->ar_select ) .
            ' FROM ' .
            implode( ' , '  , $this->db->ar_from ) .
            ( count( $this->db->ar_where ) ? ' WHERE ' : '' ) .
            implode( ' ' , $this->db->ar_where );
}

answered Mar 13, 2012 at 15:20

Babak Bandpay

1776 silver badges15 bronze badges

1 Comment

Matt Over a year ago

This is very unlikely to yield accurate results as to what query is actually being run

Collectives™ on Stack Overflow

Getting raw SQL query built from ActiveRecord in CodeIgniter 1.7

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related