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"?>
    <querylog>
        <query>
            <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>
            <serverip>182.178.2.35</serverip>
            <hostip>122.111.123.121</hostip>
            <dbserver>182.178.2.57:1204</dbserver>
            <dbname>testdb</dbname>
            <filename>/data/test.php</filename>
        </query>
    </querylog>

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='')
{
    if(!$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
        $dom->load($filename);
        $root   = $dom->documentElement;
    }else{ //Create XML File if it doesnt exist
        $dom->formatOutput = true;
        $root = $dom->createElement( "querylog" );
        $dom->appendChild($root);
        //chmod($path."/querylog.xml", 0777);
    }
    
    $q = $dom->createElement("query");
    foreach($arr as $key=>$value)
    {
        $element = $dom->createElement($key);
        if($key=='query'||$key=='error'){
            $element->appendChild($dom->createCDATASection($value));	
        }else{
            $element->appendChild($dom->createTextNode($value));
        }
        $q->appendChild( $element );
    }
    $root->appendChild($q);
    $dom->save($filename);
}

/**
*	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);
    for($i=0;$i<$dirCnt;$i++)
    {
        if($getDir[$i])
        {
            $pathExisting = $pathExisting.&quot;/&quot;.$getDir[$i];
            $pathExisting = str_replace('//','/',$pathExisting);
                if(!is_dir($pathExisting))	
                    if(!mkdir($pathExisting,0777))
                        die($pathExisting);
        }
    }
    return ($pathExisting);
}
Advertisements

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