Tutorials, Tips and Technology: PHP- PDO Wrapper Class

An easy to use PDO wrapper class. Includes methods like

PDO::getRecord()
PDO::insertRecord()
PDO::updateRecord()
PDO::getQuery()

for easy coding and better error control. Please let me know your comments.

<?php
class extendedPDO extends PDO{
private $arQuery = array();
/*
Parameters same as original PDO::exec
*/
public function exec($statement){
if(!empty($statement)){
try{
$result = parent::exec($statement);
if($result === FALSE)$this->triggerError();
}catch(PDOException $e){
$this->triggerError($e);
}
$this->arQuery[] = $statement;
return $result;
}else{
return FALSE;
}
}
/*
Parameters same as original PDO::query
*/
public function query($statement, $fetchMode = null, $objORClassnameORColNo = null, $ctorargs = null){
if(!empty($statement)){
try{
$this->stmt = parent::query($statement, $fetchMode, $objORClassnameORColNo, $ctorargs);
$this->arQuery[] = $statement;
if($this->stmt === FALSE)$this->triggerError();
}catch(PDOException $e){
$this->triggerError($e);
}
return $this->stmt;
}else{
return FALSE;
}
}
/*
Function to return previously executed query
@param - The index of the query. If none is provided, then last query is returned
*/
public function getQuery($idx = null){
if(empty($this->arQuery))return false;
if(is_null($idx)){
$idx = count($this->arQuery) - 1;
}
return $this->arQuery[$idx];
}
/*
Function to retrieve existing row(s) from the database
@param $tblName - DB Table Name
@param $conditions - Condition for updation as associative array(eg. array('id' => ))
@param $values - the value to be inserted as an associative array (eg. array('id' => 1, 'uid' => '007', 'name' => 'James Bond'))
@param $limit - Number of rows to retrieve. If $limit is null, then all matching rows are retrieved. If $row is an array, say array( 50, 10)
Then rows 50 - 60 will be returned. If limit is 1, then result is returned as an associative array
*/
function getRecord($tblName, $conditions, $limit = 1){
$whereSql = $this->_getWhereSQL($conditions);
$limitSql = $this->_getLimitSQL($limit);
$stmt = $this->query("SELECT * FROM $tblName $whereSql $limitSql");
if($stmt !== FALSE && $limit == 1){
return $stmt->fetch(PDO::FETCH_ASSOC);
}else{
return $stmt;
}
}
/*
Function to insert a new row into the database
@param $tblName - DB Table Name
@param $values - the value to be inserted as an associative array (eg. array('id' => 1, 'uid' => '007', 'name' => 'James Bond'))
*/
public function insertRecord($tblName, $values) {
$keys = array_keys($values);
$fields = '(`' . implode('`, `', $keys) . '`)';
$values = '(' . implode(', ', array_map(array($this, 'quote'), $values)) . ")";
return $this->exec("INSERT INTO `$tblName` $fields VALUES $values");
}
/*
Function to update an existing row in the database
@param $tblName - DB Table Name
@param $conditions - Condition for updation as associative array(eg. array('id' => ))
@param $values - the value to be inserted as an associative array (eg. array('id' => 1, 'uid' => '007', 'name' => 'James Bond'))
@param $limit - Number of rows to update. If $limit is null, then all matching rows are updated. If $row is an array, say array( 50, 10)
Then rows 50 - 60 will be updated
*/
public function updateRecord($tblName, $conditions, $values, $limit = 1) {
$fields = $this->_getValues($values);
$whereSql = $this->_getWhereSQL($conditions);
$limitSql = $this->_getLimitSQL($limit);
return $this->exec("UPDATE `$tblName` SET $fields $whereSql $limitSql");
}
function triggerError($e = FALSE){
// Here do your error management procedures
// You may sent a notification mail with the error details
echo "<br/><b>Database Error:</b> ";
if($e){
echo $e->getMessage();
}else{
$errorInfo = $this->errorInfo();
echo "[$errorInfo[1]] $errorInfo[2]";
}
exit;
}
private function _getValues($values = null){
if(is_null($values)){
$values = '';
}elseif(is_array($values)){
$arField = array();
foreach($values as $field => $value){
$value = $this->quote($value);
$arField[] = "`$field` = $value";
}
$values = implode(', ', $arField);
}else{
$values = '';
}
return $values;
}
private function _getWhereSQL($condition){
//$condition = $this->_getCondition($condition);
if(is_null($condition)){
$values = '';
}elseif(is_array($condition)){
$arField = array();
foreach($condition as $field => $value){
$value = $this->quote($value);
$arField[] = "`$field` = $value";
}
$condition = 'WHERE ' . implode(' AND ', $arField);
}else{
$condition = '';
}
return $condition;
}
private function _getLimitSQL($limit = null){
if(is_null($limit)){
$limit = '';
}elseif(is_array($limit)){
$limit = "LIMIT $limit[0], $limit[1]";
}else{
$limit = "LIMIT $limit";
}
return $limit;
}
}

?>

DB Schema

CREATE TABLE `test`.`weber_test` (
`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`title` VARCHAR( 255 ) NOT NULL ,
`author` VARCHAR( 50 ) NOT NULL ,
`price` VARCHAR( 5 ) NOT NULL
) ENGINE = MYISAM

example usage

<?php
$dbType = 'mysql';
$dbHost = 'localhost';
$dbUser = 'root';
$dbPass = '';
$dbName = 'test';
$pdo = new extendedPDO("$dbType:host=$dbHost;port=4040;dbname=$dbName", $dbUser, $dbPass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

$pdo->insertRecord('weber_test', array('title' => 'Around the World in 80 Days', 'author' => 'Julie Verne', 'price' => '$90'));
$id = $pdo->lastInsertId();
$pdo->updateRecord('weber_test', array('id' => $id), array('author' => 'Jules Verne', 'price' => '$' . mt_rand(0, 500) ));
$record = $pdo->getRecord('weber_test', array('id' => $id));

print <<<HERE
Inserted Data<br/>
ID : $id<br/>
Title : {$record['title']}<br/>
Author : {$record['author']}<br/>
Price : {$record['price']}<br/><br/>
HERE;

$stmt = $pdo->getRecord('weber_test', null, null);
if($stmt){
echo '<table>';
$stmt->setFetchMode(PDO::FETCH_ASSOC);
while($record = $stmt->fetch()){
echo "<tr><td>{$record['id']}</td><td>{$record['title']}</td><td>{$record['author']}</td><td>{$record['price']}</td></tr>";
}
echo '</table>';
}
?>

Tutorials, Tips and Technology

Saturday, December 20, 2008

PHP- PDO Wrapper Class

No comments:

All Tutorials