#native_company# #native_desc#
#native_cta#

MyDBObject

By Mattias Sundberg
on March 23, 2002

Version: 0.1

Type: Class

Category: Databases

License: GNU General Public License

Description: Database abstraction layer for MySQL. Caching of the resultset in both numeric and asscociative arrays. Enables you to jump both forwards and backwards in the resultset. Basic XML-output of the resultsets. Debug and logging functions.

<?

class dbObject {

	var $hostname;
	var $database = "music";
	var $username = "www-data";
	var $password;
	var $conLink = false;
	var $conLinkID;
	var $result = false;
	var $errorMsg = false;
	var $resultArray = false;
	var $curRow = false;
	var $resultCount = false;
	var $queryString;
	var $queryExecTime;
	var $queryStoreTime;
	var $queryTime;
	var $resultXML;
	var $XMLHeader = "<?xml version="1.0"?>";
	var $logfile = "/usr/share/pear/dbObject.log";
	var $insert_id;
	var $affected_rows;
	var $debug = true;

	function dbObject ($hostname="",$database="",$username="",$password="") {
		$this->conLinkID = rand();
		if($hostname)
			$this->hostname = $hostname;
		if($database)
			$this->database = $database;
		if($username)			
			$this->username = $username;		
		if($password)			
			$this->password = $password;		
		if(@!$this->conLink = mysql_connect("$this->hostname","$this->username","$this->password")) {
			$this->logger("Could not create object: ".$this->error());
			return false;
		}
		if(!mysql_select_db($this->database,$this->conLink)) {
			$this->logger("Could not create object: ".$this->error());
			return false;
		}
		$this->logger("New instance created for '$this->hostname' using '$this->database'");
		return $this->conLink;
	}
	
	function close() {
		if(@!mysql_close($this->conLink)) {
			$this->logger("Connection closed failed: no open connection");
			return false;
		}
		$this->logger("Connection closed: [$this->conLink]");
		return true;
	}
	
	function logger($string) {
		if(!string || !$this->logfile)
			return false;
		$fp = fopen($this->logfile,"a");
		$write = fputs($fp,date('Y-m-d H:i:s')."	[$this->conLinkID]	From: ".getenv("REMOTE_ADDR")." [$this->username]	$string".chr(13).chr(10));
		fclose($fp);
		if($this->debug)
			echo $string."<br>";
		return true;
	}
	
	function error() {
		@$this->errorMsg = mysql_error();
		return $this->errorMsg;
	}
		
	function getmicrotime(){ 
		list($usec, $sec) = explode(" ",microtime()); 
		return ((float)$usec + (float)$sec); 
    } 
	
	function query($str,$store=true,$insert=false) {
		if(!$this->conLink) {
			$this->logger("Query failed: '$this->queryString' ".$this->error());
			return false;
		}
		$db = $this->conLink;
		$this->queryString = $str;
		$start = $this->getmicrotime();
		if(!$this->result = mysql_query($this->queryString)) {
			$this->logger("Query failed: '$this->queryString' ".$this->error());
			return false;
		}
		if(!$store) {
			if($insert) 
				$this->insert_id = mysql_insert_id($this->conLink);
			$end = $this->getmicrotime();
			$this->queryExecTime = round($end-$start,4);
			$this->logger("Query executed: '$this->queryString' in $this->queryExecTime");
			return $result;
		}
		$i = 0;
		$storetime = $this->getmicrotime();
		while($row = mysql_fetch_assoc($this->result)) {
			$keys = $this->buildKeys($row);
			$this->resultArray[$i] = $keys;
			$i++;
		}
		$storetimee = $this->getmicrotime();
		$this->queryStoreTime = round($storetimee-$storetime,4);
		$this->logger("Stored resultset and built keys on $i rows in $this->queryStoreTime");
		mysql_free_result($this->result);
		$this->resultCount = $i;
		$this->curRow = -1;
		$end = $this->getmicrotime();
		$this->queryExecTime = round($end-$start,4);
		$this->queryTime = $this->queryExecTime+$this->queryStoreTime;
		$this->logger("Query executed: '$this->queryString' in $this->queryExecTime");
		$this->logger("Total querytime: $this->queryTime");
		return true;
	}
	
	function buildKeys($row) {
		$i = 0;		
		$keys = array();
		while(list($key,$val) = each($row)) {
			$keys[$key] = $val;
			$keys[$i] = $val;
			$i++;
		}
		return $keys;
	}
	
