Whenever I use an ORM, such as CodeIgniter’s Active Record implementation, I find it very important to see the actual queries generated. Mainly because the danger of using an ORM is not knowing what happens in the background, which can introduce hard to find bugs and performance problems.
Below you’ll find a CodeIgniter hook that logs all database queries to a simple text file. I found this code useful in my first CodeIgniter project (since it’s from my first CI project, I think many revisions will follow, but you’ll get the idea).
/* config/hooks.php */
$hook['post_system'][] = array(
'class' => 'QueryLogHook',
'function' => 'log_queries',
'filename' => 'QueryLogHook.php',
'filepath' => 'hooks'
/* application/hooks/QueryLogHook.php */
class QueryLogHook {
function log_queries() {
$CI =& get_instance();
$times = $CI->db->query_times;
$dbs = array();
$output = NULL;
$queries = $CI->db->queries;
if (count($queries) == 0)
{
$output .= "no queries\n";
}
else
{
foreach ($queries as $key=>$query)
{
$output .= $query . "\n";
}
$took = round(doubleval($times[$key]), 3);
$output .= "===[took:{$took}]\n\n";
}
$CI->load->helper('file');
if ( ! write_file(APPPATH . "/logs/queries.log.txt", $output, 'a+'))
{
log_message('debug','Unable to write query the file');
}
}
}
$hook['post_system'][] = array(
'class' => 'QueryLogHook',
'function' => 'log_queries',
'filename' => 'QueryLogHook.php',
'filepath' => 'hooks'
/* application/hooks/QueryLogHook.php */
class QueryLogHook {
function log_queries() {
$CI =& get_instance();
$times = $CI->db->query_times;
$dbs = array();
$output = NULL;
$queries = $CI->db->queries;
if (count($queries) == 0)
{
$output .= "no queries\n";
}
else
{
foreach ($queries as $key=>$query)
{
$output .= $query . "\n";
}
$took = round(doubleval($times[$key]), 3);
$output .= "===[took:{$took}]\n\n";
}
$CI->load->helper('file');
if ( ! write_file(APPPATH . "/logs/queries.log.txt", $output, 'a+'))
{
log_message('debug','Unable to write query the file');
}
}
}
I like your idea, I just couldn’t get your hook to work.
good idea thanks !
Just what I was looking for. I’m using it to keep track of database, so I modified it to filter out select statements and write to a db table.
Thanks!
It works, but you have to make sure you have hooks turned on in config.php, also the code for config/hooks.php is missing a closing bracket and a semicolon. It should look like this:
$hook[‘post_system’][] = array(
‘class’ => ‘QueryLogHook’,
‘function’ => ‘log_queries’,
‘filename’ => ‘QueryLogHook.php’,
‘filepath’ => ‘hooks’);
This worked liked a charm thank you – just what I needed as was pulling my hair out trying to debug something!
It was helpful
Great help.
Thanks..!!
Works exactly as expected.
Good Idea,
It help me alot in a great way….
Thanks
Great, it help full
But I have some problem, it doesn’t work with DELETE statement I have no idea why
Works a treat!
Had to enable hooks in the app config, by add/changing this line in application/config/config.php:
$config[‘enable_hooks’] = TRUE;
Thanks for your post. It really helps me..
Thanks but it doesn’t work if a redirect occurs. Any help?
Thanks
Thought it woudln’t to give it a shot. I was right.
I want to log every insert, update and delete query into a table.
How to save the log in a table?
Thanks
Is just what i was lookin for.
best regards