ReTweet
Author: Nitesh Apte
Posted on: 2015-01-26
Package: PDO Database Abstraction Layer
The idea is to abstract PDO itself. Developers do not need to know about if the implementation uses either PDO or some other PHP database access code.
Read this article to learn more how it works.
Read this article to learn more how it works.
PDO is abstracted!
Some time ago I created a PDO class. I would like to share that class with you all, as it may come handy.
The idea is to abstract PDO itself. Developers do not need to know if the implementation uses either PDO or some other PHP database access code.
Just follow the implementation in the example script. It might not look like what I have mentioned but in case your team is big it might look like that.
The supported features are :
1. SELECT
2. INSERT
3. UPDATE
4. DELETE
5. TRANSACTIONS
Let’s jump into the code.
DBConfig.php
<?php
/**
* Values for database
*
* @author Nitesh Apte
* @copyright 2014 Nitesh Apte
* @version 1.0
* @license GPL Version 3.0
*/
/**
* Database Connectivity Settings
*/
define('DBHOST', 'localhost');
define('DBUSER', 'user');
define('DBPASS', 'pass');
define('DBNAME', 'databaseName');
define('DBPORT', 3306);
/**
* Choose Database
*/
define('DBTYPE', 'mysql');
?>
abstract.DBConfig.php. This class initializes the values which has to be used.
<?php
require 'DBConfig.php';
/**
* Initialize configuration of database
*
* @author Nitesh Apte
* @copyright 2014 Nitesh Apte
* @version 1.0
* @license GPL Version 3.0
*/
abstract class DBConfig {
/**
* @var Database host
* @access protected
*/
protected $sqlHost;
/**
* @var Database user
* @access protected
*/
protected $sqlUser;
/**
* @var Database password
* @access protected
*/
protected $sqlPass;
/**
* @var Database name
* @access protected
*/
protected $sqlDB;
/**
* @var Database type
* @access protected
*/
protected $dbType;
/**
* @var Database port
* @access protected
*/
protected $dbport;
/**
* Set the configuration values for Database Connectivity
*
* @param none
* @return none
*/
protected function initializeConfiguration() {
$this->dbType = DBTYPE;
$this->sqlHost = HOST;
$this->sqlUser = USER;
$this->sqlPass = PASSWORD;
$this->sqlDB = DNAME;
$this->dbport = PORT;
}
}
?>
Interface DBConfigInterface. Might help in case you have plans for DI and IOC.
<?php
/**
* Interface declaring the methods to implement for database interaction.
*
* @author Nitesh Apte
* @copyright 2014 Nitesh Apte
* @version 1.0
* @license GPL Version 3.0
*/
interface DBConfigInterface {
/**
* Get the single instance of class
*
* @param none
* @return Object
*/
public static function getInstance();
/**
* Method for connecting to database
*
* @param none
* @return none
*/
public function makeConnection();
/**
* Execute a sql query
*
* @param String $query
* @return Object
*/
public function executeSql($query);
/**
* Begin the transaction
*
* @param none
* @return none
*/
public function beginTransaction();
/**
* Commit the transaction
*
* @param none
* @return none
*/
public function commitTransaction();
/**
* Rolls back the transaction
*
* @param none
* @return none
*/
public function rollbackTransaction();
/**
* Fetch associative array
*
* @param none
* @return none
*/
public function fetchAssoc();
/**
* Fetch enumerated array
*
* @param none
* @return none
*/
public function fetchArray();
/**
* Fetch Object instead of array
*
* @param none
* @return none
*/
public function fetchObject();
/**
* Fetch the number of affected rows
*
* @param none
* @return int number of rows
*/
public function affectedRows();
/**
* Fetch the last inserted id
*
* @param noe
* @return int last row id of table
*/
public function lastID();
/**
* Fetch the ids of last entry
*
* @param int $size
*/
public function multipleID($size);
/**
* Frees the database result
*
* @param none
* @return none
*/
public function freeResult();
}
?>
And here is the main class of PDO for database interaction
<?php
require 'abstract.DBConfig.php';
require 'interface.DBConfigInterface.php';
/**
* The main class of PDO for database interaction
*
* @author Nitesh Apte
* @copyright 2014 Nitesh Apte
* @version 1.0
* @license GPL Version 3.0
*/
final class PDOManager extends DBConfig implements DBConfigInterface, Serializable {
/**
* Variable holding the PDOManager instance.
*
* @var PDOManager
* @access static
*/
private static $singleInstance;
protected $pdoObject;
private $prepareStatement;
/**
* Create the single instance of class
*
* @param none
* @return Object self::$singleInstance Instance
*/
public static function getInstance() {
if(!(self::$singleInstance instanceof self)) {
self::$singleInstance = new self();
}
return self::$singleInstance;
}
/**
*
*/
private function __construct() {
parent::initializeConfiguration();
$this->makeConnection();
}
/**
* Method for connecting to database
*
* @param none
* @return none
*/
public function makeConnection() {
$this->pdoObject = new PDO($this->dbType.":host=".$this->sqlHost.";dbname=".$this->sqlDB.";charset=utf8", $this->sqlUser, $this->sqlPass, array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
}
/**
* Execute a sql query
*
* @param String $query
* @return Object
*/
public function executeSql($query, $parameter = array()) {
$this->prepareStatement = $this->pdoObject->prepare($query);
for($i = 0; $i < sizeof($parameter); $i++) {
$this->prepareStatement->bindParam(":x".intval($i+1), $parameter[$i]);
}
$this->prepareStatement->execute();
return $this;
}
/**
* Begin the transaction
*
* @param none
* @return none
*/
public function beginTransaction() {
$this->pdoObject->beginTransaction();
return $this;
}
/**
* Commit the transaction
*
* @param none
* @return none
*/
public function commitTransaction() {
$this->pdoObject->commit();
return $this;
}
/**
* Rolls back the transaction
*
* @param none
* @return none
*/
public function rollbackTransaction() {
$this->pdoObject->rollBack();
return $this;
}
/**
* Fetch associative array
*
* @param none
* @return none
*/
public function fetchAssoc() {
$result = $this->prepareStatement->fetchAll(PDO::FETCH_ASSOC);
$this->freeResult();
return $result;
}
/**
* Fetch enumerated array
*
* @param none
* @return none
*/
public function fetchArray() {
$result = $this->prepareStatement->fetchAll(PDO::FETCH_BOTH);
$this->freeResult();
return $result;
}
/**
* Fetch Object instead of array
*
* @param none
* @return none
*/
public function fetchObject() {
$result = $this->prepareStatement->fetchAll(PDO::FETCH_OBJ);
$this->freeResult();
return $result;
}
/**
* Fetch the number of affected rows
*
* @param none
* @return int number of rows
*/
public function affectedRows() {
return $this->prepareStatement->rowCount();
}
/**
* Fetch the last inserted id
*
* @param noe
* @return int last row id of table
*/
public function lastID() {
return intval($this->pdoObject->lastInsertId());
}
/**
* Fetch the ids of last entry
*
* @param int $size
*/
public function multipleID($size) {
$lastID = intval($this->lastID());
for($i = $lastID;$i < ($lastID + $size);$i++) {
$lastIDs[] = $i;
}
return $lastIDs;
}
/**
* Frees the database result
*
* @param none
* @return none
*/
public function freeResult() {
$this->prepareStatement->closeCursor();
}
/**
* (non-PHPdoc)
* @see Serializable::serialize()
*/
public function serialize() {
throw new Exception("Serialization is not supported.");
}
/**
* (non-PHPdoc)
* @see Serializable::unserialize()
*/
public function unserialize($serialized) {
throw new Exception("Serialization is not supported.");
}
/**
* Override clone method to stop cloning of the object
*
* @throws Exception
*/
private function __clone() {
throw new Exception("Cloning is not supported in singleton class");
}
/**
* Make connection null and void. Comment the statement if you don't want this.
*
* @param none
* @return none
*/
public function __destruct() {
$this->pdoObject = null;
}
}
?>
This is how you can use this class: class.UserProfile.php
<?php
require 'class.PDOManager.php';
/**
* Example class illustrating the use of PDOManager.
* User profile acivities like login, change details, etc.
*
* @author Nitesh Apte
* @copyright 2014 Nitesh Apte
* @version 1.0
* @license GPL Version 3.0
*/
class UserProfile {
private $dataObject;
public function __construct() {
$this->dataObject = PDOManager::getInstance();
}
/**
* SELECT query example.
* Method for user login.
*/
public function _userLogin($_userName, $_userPass) {
$sql = "SELECT * FROM user_table, user_access_level_table WHERE user_table.user_uname = :x1 AND user_table.user_passwd = :x2";
$result = $this->dataObject->executeSql($sql, array($_userName, $_userPass))->fetchAssoc();
return $result;
}
/**
* INSERT query example.
* Method for user registration
*/
public function _userRegister($_userName, $_userFname, $_userLname, $_userEmail, $_userPass) {
$sql = "INSERT INTO user_table(user_uname, user_fname, user_lname, user_email, user_passwd, user_activation_code, user_join_date) VALUES (:x1, :x2, :x3, :x4, :x5, '".md5(trim($_userEmail))."' , '".date("Y-m-d H:i:s",time())."')";
$result = $this->dataObject->executeSql($sql, array($_userName, $_userFname, $_userLname, $_userEmail))->affectedRows();
return $result;
}
/**
* UPDATE query example.
* Method to activate user's account
*/
public function _userActivate($_activationCode){
$sql = "UPDATE user_table SET user_status='Active' WHERE user_activation_code=:x1 AND user_status='Inactive'";
$result = $this->dataObject->executeSql($sql, array($_activationCode))->affectedRows();
return $result;
}
/**
* DELETE query example.
* Method to activate user's account
*/
public function _deleteUser($_userID){
$sql = "DELETE FROM user_table WHERE user_id=:x1 AND user_status='Inactive'";
$result = $this->dataObject->executeSql($sql, array($_userID))->affectedRows();
return $result;
}
}
?>
So, that’ is it. You will find it useful in case working where developer does no’t need to know if the implementation uses PDO or some other database access API.
You need to be a registered user or login to post a comment
1,450,098 PHP developers registered to the PHP Classes site.
Be One of Us!
Login Immediately with your account on:
 |  | ||
 |  | ||
 |  |
Comments:
No comments were submitted yet.