	function insert($inserts,$table) {
		if(!$inserts || !$table)
			return false;
		if(!is_array($inserts)) {
			$this->logger("INSERT: parameter was not an array.");
			return false;
		}
		while(list($key,$val) = each($inserts)) {
			$values = $values . "'$val',";
			$cols = $cols . "$key,";
		}
		$str = "insert into $table(".substr($cols,0,strlen($cols)-1).") values(".substr($values,0,strlen($values)-1).")";
		return $this->query($str,0,1);
	}
	
	function del($string) {
		$start = $this->getmicrotime();
		if(!$string)
			return false;
		$this->queryString = $string;
		if(!$result = mysql_query($this->queryString)) {
			$this->logger("DEL: error while executing query '$this->queryString'".chr(13).chr(10).$this->error());
			return false;
		}
		$this->affected_rows = mysql_affected_rows($this->conLink);
		$end = $this->getmicrotime();
		$this->queryExecTime = round($end-$start,4);
		$this->logger("Delete query executed: '$this->queryString' in $this->queryExecTime");
		return true;
	}
	
	function upd($fields=false,$table=false,$where=false,$string=false) {
		if(!is_array($fields) && !$table && !$where && !$string)
			return false;
		$start = $this->getmicrotime();
		if(is_array($fields) && $table) {
			while(list($key,$val) = each($fields)) {
				$values = $values . "$key='$val',";
			}
			if($where) 
				$this->queryString = "update $table set ".substr($values,0,strlen($values)-1) ." where $where";
			else
				$this->queryString = "update $table set ".substr($values,0,strlen($values)-1);
		} else {
			if($string)
				$this->queryString = $string;
			else
				return false;
		}
		if(!$result = mysql_unbuffered_query($this->queryString)) {
			$this->logger("UPD: error while executing query '$this->queryString'".chr(13).chr(10).$this->error());
			return false;
		} else {
			$this->affected_rows = mysql_affected_rows($this->conLink);
			$end = $this->getmicrotime();
			$this->queryExecTime = round($end-$start,4);
			$this->logger("Update query executed: '$this->queryString' in $this->queryExecTime");
			return true;
		}
		if(!$result = mysql_unbuffered_query($string)) {
			$this->logger("UPD: error while executing query '$string'".chr(13).chr(10).$this->error());
			return false;
		} else {
			$this->affected_rows = mysql_affected_rows($this->conLink);
			$end = $this->getmicrotime();
			$this->queryExecTime = round($end-$start,4);
			$this->logger("Update query executed: '$string' in $this->queryExecTime");
			return true;
		}
	}
	
