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> </td>
<td bgcolor="$bg" width=60><b>name:</b></td>
<td bgcolor="$bg">$key</td>
</tr>
<tr>
<td> </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;
}
}
?>
