Page 1 of 1

My DB Class comments needed

Posted: Sat Sep 12, 2009 9:22 am
by dude81
Hello all,

Here is little DB class that I've written on myself with PDOs so that the end developer is able to use the methods inside. Could some experts throw some comments on the code

The directory structure is as follows
config/config.ini,
config/Config.php
Dataset.php
DB.php

config/config.ini

Code: Select all

 
; Database parameters
[DB]
driver="mysql"
host="localhost"
port="3306"
dbname="test"
dbuser="root"
dbpass=""
;Other sections could be added here
[MAIL]
smtp_host="localhost"
smtp_port="25"
 
config/Config.php

Code: Select all

 <?php
/*
 * Created on 12-Aug-08
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
 
 Class Config
 {
    
   const CONFIG_FILE ='config.ini';
    
   public $properties = array();
   
   function __construct()
   {
     return  $this->properties = parse_ini_file(self::CONFIG_FILE,true);
   }
   
 }
 
 ?>
Dataset.php

Code: Select all

<?php
/*
 * Created on 13-Aug-08
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
 
 class Dataset 
 {
    
    function __set($name, $value) 
    {
        $this->$name = $value;
    }
    
    function __get($name) 
    {
        return $this->$name;
    }
    
 }
?>
DB.php

Code: Select all

<?php
/*
 * Created on 13-Aug-08
 *
 * To change the template for this generated file go to
 * Window - Preferences - PHPeclipse - PHP - Code Templates
 */
 
/**
 * For db configuration to be loaded
 */
require_once('config/Config.php');
 
/**
 * For SQL retrieved data to be in  dataset Object format
 */
require_once('Dataset.php');
 
/* This class is DB class used for database connection, executions of queries and retrieving data
 * 
 * @version: 0.1
 * @package Core
 * @subpackage DB
 */
class DB 
{
    
    /**
     * Dsn database connection parameter in order to connect to various kind of databases
     * @access private
     * @var string
     */
    private $dsn;
    
    /**
     * database user name
     * @access private
     * @var string
     */
    private $dbuser;
    
    /**
     * database user password
     * @access private
     * @var string
     */
    private $dbpassword;
    
    /**
     * A connection string variable for the database
     * @access private
     * @var string
     */
    private $connectionString;
    
    /**
     * DB constructor method
     * @access public
     * @return void;
     */
    function __construct()
    {
        $config = new Config();
        $this->getDBConnect($config->properties);
    }
    
