Outputting SQL information is incredibly valuable when you’re trying to debug your app. If you’ve ever used CakePHP then you’ve seen the query output appear at the bottom of your page.

I’ve whipped up a little class that allows you to:

  • See the query outputted as a string
  • See what file the query was called from
  • See what line the query was called on
  • How long it took to run that query
  • The total time to run all the queries on your page

sql-log

Make a file called lib.database.php and put the following code in it.

<?php
class Database {
 
	protected static $queries = array();
	protected static $total = 0;
 
	/**
	* Return the categories in an array
	*
	* @param string $sql The SQL query you are passing in to be executed
	* @return resource
	*/
	public static function query($sql){
 
		// get the amount of time it took to run the query
		$start = microtime(true);
		$q = mysqli_query($sql);
		$end = microtime(true);
		$difference = substr(($end - $start), 0, 5);
 
		// get the information of where it was called from
		// you can adjust the array key of $backtrace depending on the depth of your debug
		$backtrace = debug_backtrace();
		$line = $backtrace[0]['line'];
		$file = $backtrace[0]['file'];
 
		self::$total += $difference;
		self::$queries['queries'][] = array(
			'sql' => $sql, 
			'time' => $difference, 
			'file' => $file, 
			'line' => $line
		);
 
		return $q;
 
	}
 
	/**
	* Get all the queries and tack on the total amount of time it took to spit all of them out
	*
	* @return array
	*/
	public static function getQueries(){
 
		self::$queries['total'] = self::$total;
		return self::$queries;
 
	}
 
 
}

Now make a new page and include the file with your other queries. Instead of running mysqli_query($sql), run Database::query($sql). 

require_once('lib.database.php');
 
// This should look familiar to you...
$sql = 'SELECT * FROM table WHERE chairs = 1';
$query = Database::query($sql);
while($data = mysql_fetch_assoc($query)){
	// do whatever you normally do in this loop
}
 
// Now to output the SQL log
$queries = Database::getQueries();
foreach($queries as $query){
	print_r($query); // style this for output.. do whatever you want - it's an array!
}

Now wasn’t that easy?