PDO is abstracted!

Hi Guys

Few days back I created a PDO class. I would like to share it with you all, perhaps, it may come handy. You find it on PHPClasses too. http://www.phpclasses.org/package/8853-PHP-Database-access-wrapper-based-on-PDO.html

The idea is to abstraction PDO itself. Developers don’t need to know about the implementation if it is either a PDO or simple PHP database interaction code. (To get a better understanding you might want to look into this – http://www.phpclasses.org/package/6535-PHP-Access-different-types-of-SQL-database.html.) Just follow the implementation in the sample class. It might not look like what I have mentioned but in case your team is big it might look true.

The features supported 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 are going to 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’s it guys. You will find it useful in case working with a large and distributed team where developer don’t need to know if the DB implementation is generic or PDO.

Cheers!