4
\$\begingroup\$

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' => 'dan@harper.net');

$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'=>'learner@sfo.net','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'=>'learner@code.com');
$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
\$\endgroup\$
3
\$\begingroup\$

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.
\$\endgroup\$
  • \$\begingroup\$ Thanks paul for the comments. I will try to implement what you have suggested and get back here again! \$\endgroup\$ – LearnCode Oct 28 '11 at 2:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.