    /**
     * getDBConnect method for connecting to a database
     * @access private
     * 
     * @return void
     */
    private function getDBConnect($iniArray)
    {
        if(isset($iniArray['DB'])){
            $driver = $iniArray['DB']['driver'];
            
            switch($driver) {
                case 'mysql':
                    $host = $iniArray['DB']['host'];
                    $port = $iniArray['DB']['port'];
                    $dbname = $iniArray['DB']['dbname'];
                    $this->dsn = $driver.":host=".$host.";port=".$port.";dbname=".$dbname;
                    break;
                case 'oci':
                    // code for oracle compatibility
                    break;
                default:
                     throw new Exception("No database driver details found");
                     exit;
            }
        
            $this->dbuser = $iniArray['DB']['dbuser'];
            $this->dbpassword = $iniArray['DB']['dbpass'];
            try{
                $this->connectionString = new PDO($this->dsn,$this->dbuser,$this->dbpassword,array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
                $this->connectionString->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            }catch  (PDOException $e){
                echo $e->getMessage();
                exit;
            }   
            
        }
    }
    
    /**
     * getSQLData is used for retrival of data from a table or for execution of a stored procedure
     * @access public
     * @param string $query
     * @param array $params
     * 
     * @return object $result Dataset Object 
     */
    public function getSQLData($query,$params=array())
    {
        try{
            $stmt = $this->connectionString->prepare($query);
            $stmt->execute($params);
        } catch(PDOException $e){
            echo $e->getMessage();
            exit;
        }
        
        $result = null;
        for($i=0; $i<$stmt->rowCount();$i++){
            $result[] = $stmt->fetchObject('Dataset');
        }
        return $result;
    }
    
    /**
     * Not in Use
     * validateSQLValues is used for preventing SQL injections
     * @access private
     * @param string $data
     * 
     * @return string $data 
     */
    private function validateSQLValues( $data )
    {
        if(preg_match('/\'+/si',$data)){
            $data = preg_replace("/\'+/si","\\'", $data);
        }
        return $data;
    }
    
    
    /**
     * beginTransaction is used whenever a tranasaction is started
     * @access public
     * 
     * @return boolean true
     */
    public function beginTransaction()
    {
        try{
            if ($this->connectionString->getAttribute(PDO::ATTR_AUTOCOMMIT)===TRUE) {
                $this->connectionString->setAttribute(PDO::ATTR_AUTOCOMMIT,FALSE);
                if(!$this->connectionString->getAttribute(PDO::ATTR_AUTOCOMMIT)===FALSE){
                    throw new Exception("Tranasaction failed to begin");
                }
            }
            echo $this->connectionString->getAttribute(PDO::ATTR_AUTOCOMMIT);
            //$this->connectionString->beginTransaction();
            
        }catch (PDOException $e){
            echo $e->getMessage();
            exit;
        }
        return true;
    }
    
    /**
     * commitTransaction is used whenever a tranasaction is commited
     * @access public
     * 
     * @return boolean true
     */
    public function commitTransaction()
    {
        try{
            
            //$this->connectionString->commit();
            if ($this->connectionString->getAttribute(PDO::ATTR_AUTOCOMMIT)===FALSE){
                $this->connectionString->setAttribute(PDO::ATTR_AUTOCOMMIT,TRUE);
            
                if(!$this->connectionString->getAttribute(PDO::ATTR_AUTOCOMMIT)===TRUE){
                    throw new Exception("Tranasaction failed to commit");
                }
            }
            
        }catch (PDOException $e){
            echo $e->getMessage();
            exit;
        }
        return true;
    }
    
    /**
     * rollBackTransaction restores database to old state before the transaction started
     * @access public
     * 
     * @return boolean true
     */
    public function rollBackTransaction()
    {
        try{
            $this->connectionString->rollBack();
        }catch (PDOException $e){
            echo $e->getMessage();
            exit;
        }
        return true;
    }
    
    
    /**
     * buildSQL builds an insert or update query when a table name, columnanmes and values are passed
     * @access public
     * @param string $table
     * @param array $columnnames
     * @param array $rowvals
     * @param string $type
     * 
     * @return string $querystr 
     */
     
    public function buildSQL($table, $columnnames, $rowvals,$type,$condition=NULL)
    {
        
        if(count($columnnames) != count($rowvals)){
            throw new Exception ("fieldnames and values count does not match");
            exit;
        }
        
        switch($type){
            
        case 'insert':
            $colnameslist = "(" ;
            $valuelist = "(" ;
            $count = 0 ;
            
            for ($count = 0 ; $count < count($columnnames) ; $count ++) {
    
                if ($count > 0) {
                    $colnameslist .= "," ;
                    $valuelist .= "," ;
                }
                $colnameslist .= $columnnames[$count] ;
                $valuelist .= ($this->quote($rowvals[$count])) ;
            }
            
            $colnameslist .= ")";
            $valuelist .= ")";
            $querystr = "insert into " . $table . " " .  $colnameslist . " values " . $valuelist;
            break;
            
        case 'update':
            if(count($columnnames) !=0 ){
                $querystr = "update $table set ";
            }else{
                return;
            }
            $count = 0;
            for($count = 0; $count < count($columnnames); $count++){
                $querystr .= $columnnames[$count]."=".$this->quote($rowvals[$count]).","; 
            }   
            
            $querystr = substr($querystr,0,-1);
            
            if($condition !== NULL) {
                $querystr .= " ".$condition;    
            }
            
            break;
        }
        return $querystr;
        
    }
    
    public function buildMultiInsertSQL($table, $coloumnnames, $rowvals){
        
        $data = "";
        
        for($i=0; $i<count($rowvals); $i++){
                
            $actual_rowvals = array_values($rowvals[$i]);
            
            if(count($coloumnnames) != count($actual_rowvals)){
                $msg = true;
            }
            
            if(!$msg){
                $data   .= "(".implode(',',$actual_rowvals)."),";
            }
        }
        
        if($msg == true){
            throw new Exception("An array field count and column names does not match");
            exit;
        }
        
        $dbcolumnames = implode(',',$coloumnnames);
        $sql = "insert into $table ($dbcolumnames) values $data";
        $sql = substr($sql,0,-1);
        
        return $sql;
    }
    
    /**
     * Note: This is a database specific function, it may not be supported in all kind of databases
     *       For more information on this please refer: http://in2.php.net/manual/en/pdo.quote.php
     * 
     * Quote function escapes the variable. 
     * This can be used where values could not be bound. More in insert and update queries
     * @access public
     * @param string $variable
     * 
     * @return string $quoted_variable
     */
     
    public function quote($variable){
        $quoted_variable = $this->connectionString->quote($variable);
      return $quoted_variable;
    }
    
    
    /**
     * This is used for exclusively to run select statements
     * @access public
     * @param string $select_stmt
     * 
     * @return array $resultset
     */
    public function executeSelect($select_stmt){
        
        if(!empty($select_stmt)){
            $stmt = $this->connectionString->query($select_stmt);
            while ($row = $stmt->fetchObject('Dataset')) {
                $resultset[] = $row;
            }
        }
        return $resultset;
        
    }
    
    
    /**
     * This is used for execution of insert, delete, update queries
     * @access public
     * @param string $query
     * 
     * @return int $affected_rows
     */
    public function executeQuery($query){
        try{
            $pass = false;
            $query = trim($query);
            if($query === NULL || empty($query)){
                return true;
            }
            if(!$pass){
                $affected_rows = $this->connectionString->exec($query);
            }
            
        }catch (PDOException $e){
            echo "Caught Exception:".$e->getMessage();
        }
     return $affected_rows;
    }
    
    /**
     * This will return the last insertID
     * @access public
     * @param string $name
     * 
     * @return $id;
     */
    public function  getLastInsertId($name=''){
        $id = $this->connectionString->lastInsertId($name);
      return $id;
    }
    
    
    public function executeSelectArray($select_stmt){
        if(!empty($select_stmt)){
            $stmt = $this->connectionString->query($select_stmt);
            while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $resultset[] = $row;
            }
        }
        return $resultset;
        
        
    }
    
