PDO wrapper class

Question

Connection stored in the xml config file.

<?xml version='1.0' ?>
<database>
 <connection>
 <dbtype>mysql</dbtype>
 <dbname>shoutbox</dbname>
 <host>localhost</host>
 <port>3306</port>
 <user>admin</user>
 <password>admin</password>
 </connection>
</database> 

class DBWrapper {
 /**
 * Stores the database connection object.
 * 
 * @access protected
 * @var database connection object
 */
 protected $dbo = NULL;
 /**
 * Stores the class instance, created only once on invocation. 
 * Singleton object instance of the class DBWrapper
 * 
 * @access protected
 * @static
 */
 protected static $instance = NULL;
 /**
 * Stores the database configuration, from the config.xml file
 * 
 * @access protected
 */
 protected $xml;
 /**
 * When the constructor is called (which is called only once - singleton instance) 
 * the connection to the database is set.
 * 
 * @access protected
 */ 
 protected function __construct()
 {
 $this->getConnection();
 }
 /**
 * Grabs the database settings from the config file
 * 
 * @access private
 */
 private function loadConfig()
 {
 $this->xml = simplexml_load_file("Config.xml");
 }
 /**
 * Instantiates the DBWrapper class.
 * 
 * @access public
 * @return object $instance
 */ 
 public static function getInstance()
 {
 if(!self::$instance instanceof DBWrapper)
 {
 self::$instance = new DBWrapper();
 }
 return self::$instance;
 }
 /**
 * Sets up the connection to the database.
 */ 
 protected function getConnection()
 {
 if(is_null($this->dbo))
 {
 $this->loadConfig();
 list($dsn,$user, $password) = $this->setDSN();
 $this->dbo = new PDO($dsn,$user,$password);
 $this->dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 }
 }
 /**
 * Constructs the database source name(dsn) after the config file is read.
 * 
 * @return array
 */ 
 protected function setDSN()
 {
 $dbtype = $this->xml->connection[0]->dbtype;
 $dbname = $this->xml->connection[0]->dbname;
 $location = $this->xml->connection[0]->host.":".$this->xml->connection[0]->port;
 $user = $this->xml->connection[0]->user;
 $password = $this->xml->connection[0]->password;
 $dsn = $dbtype.":dbname=".$dbname.";host=".$location;
 return array($dsn, $user,$password);
 }
 /**
 * Initiates a transaction.
 */ 
 protected function beginTransaction()
 {
 $this->dbo->beginTransaction();
 }
 /**
 * Commits a transaction.
 */ 
 protected function commitTransaction()
 {
 $this->dbo->commit();
 }
 /**
 * Roll back a transaction.
 */ 
 protected function rollbackTransaction()
 {
 $this->dbo->rollBack();
 }
 /**
 * Select rows from the database.
 * 
 * @param string $table Name of the table from which the row has to be fetched
 * @param array $columns Name of the columns from the table
 * @param array $where All the conditions have to be passed as a array
 * @param array $params For binding the values in the where clause
 * @param array $orderby Name of the columns on which the data has to be sorted
 * @param int $start Starting point of the rows to be fetched
 * @param int $limit Number of rows to be fetched
 * @exception $ex
 * @return int $rowcount
 */ 
 public function select($table, $columns = '*', $where = '', $params = null, $orderby = null, $limit = null, $start = null)
 {
 try
 {
 $query = 'SELECT ';
 $query .= is_array($columns) ? implode(",",$columns) : $columns;
 $query .= " FROM {$table} ";
 if(!empty($where))
 {
 $query .= " where ".implode(" and ", $where);
 }
 if(is_array($orderby)) 
 {
 $query .= " order by ";
 $query .= implode(",",$orderby);
 }
 $query .= is_numeric($limit) ? " limit ".(is_numeric($start) ? "$start, " : " ").$limit : "";
 $sth = $this->dbo->prepare($query);
 $sth->execute($params);
 $rows = $sth->fetchAll(PDO::FETCH_ASSOC);
 return $rows;
 }
 catch(Exception $ex)
 {
 $this->exceptionThrower($ex,true);
 exit;
 }
 }
 /**
 * Insert's a row into the database.
 * 
 * @param string $table Name of the table into which the row has to be inserted
 * @param array $params For binding the values in the where clause
 * @exception $ex
 * @return int $rowcount
 */ 
 public function insert($table, $params)
 {
 try
 {
 $bind = "(:".implode(',:', array_keys($params)).")";
 $query = "INSERT INTO ".$table. "(" .implode(",", array_keys($params)).") VALUE ".$bind;
 $this->beginTransaction();
 $sth = $this->dbo->prepare($query);
 $sth->execute($params);
 $rowcount = $sth->rowCount();
 $this->commitTransaction();
 return $rowcount;
 }
 catch(Exception $ex)
 {
 $this->exceptionThrower($ex,false);
 exit;
 }
 }
 /**
 * Delete's a row from the database.
 * 
 * @param string $table Name of the table into which the row has to be deleted
 * @param array $where All the conditions have to be passed as a array
 * @param array $params For binding the values in the where clause
 * @exception $ex
 * @return int $rowcount
 */ 
 public function delete($table,$where=null,$params=null)
 {
 try
 {
 $query = 'DELETE FROM '.$table;
 if(!is_null($where))
 {
 $query .= ' WHERE ';
 $query .= implode(" AND ",$where);
 }
 $this->beginTransaction();
 $sth = $this->dbo->prepare($query);
 $sth->execute($params);
 $rowcount = $sth->rowCount();
 $this->commitTransaction();
 return $rowcount;
 }
 catch(Exception $ex)
 {
 $this->exceptionThrower($ex,false);
 exit;
 }
 }
 /**
 * Update's a row in the database.
 * 
 * @param string $table Name of the table into which the row has to be updated
 * @param array $set Values to be changed are set as an associative array
 * @param array $where All the conditions have to be passed as a array
 * @param array $params For binding the values in the where clause
 * @exception $ex
 * @return int $rowcount
 */ 
 public function update($table, $set ,$where = null, $params = null)
 {
 try
 {
 $count = 0;
 $str = '';
 $query = "UPDATE {$table} SET ";
 foreach($set as $key=>$val)
 {
 $count += 1;
 if($count > 1)
 {
 $query .= " , ";
 }
 if(is_numeric($val)){
 $query .= $key ." = ". $val;
 }
 $query .= $key ." = '". $val."'";
 }
 echo $query."<br/>";
 if(!is_null($where))
 {
 $query .= " where ". implode(" and ", $where);
 }
 $this->beginTransaction();
 $sth = $this->dbo->prepare($query);
 $sth->execute($params);
 $rowcount = $sth->rowCount();
 $this->commitTransaction();
 return $rowcount;
 }
 catch(Exception $ex)
 {
 $this->exceptionThrower($ex,false);
 exit;
 }
 }
/**
 * @param object $ex Incoming exception object
 * @param bool $isSelect Useful for instantiating a roll back
 */ 
 private function exceptionThrower($ex, $isSelect = true)
 {
 if(!$isSelect)
 {
 $this->rollbackTransaction();
 }
 echo "Exception in the: ".get_class($this).
 " class. <b>Generated at line number:</b> ".$ex->getLine().
 "<br/> <b>Exception:</b> ".$ex->getMessage().
 "<br/><b>Trace:</b>".$ex->getTraceAsString();
 }
 }

