Logging is absolutely essential to keep track of your system.
I created the below function to keep track of any database queries which failed. Since 1 of the reasons for this could be a loss of database connectivity, keeping a XML based log was more logical than a database log.

For further examples of adding/editing a XML file, check out my other post :
PHP : Create Add Edit Modify Search a XML File via SimpleXML , DOM & XPath

Note :

  • Folder Structure :
    The XML file will have be in a folder in which Y-M-d structure will be maintained. (eg : Today is 2010-05-29 so the XML file will be in inside LOGS/2010/05/29).
  • File Structure :
    <?xml version="1.0"?>
            <timestamp>2008-01-12 19:05:17</timestamp>
            <query>SELECT a,b FROM master WHERE flag='1</query>
            <error>You have an error in your SQL syntax</error>

Code :

*	fn_QueryLog()
*	Internally called to Log Query.
*      	@author 	Rohit (quest4knowledge.wordpress.com)
*	@param   	string	$sql	Query
*	@param  	string	$path	Existing Folder of Query Logging
*	@return 	NULL
function fn_QueryLog($sql,$path='')
        $path 		= rtrim($_SERVER['DOCUMENT_ROOT'],'/').'/LOGS';
    $folders 	= date("Y")."/".date("m")."/".date("d");
    $path       = folderCreator($path,$folders); //defined below
    $filename 	= $path."/querylog.xml"; //XML File Name
    $arr['timestamp'] 	= date('Y-m-d H:i:s');
    $arr['query'] 	= $sql;
    $arr['error'] 	= @mysql_error();
    $arr['serverip'] 	= $_SERVER['SERVER_ADDR'];
    $arr['hostip'] 	= $_SERVER['REMOTE_ADDR'];
    $arr['dbserver'] 	= $this->dbserver; // Your DB Server name
    $arr['dbname'] 	= $this->dbname;	// Your DB Name
    $arr['filename'] 	= $_SERVER['SCRIPT_NAME'];
    $dom = new DOMDocument('1.0');
    if(file_exists($filename)){ //Edit Existing XML File
        $root   = $dom->documentElement;
    }else{ //Create XML File if it doesnt exist
        $dom->formatOutput = true;
        $root = $dom->createElement( "querylog" );
        //chmod($path."/querylog.xml", 0777);
    $q = $dom->createElement("query");
    foreach($arr as $key=>$value)
        $element = $dom->createElement($key);
        $q->appendChild( $element );

*	folderCreator
*	Internally called to Create Folders if they dont exist.
*      	@author 	Rohit (quest4knowledge.wordpress.com)
*	@param   	string	$pathExisting	Existing Folder structure..
*	@param  	string	$dirToCreate	Folders to be created.
*	@return 	string	$pathExisting	New Existing Folder structure.
function folderCreator($pathExisting,$dirToCreate)
    $getDir = explode("/",$dirToCreate);
    $dirCnt = count($getDir);
            $pathExisting = $pathExisting.&quot;/&quot;.$getDir[$i];
            $pathExisting = str_replace('//','/',$pathExisting);
    return ($pathExisting);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s