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
