connectDB();
$this->benchmarker=&benchmark::getInstance();
} // function db
/**
Instantiate with a singlegton
*///________________________________________________________________________________________________________________________
function &getInstance(){
static $instance;
if (!$instance) {
$instance = new db();
}
return $instance;
}
//________________________________________________________________________________________________________________________
/**
Connect to DB
This is called when instantiating the class - doesn't need to be called directly
* @param string $declarationFile - the name and path to the file
* @param mixed $external - flag. If it's external we need to use a port.
* @todo - the email for errors is hard-coded. Need a universal error handling class.
*///________________________________________________________________________________________________________________________
function connectDB(){
if (!defined('DB_NAME') or !defined('DB_USER') or !defined('DB_PWD')){
return "database declarations not set up";
}
$databaseName=DB_NAME;
$user=DB_USER;
$pwd=DB_PWD;
$ipPort=DB_IPPORT;
$this->connhandle = mysql_connect($ipPort,$user,$pwd);
mysql_select_db($databaseName,$this->connhandle);
} // function connectDB(){} ends
//________________________________________________________________________________________________________________________
/**
* returns a row.
*///________________________________________________________________________________________________________________________
function row($str_sql){
$benchCode=$this->benchmarker->start();
$result=mysql_query($str_sql) or die($this->outputError($str_sql."
".mysql_error()));
if($row=mysql_fetch_assoc($result)){
mysql_free_result($result);
$this->benchmarker->stop($benchCode,$str_sql);
return $row;
}
$this->benchmarker->stop($benchCode,$str_sql);
} // row ends
//________________________________________________________________________________________________________________________
/**
* returns a 2d array of a query - as an associative array.
*///________________________________________________________________________________________________________________________
function data($str_sql,$direction=0,$assoc=''){
$benchCode=$this->benchmarker->start();
$result=mysql_query($str_sql) or die($this->outputError($str_sql."
".mysql_error()));
if($row=mysql_fetch_array($result)){
$n=0;
do{
if ($assoc) {
$n=$row[0];
}
for ($i=0;$ibenchmarker->stop($benchCode,$str_sql);
return $vals;
}
$this->benchmarker->stop($benchCode,$str_sql);
} // data ends
//________________________________________________________________________________________________________________________
/**
* get the field info from a table
*///________________________________________________________________________________________________________________________
function getFields($thetable,$n='',$types=''){
if (!count($this->fieldsInTables[$thetable]) or ($types and strpos($this->fieldsInTables[$thetable][0],'DB_')!==false)) {
$benchCode=$this->benchmarker->start();
$this->fieldsInTables[$thetable]=array();
if (eregi('select ', $thetable)) {
$str_sql=$thetable;
}else{
$str_sql="SELECT * FROM ".$thetable. " limit 1";
}
$result = mysql_query($str_sql) or die($this->outputError($str_sql."
".mysql_error()));
$j=0;
for($i=0;$ifieldsInTables[$thetable][$j]['name'] =mysql_field_name($result, $i);
$this->fieldsInTables[$thetable][$j]['type'] =mysql_field_Type($result, $i);
}else{
$this->fieldsInTables[$thetable][$j] =mysql_field_name($result, $i);
}
$j++;
}
}
mysql_free_result($result);
$this->benchmarker->stop($benchCode,$str_sql);
}
if ($n!=='') { // nb: equivalence rather than == . Because ''==0
if (is_array($this->fieldsInTables[$thetable][$n])) {
$fieldName=$this->fieldsInTables[$thetable][$n]['name'];
}else{
$fieldName=$this->fieldsInTables[$thetable][$n];
}
return $fieldName;
}
return $this->fieldsInTables[$thetable];
} // getfields ends
//________________________________________________________________________________________________________________________
/**
* performs query - used for updates + deletes - if select then it returns a single value
*///________________________________________________________________________________________________________________________
function qry($str_sql){
$benchCode=$this->benchmarker->start();
$result=mysql_query($str_sql) or die($this->outputError($str_sql."
".mysql_error()));
if (strtolower(left($str_sql,7))=='select ') {
$row=mysql_fetch_array($result);
$this->benchmarker->stop($benchCode,$str_sql);
return $row[0];
}else{
$this->benchmarker->stop($benchCode,$str_sql);
return mysql_affected_rows(); // note this will return 0 for an update that doesn't update anything. It isn't always an error.
}
$this->benchmarker->stop($benchCode,$str_sql);
} // qry ends
//________________________________________________________________________________________________________________________
/**
* adds a row to a table.
Simulates autoincrementing behaviour - ie: will find the next id.
* @param string $table - the table that we're pulling the ID from
* @param array $fields - an associative array to be added to the table
* @return integer
*///_______________________________________________________________________________________________________________________
function add($table,$fields){
if ($table and is_array($fields)){
$benchCode=$this->benchmarker->start();
$idField=$this->getFields($table,0);
// if a userID is supplied, use that. Might cause crashes.
if (!$fields[$idField]) {
$fields[$idField]=$this->qry("SELECT Max(".$idField.")+1 FROM ".$table);
$fields[$idField]=$fields[$idField]?$fields[$idField]:1;
}
$str_sql='insert into '.$table.' ('.implode(',',array_keys($fields)).') values ("'.implode('","',$fields).'")';
mysql_query($str_sql) or die($this->outputError($str_sql."
".mysql_error()));
$this->benchmarker->stop($benchCode,$str_sql);
return $fields[$idField];
}
} // getnextid ends
//________________________________________________________________________________________________________________________
/**
* updates a row in a table
* @param string $table - the table that we're pulling the ID from
* @param array $fields - an associative array to be added to the table
* @return integer
*///_______________________________________________________________________________________________________________________
function update($table,$fields,$id){
if ($table and is_array($fields) and $id){
$benchCode=$this->benchmarker->start();
$idField=$this->getFields($table,0);
$fields[$idField]=$id;
$str_sql="update ".$table." set ";
$n=0;
foreach($fields as $field=>$value) {
$str_sql.=($n?",":"").$field.'="'.$value.'"';
$n++;
}
$str_sql.=" where ".$idField."='".$id."'";
mysql_query($str_sql) or die($this->outputError($str_sql."
".mysql_error()));
$this->benchmarker->stop($benchCode,$str_sql);
return $fields[$idField];
}
} // getnextid ends
//________________________________________________________________________________________________________________________
/**
* adds a row to a table or updates if there's key duplication
Simulates autoincrementing behaviour - ie: will find the next id.
* @param string $table - the table that we're pulling the ID from
* @param array $fields - an associative array to be added to the table
* @return integer
*///_______________________________________________________________________________________________________________________
function save($table,$fields){
if ($table and is_array($fields)){
$benchCode=$this->benchmarker->start();
$idField=$this->getFields($table,0);
// if a userID is supplied, use that. Might cause crashes.
if (!$fields[$idField]) {
$fields[$idField]=$this->qry("SELECT Max(".$idField.")+1 FROM ".$table);
$fields[$idField]=$fields[$idField]?$fields[$idField]:1;
}
$str_sql='insert into '.$table.' ('.implode(',',array_keys($fields)).') values ("'.implode('","',$fields).'")';
// get the keys
$keys=$this->data('show keys in '.$table,"vertical");
if (count($keys)) {
$n=0;
$str_sql.=" on duplicate key update ";
foreach($fields as $key=>$field) {
if (!@in_array($key,$keys['Column_name'])) {
$str_sql.=($n?",":"").$key."=\"".$field."\"";
$n++;
$foundExtras="y";
}
}
if (!$foundExtras) { // just go update the PK if there are no non-key fields
$str_sql.=$idField.'='.$idField;
}
}
mysql_query(trim($str_sql)) or die($this->outputError($str_sql."
".mysql_error()));
$this->benchmarker->stop($benchCode,$str_sql);
return $fields[$idField];
}
} // getnextid ends
//________________________________________________________________________________________________________________________
/**
* deletes a row from a table
[example]
$db->delete("DB_011_USER",1);
* @param string $table - the table that we're pulling the ID from
* @param array $fields - an associative array to be added to the table
* @return integer
*///_______________________________________________________________________________________________________________________
function delete($table,$id){
if ($table and $id){
$benchCode=$this->benchmarker->start();
$idField=$this->getFields($table,0);
$str_sql='delete from '.$table.' where '.$idField."='".$id."'";
mysql_query($str_sql) or die($this->outputError($str_sql."
".mysql_error()));
$this->benchmarker->stop($benchCode,$str_sql);
return mysql_affected_rows(); // note - this can't be used to gauge success because there may be nothing to delete
}
} // getnextid ends
/**
caches the results of a query
*///________________________________________________________________________________________________________________________
function dataCache($cacheFile,$str_sql){
if (is_file($cacheFile)) {
$last_modified = filemtime($cacheFile);
$modDate=date("Y-m-d H:i:s", $last_modified);
if (get_date($modDate,array('d'=>1))>date('Y-m-d H:i:s')) {
$output=file_get_contents($cacheFile);
$data=unserialize($output);
return $data;
}
}
$data=$this->data($str_sql);
$f=fopen($cacheFile,'w');
fwrite($f,serialize($data));
fclose($f);
return $data;
}
//________________________________________________________________________________________________________________________
/**
* outputs a big error message - with tracing etc
*
* @access private
* @param string $msg - error message generated for SQL
*///________________________________________________________________________________________________________________________
function outputError($msg){
if ($_REQUEST['isDeveloper'] or 1) {
$str.="
MYSQL ERROR ".$msg."
";
$vars=debug_backtrace();
$str.="";
//$str.="------------------------------------------------------------------------------------------------------------------------------------------------- ";
}else{
$str.="------------------------------------------------------------------------------------------------------------------------------------------------- ";
$str.=" There has been a glitch in the database - the details have been logged and our developers contacted.