Code Igniter : Log all queries

Developing applications in Code Igniter is fun and easy, as we do not have to write much of the actual queries because of its Active Record Implementation. Moreover, we can find plugins for CI which will write all the model classes for you in a click.

As we keep on developing application, we might sometimes need to keep track of the queries that have been run during each page load. It might seem minimal but while developing large enterprise applications, even the smallest optimizations will speed up things a lot.

Below you’ll find a CodeIgniter hook that logs all database queries to a simple text file.

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

/* application/hooks/LogQueryHook.php */
class LogQueryHook {

	function log_queries() {
		$CI => get_instance();
		$times = $CI->query_times;
		$dbs    = array();
		$output = NULL;
		$queries = $CI->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->helper('file');
		if ( ! write_file(APPPATH  . "/logs/queries.log.txt", $output, 'a+')){
			log_message('debug','Unable to write query the file');
		}
	}
}

Setting up CodeIgniter application on a sub domain can be a huge challenge. It seems to work fine on the view pages, but when we try to submit a form, or link pages, it shows a 505 error page. Our application just becomes useless when we are not able to submit a single form.

After much searching on Google, I finally found the best ideal solution to it. I am just making a copy of the answers given in the CI forum, as I think this will help solve a lot of problems.

Source: Code Igniter Forum by wiredesignz.

Make the following changes in your /system/application/config/config.php file.

$config['uri_protocol'] = "APP_PATH";

// enable query strings if you are using
// GET requests at any point of your application.
$config['enable_query_strings'] = TRUE;

RewriteEngine On  

RewriteBase /  

RewriteCond %{ENV:REDIRECT_APP_PATH} !^$
RewriteRule ^(.*)$ - [E=APP_PATH:%{ENV:REDIRECT_APP_PATH}]  

RewriteCond %{ENV:APP_PATH} ^$
RewriteRule ^(.*)$ - [E=APP_PATH:/$1]  

RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule ^(.*)$ index.php [L]

Tired of writing Stored Procedures for regular CRUD operations? Here is useful PHP script, which generates the script for the required stored procedures. This code studies the table structure and creates the stored procedures accordingly.

<?php

$tableName = "Your Desired Table";

// Connection String
$conn = mysql_connect('localhost', 'root','');

// Select database
mysql_select_db('<DBNAME>', $conn);

$result = mysql_query('select * from `'.$tableName.'`');

$paramList = '(<br/>';

$tableIdField = mysql_field_name($result, 0);

$insertFields = "(";
$insertValues = "(";

$updateList = "";

for($i=0;$i< mysql_num_fields($result);$i++){
     $fieldName = mysql_field_name($result, $i);
     $fieldType = mysql_field_type($result, $i);

     if($i>0){
          $insertFields = $insertFields.$fieldName.',';
          $insertValues = $insertValues.' _'.$fieldName.',';
     }

     $updateList = $updateList.' '.$fieldName.'='.' _'.$fieldName.',';

     $fieldFormat = '';
     $fieldFormat = $fieldFormat.' _'.$fieldName;
     $fieldFormat = $fieldFormat.' '.$fieldType;
     $fieldFormat = $fieldFormat.' '.',<br/>';

     $paramList = $paramList.' '.$fieldFormat;
}

$insertFields = $insertFields.')';
$insertValues = $insertValues.')';

$updateList = $updateList.')';

$paramList = $paramList.')<br/>';
$paramList = str_replace('int','int(11)',$paramList);
$paramList = str_replace('string','text',$paramList);
$paramList = str_replace(',)',')',$paramList);

$insertFields = str_replace(',)',')',$insertFields);
$insertValues = str_replace(',)',')',$insertValues);

$updateList = str_replace(',)','',$updateList);

$spText = '<br/><br/>DELIMITER
lt;br/>';

$spText .= ' <br/>DROP PROCEDURE IF EXISTS Manage'.$tableName.'
lt;br/>';

$spText .= ' <br/>CREATE PROCEDURE Manage'.$tableName.'<br/>';

$spText .= $paramList;

$spText .= ' <br/>BEGIN<br/>';

$spText .= ' <br/>IF _'.mysql_field_name($result, 0).' = 0 THEN<br/>';

$spText .= ' INSERT INTO '.$tableName.' '.$insertFields;

$spText .= ' VALUES '.$insertValues.';';

$spText .= '<br/> ELSE<br/>';

$spText .= ' UPDATE '.$tableName.' SET '.$updateList;

$spText .= ' WHERE '.$tableIdField.'=_'.$tableIdField.';';

$spText .= ' <br/>END IF;<br/>';

$spText .= ' <br/>END
lt;br/>';

$spText .= ' <br/>DELIMITER ;<br/>';
echo '<br/><strong>Insert/Update stored Procedure: </strong><br/>';
echo $spText;

$spText='';
echo '<br/><br/><br/><br/>';
$spText = '<br/><br/>DELIMITER
lt;br/>';

$spText .= ' <br/>DROP PROCEDURE IF EXISTS Read'.$tableName.'
lt;br/>';

$spText .= ' <br/>CREATE PROCEDURE Read'.$tableName.'<br/>';

$spText .= '(_id int(11))';

$spText .= ' <br/>BEGIN<br/>';

$spText .= ' <br/>IF _id = 0 THEN<br/>';

$spText .= ' SELECT * FROM '.$tableName.';';

$spText .= '<br/> ELSE<br/>';

$spText .= ' SELECT * FROM '.$tableName.' WHERE ID = _ID;';

$spText .= ' <br/>END IF;<br/>';

$spText .= ' <br/>END
lt;br/>';

$spText .= ' <br/>DELIMITER ;<br/>';
echo '<br/><strong>Select All/specific record stored Procedure: </strong><br/>';
echo $spText;
?>

Here is a simple PHP based calender script for your website.Simply copy and paste the code and get it working.

The script generates a calender widget,can be styled through CSS as desired and next and previous months can be navigated.

https://github.com/sajanm/php-calendar/