	function first($format="") {
		$fields = $this->resultArray[0];
		if(!$fields) {
			$this->logger("FIRST: could not get first row in resultset.");
			return false;
			}
		$this->curRow = 0;
		if(!$format)
			return $fields;
		$formatTmp = $format;
		while( list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}
	
	
	
	function curr($format="") {
		$fields = $this->resultArray[$this->curRow];
		if(!$fields) {
			$this->logger("CURRENT: could not get current row in resultset.");
		}
		if(!$format)
			return $fields;
		$formatTmp = $format;
		while( list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}

	function last($format="") {
		$fields = $this->resultArray[$this->resultCount-1];
		if(!$fields) {
			$this->logger("LAST: could not get last row in resultset.");
			return false;
		}
		$this->curRow = $this->resultCount;
		if(!$format)
			return $fields;
		$formatTmp = $format;
		while( list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}
	
	function getRow($format="",$curRow=-1) {
		if($curRow == -1)
			$curRow = $this->curRow;
		$fields = $this->resultArray[$curRow];
		if(!$fields) {
			$this->logger("ROW: could not get row $curRow in resultset.");
			return false;
		}
		if(!$format)
			return $fields;
		$formatTmp = $format;
		while( list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}
	
	function next($format="") {
		$this->curRow++;
		if($this->resultCount <= $this->curRow)
			return false;
		$fields = $this->resultArray[$this->curRow];
		if(!$fields) {
			$this->logger("NEXT: could not get next row in resultset ($this->curRow / $this->resultCount)");
			return false;
		} 
		if(!$format) 
			return true; 
		$formatTmp = $format;
		while(list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}
  
	function prev($format="") {
		$this->curRow--;
		if($this->curRow < 0)
			return false;
		$fields = $this->resultArray[$this->curRow];
		if(!$fields) {
			$this->logger("PREV: could not get previous row in resultset ($this->curRow / $this->resultCount)");
			return false;
		} 
		if(!$format)
			return true;
		$formatTmp = $format;
		while( list($key,$val) = each($fields) ) {
			$formatTmp = str_replace("<!--$key-->",$val,$formatTmp);
		}
		return $formatTmp;
	}

	function getVal($fieldname,$curRow=-1,$keys=-1) {
		if($curRow == -1)
			$curRow = $this->curRow;
		$row = $this->resultArray[$curRow];
		if($keys == -1) {
			$value = $row[$fieldname];
			if(!$value) {
				$this->logger("GETVAL: Could not get requested field '$fieldname'");
				return false;
			}
			return $value;
		}
		while(list($key,$val) = each($row) ) {
			echo "Key: $key - $val<br>";
		}	
	}
	
	function showResult() {
		$string = "<table border=0>";
		while(list($rowkey,$row) = each($this->resultArray)) {
			$string = $string . "<tr><td width=40><b>row:</b></td><td colspan=2>$rowkey</td></tr>n";
			while(list($key,$val) = each($row)) {
			if($bg == "#ffffff") { $bg = "#f0f0f0"; } else { $bg = "#ffffff"; }
				$string = $string . "<tr><td>&nbsp;</td>
				<td bgcolor="$bg" width=60><b>name:</b></td>
				<td bgcolor="$bg">$key</td>
			</tr>
			<tr>
				<td>&nbsp;</td>
				<td bgcolor="$bg"><b>value:</b></td>
				<td bgcolor="$bg">$val</td>
			</tr>n";
			}
		}
		$string = $string . "</table>";
		return $string;
	}
	
	function resetResult() {
		$this->curRow = -1;
		$this->logger("Reseted resultset.");
	}
	
	function clearResult() {
		$this->resultArray = false;
		$this->curRow = -1;
		$this->resultCount = 0;
		$db = $this->conLink;
		$this->logger("Resultset cleared");
	}
	
	function reverseResult() {
		rsort($this->resultArray);
		$this->logger("Resultset reversed");
	}

	function getValXML($fieldname,$curRow=-1,$keys=-1) {
		if($curRow == -1)
			$curRow = $this->curRow;
		$row = $this->resultArray[$curRow];
		if($keys == -1)
			return "<record name="$fieldname"><![CDATA[".trim(htmlentities($row[$fieldname]))."]]></value>n";
		while(list($key,$val) = each($row) ) {
			echo "Key: $key - $val<br>";
		}	
	}
	
	function showResultXML($header=false) {
		if($header) {
			$string = $string . $this->XMLHeader;
		}
		$string = $string . "<resultset>n";
		while(list($rowkey,$row) = each($this->resultArray)) {
			$string = $string . "<row nr="$rowkey">n";
			$i = 0;
			while(list($key,$val) = each($row)) {
				$string = $string . "<column nr="$i" name="$key"><![CDATA[".trim(htmlentities($val))."]]></column>n";
				$i++;
			}
			$string = $string . "</row>n";
		}
		$string = $string . "</resultset>n";
		return $string;
	}
	
	function getRowXML($format=-1,$curRow=-1) {
		$i = 0;
		$xml = $xml . "<row nr="$curRow">n";
		if($curRow == -1)
			$curRow = $this->curRow;
		$fields = $this->resultArray[$curRow];
		if(!$fields)
			return "dbObject: Could not get row <b>$row</b> in resultset<br>";
		if($format == -1) {
			while( list($key,$val) = each($fields) ) {
				$xml = $xml . "<column nr="$i" name="$key"><![CDATA[".trim(htmlentities($val))."]]></column>n";
				$i++;
			}
			$xml = $xml . "</row>n";
			return $xml;
		}
		if(strstr($format,",")) {
			$format = strtok($format,",");
			while($format) {
				$formatTmp = str_replace("$format",$fields[$format],$format);
				$xml = $xml . "<column nr="$i" name="$format"><![CDATA[".trim(htmlentities($formatTmp))."]]></column>n";
				$format = strtok(",");
				$i++;
			}
		} else {
			$formatTmp = str_replace("$format",$fields[$format],$format);
			$xml = $xml . "<column nr="$i" name="$format"><![CDATA[".trim(htmlentities($formatTmp))."]]></column>n";
			$i++;
		}
		$xml = $xml . "</row>n";
		return $xml;
	}
	
}
?>