Query log in codeigniter using hook

Question

I need to create a query log in my project. So I created a post_controller hook. It saves all the executed queries in both a text file and a database. But it works only for SELECT queries. I know it is repeated question, but after a lot of search, I couldn't find solution.

Here is my code:

config/hooks.php:

$hook['post_controller'] = array(
    'class' => 'LogQueryHook',
    'function' => 'log_queries',
    'filename' => 'log_queries.php',
    'filepath' => 'hooks'
);

hooks/log_queries.php

class LogQueryHook {

function log_queries() {   
    $CI =& get_instance();             
    $times = $CI->db->query_times;
    //$dbs    = array();
    $output = NULL;     
    $queries = $CI->db->queries;
    //print_r($queries);
    if (count($queries) == 0){
        $output .= "no queries\n";
    }else{
        foreach ($queries as $key=>$query){
            $took = round(doubleval($times[$key]), 3);
            $CI->db->query('INSERT INTO queryLog_tbl(`query`, `executedTime`, `timeTaken`, `executedBy`) VALUES ("'.$query.'", "'.date('Y-m-d h:i:s').'", "'.$took.'","'.$CI->session->userdata('UserID').'")');
            $output .= $query . "\n";                
            $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');
    }   
}
}

and hooks enabled in my config.php : $config['enable_hooks'] = TRUE;

Vivek Pipaliya · Accepted Answer · 2017-10-26 12:29:11Z

1

You need to check your internal redirection after any modification query(Insert, Update or delete query) executed. If you put any redirect statement after modification query then it will overtake hook execution.

You can do it by overwriting the query() method in system/database/DB_driver.php

Or

Create library and call it from relevant controllers.

answered Oct 26, 2017 at 12:29

Vivek Pipaliya

4981 gold badge7 silver badges17 bronze badges

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

1 Comment

geeth Over a year ago

thank you for your suggestion. I created library for the same as you suggested.

geeth · Accepted Answer · 2017-10-27 06:23:49Z

My code skipping all queries other than SELECT because of internal redirection. So I created a library for this. I am attaching my code here. It may help someone else

application/libraries/Querylog.php

class Querylog {
    protected $CI;

    public function __construct() {        
        $this->CI =& get_instance();
    }

    function save_query_in_db() {
        $query = $this->CI->db->last_query();
        $times = $this->CI->db->query_times; 
        $time = round(doubleval($times[2]), 5);
        $this->CI->db->query('INSERT INTO queryLog_tbl(`query`, `executedTime`, `timeTaken`, `executedBy`) '
            . 'VALUES ("'.$query.'", "'.date('Y-m-d h:i:s').'", "'.$time.'","'.$this->CI->session->userdata('UserID').'")');
    }
}

load this library in your controller or autoload.php

and call save_query_in_db() where ever you want

eg: in model :

$this->db->set('status', 1);
$this->db->where('UserID', $this->session->userdata('UserID'));
$this->db->update('user_tbl');
$this->querylog->save_query_in_db();

Collectives™ on Stack Overflow

Query log in codeigniter using hook

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related