Location: PHPKode > projects > Peters PHP PDF Report > class.Database.php
<?php

require_once('DB.php');
require_once('constants.php');

class Database {
  private $conn;

  public function __construct($dsn = null) {
    global $cfg;
    
    //If nothing was passed in, use the value from $cfg
    if($dsn == null) {
    	$dsn=$this->getDSN($cfg['db']['name']);
    }
    $this->buildConnection($dsn);
  }
  
  static public function instance() {
  	static $objDB;
  	
  	if (!isset($objDB)) {
			$objDB = new Database();
		}
		return $objDB;
  }

  
  private function buildConnection($dsn){
    //Open a connection using the info in $dsn
    $this->conn = DB::connect($dsn);
    
    if(DB::isError($this->conn)) {
      //We're not connected.  Throw an exception
      throw new Exception($this->conn->getMessage(), $this->conn->getCode());
    }
    
    $this->conn->setOption('persistent',true);
    
    //Always fetch data as an associative array
    $this->conn->setFetchMode(DB_FETCHMODE_ASSOC);
  }
  
  private function getDSN($name){
    global $cfg;
      $dsn = $cfg['db']['type'];
      $dsn .= '://'.$cfg['db']['user'];
      if (isset($cfg['db']['pass']) && strlen($cfg['db']['pass']) > 0) {
      	$dsn .= ':'.$cfg['db']['pass'];
      }
      $dsn .= '@'.$cfg['db']['host'];
      $dsn .= '/'.$name;
      return $dsn;
  }
  
  function reConnect($dbname){
    $this->buildConnection($this->getDSN($dbname));
  }
  
  function getConnection(){
  	return $this->conn;
  }

	function delete($sql){
		$this->select($sql);
	}

  
  //returns a DB_result object  
  function selectForUpdate($table,$where) {
  	$sql="SELECT * FROM $table WHERE $where FOR UPDATE";
    return $this->select($sql);
  }

  //returns a DB_result object  
  function select($sql) {

    $result = $this->conn->query($sql);
   
    if(DB::isError($result)) {
      throw new Exception($result->getMessage(), $result->getCode());
    }
    
    return $result;
  }
  
  //returns 2D assoc array
  function getAll($sql) {

    $result = $this->conn->getAll($sql);
   
    if(DB::isError($result)) {
      throw new Exception($result->getMessage(), $result->getCode());
    }
    
    return $result;
  }
  
  //returns single scalar value from the first column, first record
  function getOne($sql) {

    $result = $this->conn->getOne($sql);
    
    if(DB::isError($result)) {
      throw new Exception($result->getMessage(), $result->getCode());
    }
    
    return $result;
  }
  
  //returns numerically indexed 1D array of values from the first column
  function getColumn($sql) {

    $result = $this->conn->getCol($sql);
    
    if(DB::isError($result)) {
      throw new Exception($result->getMessage(), $result->getCode());
    }
    
    return $result;
  }

  //returns numerically indexed 1D array of values from the first row
  function getRow($sql) {

    $result = $this->conn->getRow($sql);
    
    if(DB::isError($result)) {
      throw new Exception($result->getMessage(), $result->getCode());
    }
    
    return $result;
  }

  function update($tableName, $arUpdates, $sWhere = null) {
    
    $arSet = array();
    foreach($arUpdates as $name => $value) {
      $arg = $name . ' = ';
      if (strpos($value,"#!") === 0) { 
      	$arg .= substr($value,2);
      } else {
      	$arg .= $this->conn->quoteSmart($value);
      } 
      $arSet[] = $arg;
    }
    $sSet = implode(', ', $arSet);

    //make sure the table name is properly escaped
    $tableName = $this->conn->quoteIdentifier($tableName);   

    $sql = "UPDATE $tableName SET $sSet";
    if($sWhere) {
      $sql .= " WHERE $sWhere";
    }

  	$result = $this->conn->query($sql);
    
    if(DB::isError($result)) {
    	throw new Exception($result->getMessage(), $result->getCode());
    }
    
    //return the number of rows affected
    return $this->conn->affectedRows();
  }
function insert($tableName, $arValues) {
    $id = null;

    $sFieldList = join(', ', array_keys($arValues));
    
    $arValueList = array();
    foreach($arValues as $value) {
      if(strpos($value,"##") === 0) {
        //we need to get the next value from this table's sequence
        if($p=strpos($value,"#",2)){
        	$value=$this->getNextSeq(substr($value,2,$p-2),substr($value,$p+1));
        }else{
        	$value=$this->getNextSeq(substr($value,2));
        }
        // pear solution!!!
        // $value = $id = $this->conn->nextID($tableName . "_id");
      }   
      if (strpos($value,"#!") === 0) {
      	$value=substr($value,2);
      }else{ 
      	$value = $this->conn->quoteSmart($value);
      } 
      $arValueList[] = $value;
    }
    $sValueList = implode(', ', $arValueList);
    
    //make sure the table name is properly escaped
    $tableName = $this->conn->quoteIdentifier($tableName);
    
    $sql = "INSERT INTO $tableName ( $sFieldList) VALUES ( $sValueList )";

    $result = $this->conn->query($sql);
    $res=$this->conn->query("select LAST_INSERT_ID() id"); 
    
    if(DB::isError($result)) {
      throw new Exception($result->getMessage(), $result->getCode());
    }
    
    $id = 0;
    if($row=($res->fetchRow())){
	    $id=$row['id'];
    }
    //return the ID, if there was one, or the number of rows affected
    //return $id ? $id : $this->conn->affectedRows();
    return $id;
  }
  function startTransaction() {
    //autoCommit returns true/false if the command succeeds
    return $this->conn->autoCommit(false);
  }
  function commit() {
    $result = $this->conn->commit();
    
    if(DB::isError($result)) {
      throw new Exception($result->getMessage(), $result->getCode());
    }
    
    $this->conn->autoCommit(true);
    return true;
  }
  
  function rollback() {
    $result = $this->conn->rollback();
    
    if(DB::isError($result)) {
      throw new Exception($result->getMessage(), $result->getCode());
    }
    
    return true;
  }
  
  
  function __destruct() {
//    $this->conn->disconnect();
  }
  
  public function getNextSeq($n,$pf="") {
  	$rv="-";
  	$this->startTransaction();
  	$var=$n.$pf;
  	$res = $this->select("select count(name) as cnt from sequences where name='$var'");
  	if($row=$res->fetchRow()){
  		if($row['cnt']==0){
  			$this->insert("sequences",array("name" => $var));
  		}
  		$this->update("sequences",array("value"=>"#!LAST_INSERT_ID(value+1)"),"name='$var'");
  		$res=$this->select("select LAST_INSERT_ID() as id");
  		if($row=$res->fetchRow()){
  			$rv = $row['id'];
  			if(!empty($pf)){
  				$rv="$pf/$rv";
  			}
  		}
  	}
  	$this->commit();
  	return $rv;
  }
  
  /**
   * Get a variable from system table
   * @param string variable's name
   * @param string default value if it is not exist
   */
  static public function getVariable($name,$default="0"){
  	$db = Database::instance();
  	$res=$db->select("select ertek from system where tipus='VAR' and nev='$name'");
  	if($row=$res->fetchRow())
  		return $row['ertek'];
  	return $default;
  }
}
?>
Return current item: Peters PHP PDF Report