    public function closeDB(){
        $this->connectionString = null;
    }
    
    
    
    /**
     * This methods the TestDB Class. Should be removed when DB class is fully developed
     * 
     */
    public function TestDB(){
        $createSQL  = "create table Administrator ( adminId varchar(20) not null, surname varchar(20), firstname varchar(20) not null,".
                      " department varchar(20),primary key(adminId))ENGINE=InnoDB;";
                        
        //$this->executeQuery($createSQL);
        
        //Testing executeSelect
        $firstSELECT = "SELECT * from administrator";
        echo "<pre>";
        print_r($this->executeSelect($firstSELECT));
        
        //Testing buildSQL for insert
        $table = 'administrator';
        $fields1 = array('adminId','surname','firstname','department');
        $values1 = array('user1','Bill',' Krishna','WebDevelopment');
        $values2 = array('user2','Steve','Johns','WebDevelopment');
        $values3 = array('user3','Bauer','Chris','ProjectManagement');
        $values4 = array('user4','Papa','Shango','ProjectManagement');
        $values5 = array('user5','Man','Hit','Operations');
        
        //$this->executeQuery($this->buildSQL($table,$fields1,$values1,'insert'));
        //$this->executeQuery($this->buildSQL($table,$fields1,$values2,'insert'));
        //$this->executeQuery($this->buildSQL($table,$fields1,$values3,'insert'));
        //$this->executeQuery($this->buildSQL($table,$fields1,$values4,'insert'));
        //$this->executeQuery($this->buildSQL($table,$fields1,$values5,'insert'));
        
        //Testing buildSQL for update
        $update_fields1 = array('surname'); $update_values1 = array('P'); $condition1 = " WHERE adminId='user1'";   
        $update_fields2 = array('firstname'); $update_values2 = array('Basha'); $condition2 = " WHERE adminId='user2'";
        $update_fields3 = array('department'); $update_values3 = array('Architect'); $condition3 = " WHERE adminId='user3'";
        $update_fields4 = array('department'); $update_values4 = array('Architect'); $condition4 = " WHERE adminId='user4'";
        $update_fields5 = array('department'); $update_values5 = array('ProjectManagement'); $condition5 = " WHERE adminId='user5'";
        
        $this->executeQuery($this->buildSQL($table,$update_fields1,$update_values1,'update',$condition1));
        $this->executeQuery($this->buildSQL($table,$update_fields2,$update_values2,'update',$condition2));
        $this->executeQuery($this->buildSQL($table,$update_fields3,$update_values3,'update',$condition3));
        $this->executeQuery($this->buildSQL($table,$update_fields4,$update_values4,'update',$condition4));
        $this->executeQuery($this->buildSQL($table,$update_fields5,$update_values5,'update',$condition5));
        
        $delete1 = "DELETE  from administrator  WHERE adminId='user1'";
        $this->executeQuery($delete1);
        
        
        $this->beginTransaction();
        $this->executeQuery($this->buildSQL($table,$fields1,$values1,'insert'));
        echo "Last Insert ID:".$this->getLastInsertId();
        $secondSELECT = "SELECT * from administrator where adminId='user1'";
        echo "After Insert <pre>";
        print_r($this->executeSelectArray($secondSELECT));
        $this->executeQuery($this->buildSQL($table,$update_fields1,$update_values1,'update',$condition1));
        $thirdSELECT = "SELECT * from administrator where adminId='user1'";
        echo "After Update <pre>";
        print_r($this->executeSelect($thirdSELECT));
        
        //$this->rollBackTransaction();
        
        $this->commitTransaction();
        
        $this->executeQuery("UPDATE administrator set adminId='user10' where adminId='user1'");
        
         //Handling exceptions
        
        
        $dropTable = "DROP TABLE Administrator";
        //$this->executeQuery($dropTable);
                         
        
    }
} 
?>
Please give me some comments on my code to make it better. :) :|

Re: My DB Class comments needed

Posted: Thu Sep 24, 2009 9:04 pm
by dude81
No takers for criticizing this :?: :roll: . or is it too basic? or something silly :cry:

Re: My DB Class comments needed

Posted: Fri Sep 25, 2009 10:24 am
by pickle
  • I wonder about the need for your Config class. Couldn't the config.ini file just be loaded from your DB class?
  • Why store the database credentials? You don't need them after you connect, and it's dangerous if you ever want to var_dump() an instance of the object.
  • It would make more sense to me to rename $this->connectionString to $this->connection.
  • If you're not going to use validateSQLValues(), get rid of it.
  • It seems unnecessary to have a separate execute function just for SELECT statements.
  • I don't see where your Dataset class is used. I would have assumed executeSelect().