Log Sql Queries using hooks in codeigniter 3

Here is a post on how to log queries for debugging purpose in codeigniter3

Pre requisites

• Codeigniter 3 setup
• Basic knowledge of codeigniter 3

Codeigniter is basically a php framework, that helps to build developers api’s or a full end to end web development, at ease.

CI(codeigniter) Frameworks actually helps developers save time and also provides tools and utilities out of the box.

So today we are going to look at one such utility called hooks. As the name implies , hook your data to request or response or capture request/response by calling your own defined function at a particular event (hook) like when the request is initiated or response is sent to the client .

In this post we are going to implement a feature ,that every developer needs to debug what the actual query was executed and how long it took .The answer is to log queries and the time taken to execute.

So if you are using CI , probably you must have known CI uses MVC pattern.Model, View, Controller.

Model is used for handling the database,View to output the response of a request ,Controller is used for handling the request and take appropriate actions.

CI provides an efficient way of querying database ,using query builders ,that simplifies the writing of sql query.

But as the application grows,bugs may appear and if it’s a data related issue ,we have to debug the query to actually know what data is outputting.

But if you have used query builders ,debugging the query is hard if it’s a complex one ,because the query is defined by Query Builder Class methods ,so you can’t actually just copy the whole query and run on the database.Also even if you manage to print the last query ,it’s not efficient because you might actually ending up adding one more bug ,by forgetting to comment the print statement .

So logs are always the best friend for a developer, logging queries in a text file ,not only easy to retrieve and debug but also we are able to identify what data has been passed and how much time it took.

In CI hooks ,hooks can be applied before a request is initiated that is called as pre_system hook and hooks can be applied after response sent that is termed as post_system hook.

Let’s create our custom hook that logs sql queries .

First we need to enable hooks in Ci ,by setting $config[‘enable_hooks’] variable in config.php file to TRUE.

Now to print our queries we need to set the key save_queries to TRUE in database.php file.

Now we have to define our hook ,what type of hook it is ,a pre_system or post_system ,which file to execute and which function to execute.

We define all these in a file application/config/hooks.php,we define an array variable named as hook with key representing what kind of hook it is .

Below defines

$hook[‘post_system’][] = array(‘class’ => ‘LogQueryHook’,

‘function’ => ‘log_queries’,

‘filename’ => ‘LogQueryHook.php’,

‘filepath’ => ‘hooks’);

A post_system hook ,defined by key name and an associative array,containing details like

• The class key holds the name of a class that holds the code that needs to be executed.
• The function key holds the name of the method that’ll be called upon the hook execution.
• The filename key points to the file that defines the complete hook code.
• The file path defines the directory path of the file declared under the filename key, and it’s relative to the application directory.

So our hook is defined ,now we have to create a File LogQueryHook.php and define a function log_queries that performs logging of queries once the request has finished.

<?php

date_default_timezone_set(‘Asia/Kolkata’);

class LogQueryHook

{

public function log_queries()

{

// load ci instance

$ci = &get_instance();

// time required to complete the execution of query

$times = $ci->db->query_times;

$output = null;

//to get all queries that has been executed

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

$datetime=date(‘Y-m-d H:i:s’);

$output .= “===[took:{$took}]===[datetime:: {$datetime}]\n\n”;

}

$logDirectory = APPPATH . ‘logs/’ . date(‘Y-m-d’);

// check if directory present ,if not create

if (!is_dir($logDirectory)) {

mkdir($logDirectory,0775);

}

// Log the queries to a file

$filepath = $logDirectory . ‘/SqlQuery-’ . date(‘H’) . ‘.log’;

$handle = fopen($filepath, “a+”);

fwrite($handle, $output . “\n\n”);

fclose($handle);

}

}

The above code in nutshell ,we first retrieve an instance of the CI ,using that instance we can get execution time using a database method called query_times and to get all queries that has been executed during that request ,we use queries method.

Then we loop through all the queries and store the query to a variable called output in our desired format.

Then to log that output to a file /folder,here i am using a folder pattern,i.e, create a folder in application/logs, with today’s date in format Y-m-d ,if the folder doesn’t exists, create it, then create a file in that folder ,with name format as SqlQuery-(Current Hour 24Hr format).log and append the contents of output variable to that file.

That’s it ,you have successfully created your custom hook that logs query .

Now you can create your controller and model and test it .

Below is a snapshot how the log file is created.

I have already created and tested ,you can download the complete project from the below github link.

mark38igor/ci_log_queries_hook

To read more about hooks in codeigniter

How CodeIgniter's Hook System Works

Thank you.Hope this saves your time debugging sql query.