codeNothing? Web Development from a Beginner

PHP's debug_backtrace() is a debugging tool that developers can use to trace function calls back to their origin. A lot of developers use this for PHP specific calls. I personally find this to be of most use tracking queries on the database.

Over the short period of time that I have been working with the PHP/MySQL stack, I've noticed that the best way to speed up your applications, is to optimize your hits on the database first, rather then filing through your code looking for optimizations or reducing enlarged variables.

With that, I've created a little addition to the Database.php class that tracks query calls. Here's a look at the altered class:

/**
 * Query Tracking with Backtrace
 *
 * Originally written by Jpmaster77 @ http://evolt.org/user/62759
 * Orginal Script - http://evolt.org/PHP-Login-System-with-Admin-Features
 * 
 * Altered by Corey Hart
 * Altered on May 21, 2009
 * http://www.codenothing.com
 */ 


class Database
{
	var $DB_SERVER  = "localhost"; //SERVER NAME (almost always localhost)
	var $DB_USER    = ""; //SERVER USERNAME
	var $DB_PASS    = ""; //SERVER PASSWORD
	var $DB_NAME    = ""; //SERVER DATABASE
	var $queries = array();
	var $connection; //connection to database

	/* Class Constructor */
	function Database(){
		// Make connection to the database
		$this->connection = mysql_connect($this->DB_SERVER, $this->DB_USER, $this->DB_PASS) or die("Database Connection Error.");
		mysql_select_db($this->DB_NAME, $this->connection) or die("Cant select proper database.");
	}
	
	/**
	* Performs the given query and returns true, false, or 
	* the resource identifier
	*/
	function query($q, $btrace=1){
		// Store backtrace information
		$this->storeBacktrace($q, $btrace);
		// Run the query
		return mysql_query($q, $this->connection);
	}

	/**
	* Returns the hash of a single row in a MySQL database
	*/
	function hash($q){
		$result = $this->query($q, 2);
		if (!$result || mysql_num_rows($result) != 1){
			return NULL;
		}
		return mysql_fetch_assoc($result);
	}

	/**
	 * Store the query along with it's call details
	 */ 
	function storeBacktrace($q, $btrace){
		// Grab the backtrace details
		$backtrace = debug_backtrace();

		// Add the query count
		$id = ++$this->queries['count'];

		// Up the count on multi query array(singles removed during log display)
		if (!is_array($this->queries['multi-count'][$q])){
			$this->queries['multi-count'][$q] = array(
				"count" => 1,
				"id" => "$id",
			);
		}else{
			$this->queries['multi-count'][$q]['count']++;
			$this->queries['multi-count'][$q]['id'] .= ", $id";
		}

		// Store backtrace information of query
		$this->queries[$id] = array(
			"query" => $q,
			"file" => $backtrace[$btrace]['file'],
			"line" => $backtrace[$btrace]['line'],
		);
	}

	/**
	 * Organizes the queries into a more readable format
	 */ 
	function displayLogs(){
		// Start off with the number of queries stored
		$stmt = "<h3>Numer of Queries: ".$this->queries['count']."</h3>"; 

		// Create a seperate table for multiple queries
		$stmt .= "Queries ran more than once
			<table bgcolor='#989898' cellspacing='1' cellpadding='2' style='font-size:9pt;'>
			<tr bgcolor='#d1d1d1'>
			<th>Query</th>
			<th width='25'>Count</th>
			<th>#'s</th>
			</tr>";
		foreach ($this->queries['multi-count'] as $q => $arr){
			if ($arr['count'] > 2){
				$stmt .= "<tr bgcolor='#f1f1f1'>
					<td>$q</td>
					<td>".$arr['count']."</td>
					<td>".$arr['id']."</td>
					</tr>";
			}
		}
		$stmt .= "</table><br><br>";

		// Remove the count/multi-count sections
		unset($this->queries['count'], $this->queries['multi-count']);

		// Loop through each query seperately and display its details
		$stmt .= "Backtrace Details
			<table bgcolor='#989898' cellspacing='1' cellpadding='2' style='font-size:9pt;'>
			<tr bgcolor='#d1d1d1'>
			<th>#</th>
			<th>Query</th>
			<th>File</th>
			<th>Line</th>
			</tr>";
		foreach ($this->queries as $k => $arr){
			$stmt .= "<tr bgcolor='#f1f1f1'>
				<td width='25'>$k</td>
				<td>".$arr['query']."</td>
				<td>".$arr['file']."</td>
				<td>".$arr['line']."</td>
				</tr>";
		}
		$stmt .= "</table>";

		// Return the log statement
		return $stmt;
	}
};


/* Initialize the database object */
$database = new Database;

As you can see, there are two functions added, one for storing queries, and one to display the current log. The storing function tracks two differen't sections, Multiple Queries and Query Backtrace.

Multiple Queries section displays all queries that are run more than once within the script. It also display's their ID# which can be used with the next section to track where that query is called from. The Query Backtrace section gives details on each query as to what file it was called from, as well as the line number.

This addition is useful in the sense that you can see what queries are being run, when they are being run, and how often they might be run. It can make optimizations run a lot smoother when optimizing queries.

As usual I've included an example package, just update the DB details with your own.