One thing I really like about CakePHP is that when it’s in debug mode it prints out a list of all the database queries used to generate a page, along with the time it took to run each query. This makes it really easy to find problem queries that are slowing down your site. It’s such a great feature that I wanted something similar for projects that don’t use CakePHP.

What I came up with was a LoggedPDO class that extends PHP’s PDO class. If you’re already using PDO in your project you only need to change a single line of code to enable database query logging:

// Standard PDO...
$db = new PDO($dsn, $username, $password);
// ...should be changed to
$db = new LoggedPDO($dsn, $username, $password);

And then all your database queries will be logged, timed and all information printed out at the end of each page. My code also includes a decorator for PDOStatements so any prepared statements that get executed also get logged. Below is a small sample of the code in use:

$db = new LoggedPDO("mysql:dbname=test;host=localhost", "root");
$results = $db->query("SHOW TABLES");
$tables = $results->fetchAll(PDO::FETCH_COLUMN);
foreach($tables AS $table) {
	$statement = $db->prepare("DESCRIBE :table");
	$statement->bindParam(":table", $table, PDO::PARAM_STR);
	$results = $statement->execute();
	$columns = $statement->fetchAll(PDO::FETCH_ASSOC);
}

Which would output:

Full code for the both the LoggedPDO and LoggedPDOStatement classes is below:

/**
* Extends PDO and logs all queries that are executed and how long
* they take, including queries issued via prepared statements
*/
class LoggedPDO extends PDO
{
	public static $log = array();

	public function __construct($dsn, $username = null, $password = null) {
		parent::__construct($dsn, $username, $password);
	}

	/**
	 * Print out the log when we're destructed. I'm assuming this will
	 * be at the end of the page. If not you might want to remove this
	 * destructor and manually call LoggedPDO::printLog();
	 */
	public function __destruct() {
		self::printLog();
	}

	public function query($query) {
		$start = microtime(true);
		$result = parent::query($query);
		$time = microtime(true) - $start;
		LoggedPDO::$log[] = array('query' => $query,
				                  'time' => round($time * 1000, 3));
		return $result;
	}

	/**
	 * @return LoggedPDOStatement
	 */
	public function prepare($query) {
		return new LoggedPDOStatement(parent::prepare($query));
	}

	public static function printLog() {
		$totalTime = 0;
		echo '<table border=1><tr><th>Query</th><th>Time (ms)</th></tr>';
		foreach(self::$log as $entry) {
			$totalTime += $entry['time'];
			echo '<tr><td>' . $entry['query'] . '</td><td>' . $entry['time'] . '</td></tr>\n';
		}
		echo '<tr><th>' . count(self::$log) . ' queries</th><th>' . $totalTime . '</th></tr>\n';
		echo '</table>';
	}
}

/**
* PDOStatement decorator that logs when a PDOStatement is
* executed, and the time it took to run
* @see LoggedPDO
*/
class LoggedPDOStatement {
	/**
	 * The PDOStatement we decorate
	 */
	private $statement;

	public function __construct(PDOStatement $statement) {
		$this->statement = $statement;
	}

	/**
	* When execute is called record the time it takes and
	* then log the query
	* @return PDO result set
	*/
	public function execute() {
		$start = microtime(true);
		$result = $this->statement->execute();
		$time = microtime(true) - $start;
		LoggedPDO::$log[] = array('query' => '[PS] ' . $this->statement->queryString,
				                  'time' => round($time * 1000, 3));
		return $result;
	}
	/**
	* Other than execute pass all other calls to the PDOStatement object
	* @param string $function_name
	* @param array $parameters arguments
	*/
	public function __call($function_name, $parameters) {
		return call_user_func_array(array($this->statement, $function_name), $parameters);
	}
}

jQuery UI is an excellent add-on library for jQuery that provides all sorts of UI widgets, effects and behaviours. One particularly useful function is provides is to make any element on the page draggable, so that it can be moved about with the mouse.

Because of all the features that jQuery UI provides the library is fairly large, about 450KB uncompressed. It is possible to only include specific parts of the library, but even the bare minimum required for the draggables feature weighs in at just over 40KB uncompressed.

Therefore I decided to write my own lightweight draggable implementation using only jQuery. The code is shown below:

// Make an element draggable using jQuery
var makeDraggable = function(element) {
	element = jQuery(element);

	// Move the element by the amount of change in the mouse position
	var move = function(event) {
		if(element.data('mouseMove')) {
			var changeX = event.clientX - element.data('mouseX');
			var changeY = event.clientY - element.data('mouseY');

			var newX = parseInt(element.css('left')) + changeX;
			var newY = parseInt(element.css('top')) + changeY;

			element.css('left', newX);
			element.css('top', newY);

			element.data('mouseX', event.clientX);
			element.data('mouseY', event.clientY);
		}
	}

	element.mousedown(function(event) {
		element.data('mouseMove', true);
		element.data('mouseX', event.clientX);
		element.data('mouseY', event.clientY);
	});

	element.parents(':last').mouseup(function() {
		element.data('mouseMove', false);
	});

	element.mouseout(move);
	element.mousemove(move);
}

The element you pass to makeDraggable should already have absolute or fixed CSS positioning, and a top and left CSS value set. Something like the following:

<div id='draggable' style='position: absolute; top: 100; left: 200; padding: 50px; background: black; border: 2px solid #aaa;'>
This box is draggable!
</div>
<script type="text/javascript">
makeDraggable(jQuery('#draggable'));
</script>

Then just click on the div and move the mouse to drag it around.

Obviously it isn’t as flexible as the jQuery UI version, but it is a useful alternative if you want to save on the amount of data your user’s will need to download and you don’t require any other jQuery UI features.