How to use the class.

#Using the file:
$db = DBWrapper::getInstance();
#Selecting data
$table = "shouts";
$columns = array("id","name","post");
$where = array("email like :email");
$params = array('email' => '[email protected]');
$result_set = $db->select($table,$columns,$where, $params);
$result_set = $db->select($table);
foreach($result_set as $result)
{
 echo "<b>Post:</b>".$result['post']."<br/>";
}
#Insert
$table = "shouts";
$insert = array('name'=>'chaitanya','email'=>'[email protected]','post'=>'Congratulations! You have successfully created a Hello World application!', 'ipaddress'=>$ipaddress);
echo "<br/>Count: ".$db->insert("shouts", $insert);
#Update
$table = "shouts";
$set = array('name'=>'code learner', 'email'=>'[email protected]');
$where = array("id = :id");
$values = array('id'=>1);
echo $db->update($table, $set, $where, $values);
//$where = array("id IN (:id0,:id1,:id2,:id3)");
//$where = array("id BETWEEN :id0 and :id1");
#Delete
$table = "shouts";
$where = array("id = :id");
$values = array('id'=>1);
echo $db->delete($table, $where, $values);

I have written a PDO wrapper, am very new to PHP and this is my first try in OOP-PHP. I request to suggest

• Changes in the way the class can be implemented in a better way
• Features to be added

Answer 1

Seeing as there are no other answers I will give a quick review. You seem to have done a good job with this class. Here are some things I would do:

1. Remove the evil singleton. It is valid to have more than one database connection. Don't limit yourself to a single instance. Use Dependency Injection.
2. Provide transaction checking: Add an inTransaction property to the class, set it when you start a transaction. Throw an exception if your code tries to start more than one transaction or commit or rollback without being in a transaction.
3. Remove the transaction from your methods. An insert or update call may be part of a much wider transaction - it should not be committed without the rest working.
4. Get rid of exceptionThrower. Create an Exception_DB class that extends an exception. If you should rollback do so before you throw. There is some really useful information that you could write in this exception class if you pass the DB. PDO has the errorCode and errorInfo which will give you an idea of why your SQL statement is wrong.

• LearnCode: Thanks paul for the comments. I will try to implement what you have suggested and get back here again!