Simple PDO Wrapper Class and Functions

Simple PDO Wrapper Class and Functions

Simple PDO Wrapper Class and Functions

Hey folks, since PDO is taking over, I figured it was prime time for me to jump the bandwagon of direct db access, and take the plunge into PDO.

As a result, I have built myself a nice and simple PDO Wrapper class and some extra functions to do all the work that one would need to do against a MySQL database.

So we are going to split this up into the 2 files I have setup for my testing and environment, all are commented, and if you ‘do not get it’, well, maybe you should seek other hand holders to guide you through the basics of programming for the web ;-P

Without any further ado:

db.class.php

<?php

class o7thDB {

	/* ------------------------------------------------------------------ */
	// Public Properties
	/* ------------------------------------------------------------------ */
	public $Host 		   = '';		 // The host server we are connecting to
	public $Name 		   = '';		 // The name of the database
	public $User 		   = '';		 // The user to login with
	public $Pass 		   = '';		 // The password to login with
	public $Query 		  = '';		 // The query to execute
	public $Params 		 = array();	// An array of parameters to pass to the query for execution
	public $Exceptions 	 = '';		 // Returns a string representing the exception that occurred if any
	public $RecordCount 	= 0;		  // Returns a count of records returned, only used on SelectAll
	public $LastID		 = 0;		  // Returns the last ID of the reocrd inserted, per the Execute function
	/* ------------------------------------------------------------------ */
	// Internal Properties
	/* ------------------------------------------------------------------ */
	protected $DBHandle;
	/* ------------------------------------------------------------------ */
	// Connect to our db, and set the handle
	/* ------------------------------------------------------------------ */
	protected function Connect(){
		$dsn = array("mysql:host=$this->Host;dbname=$this->Name", $this->User, $this->Pass);
		try{
			// Set out internal db handle with the dsn values from above
			$this->DBHandle = new PDO($dsn[0], $dsn[1], $dsn[2]);
			// if the db type is mySQL, set some attributes
			$this->DBHandle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
			$this->DBHandle->setAttribute(PDO::ATTR_PERSISTENT, true);
			$this->DBHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			unset($dsn);
		} catch (PDOException $e) {
			// this should catch any error that may occur when trying to set our handle
			$this->Exceptions = $e->getMessage();
			exit;
		}
	}

	// Destroying Everything
	public function __destruct(){
		$this->DBHandle = null;
		$this->Cache = null;
		$this->Host = null;
		$this->Name = null;
		$this->User = null;
		$this->Pass = null;
		$this->Query = null;
		$this->Params = array();
		$this->Exceptions = null;
		unset($this->DBHandle, $this->Cache, $this->Host, $this->Name, $this->User, $this->Pass, 
				$this->Query, $this->Params, $this->Exceptions);
	}

	// Executes a query against the database, returns boolean success
	public function Execute(){
		$ret = false;
		// connect to the db, and get the handle
		$this->Connect();
		try{
			// prepare our query for execution
			$stmt = $this->DBHandle->prepare($this->Query);
			//Bind our parameters
			$this->BindParameters($stmt, $this->Params);
			// execute the query, passing in any parameters necessary for execution, then return if it was successful or not
			$ret = $stmt->execute($this->Params);
			$this->LastID = $this->DBHandle->lastInsertId(); 
		}catch (Exception $e) {
			// Try to catch any exception and throw it into our Exceptions property.
			$this->Exceptions = $e->getMessage();
			$ret = false;
		}
		return $ret;
	}

	// Executes a select statement against the database, single record array
	public function SelectSingle(){
		// connect to the db, and get the handle
		$this->Connect();
		try{
			// prepare our query for execution
			$stmt = $this->DBHandle->prepare($this->Query);
			//Bind our parameters
			$this->BindParameters($stmt, $this->Params);
			// execute the query, passing in any parameters necessary for execution, then return if it was successful or not
			$stmt->execute($this->Params);
			// fetch the 1st row into a single record array
			$ret = $stmt->fetch();
			return $ret;
			// probably never gets fired, but we'll try anyways....    clean up
			$stmt->closeCursor();
			unset($stmt, $ret);
		}catch (Exception $e) {
			// Try to catch any exception and throw it into our Exceptions property.
			$this->Exceptions = $e->getMessage();
		}
	}

	// Executes a select statement against the database, returns an associative array, also populates the record count property
	public function SelectAll(){
		// connect to the db, and get the handle
		$this->Connect();
		try{
			// prepare our query for execution
			$stmt = $this->DBHandle->prepare($this->Query);
			//Bind our parameters
			$this->BindParameters($stmt, $this->Params);
			// execute the query, passing in any parameters necessary for execution, then return if it was successful or not
			$stmt->execute($this->Params);
			// fetch the 1st row into an associative array of records
			$ret = $stmt->fetchAll(PDO::FETCH_ASSOC);
			// Populate our RecordCount return property
			$this->RecordCount = count($ret);
			return $ret;
			// probably never gets fired, but we'll try anyways....    clean up
			$stmt->closeCursor();
			unset($stmt, $ret);
		}catch (Exception $e) {
			// Try to catch any exception and throw it into our Exceptions property.
			$this->Exceptions = $e->getMessage();
		}
	}

	// Prepare and bind our parameters
	protected function BindParameters($Stmnt, $Params){
		// loop over params, grab the length, datatype, and value
		$pCt = count($Params);
		for($i = 0; $i < $pCt; ++$i){
			switch(strtolower(gettype($Params[$i]))){
				case 'boolean':
					$Stmnt->bindParam('?', $Params[$i], PDO::PARAM_BOOL);	
					break;
				case 'integer':
				case 'double':
				case 'float':
					$Stmnt->bindParam('?', $Params[$i], PDO::PARAM_INT);	
					break;
				case 'null':
					$Stmnt->bindParam('?', $Params[$i], PDO::PARAM_NULL);	
					break;
				default:
					$Stmnt->bindParam('?', $Params[$i], PDO::PARAM_STR, strlen($Params[$i]));	
			}
		}
	}

}
?>

db.functioning.php

<?php

	/* ------------------------------------------------------------------ */
	// Change the path here to the class file
	require_once($_SERVER['DOCUMENT_ROOT'] . '/inc/classes/db.class.php');
	/* ------------------------------------------------------------------ */
	// Retrieve an associative array of records from the query run
	/* ------------------------------------------------------------------ */
	function SelectAll($args){
		// fire up the class
		$db = new o7thDB();
		// sett all our properties
		$db->Host = $args['DB']['DBHost'];
		$db->Name = $args['DB']['DBName'];
		$db->User = $args['DB']['DBUser'];
		$db->Pass = $args['DB']['DBPass'];
		// Set a query to run	
		$db->Query = $args['Query']['SQL'];
		// Set some parameters to be run against the query
		$db->Params = $args['Query']['Parameters'];
		// clean up the methods arguments as they are not necessary past this point
		$args = null;
		unset($args);
		// Retrieve all records into an associative array of records
		$ret = $db->SelectAll();	
		// If there is an exception, show it
		echo $db->Exceptions;
		// Clean up
		$db = null;
		unset($db);
		// Return the array
		return $ret;
	}
	/* ------------------------------------------------------------------ */
	// Retrieve an associative array of columns/1 record from the query run
	/* ------------------------------------------------------------------ */
	function SelectSingle($args){
		// fire up the class
		$db = new o7thDB();
		// sett all our properties
		$db->Host = $args['DB']['DBHost'];
		$db->Name = $args['DB']['DBName'];
		$db->User = $args['DB']['DBUser'];
		$db->Pass = $args['DB']['DBPass'];
		// Set a query to run	
		$db->Query = $args['Query']['SQL'];
		// Set some parameters to be run against the query
		$db->Params = $args['Query']['Parameters'];
		// clean up the methods arguments as they are not necessary past this point
		$args = null;
		unset($args);
		// Select a single record into a 1 record array
		$ret = $db->SelectSingle();
		// If there is an exception, show it
		echo $db->Exceptions;
		// Clean up
		$db = null;
		unset($db);
		// Return the array
		return $ret;
	}
	/* ------------------------------------------------------------------ */
	// Get a count of records from the query run
	/* ------------------------------------------------------------------ */
	function GetRecordCount($args){
		// fire up the class
		$db = new o7thDB();
		// sett all our properties
		$db->Host = $args['DB']['DBHost'];
		$db->Name = $args['DB']['DBName'];
		$db->User = $args['DB']['DBUser'];
		$db->Pass = $args['DB']['DBPass'];
		// Set a query to run	
		$db->Query = $args['Query']['SQL'];
		// Set some parameters to be run against the query
		$db->Params = $args['Query']['Parameters'];
		// clean up the methods arguments as they are not necessary past this point
		$args = null;
		unset($args);
		// Retrieve all records into an associative array of records, we really only need to fire this up to get the record count
		$db->SelectAll();	
		// If there is an exception, show it
		echo $db->Exceptions;
		// Get the count of records returned
		$rCt = $db->RecordCount;
		// Clean up
		$db = null;
		unset($db);
		$ret = null;
		unset($ret);
		// Return our record count
		return $rCt;
	}
	/* ------------------------------------------------------------------ */
	// Execute a query against the statement specified, and return a boolean value
	/* ------------------------------------------------------------------ */
	function Execute($args){
		global $lastId;
		// fire up the class
		$db = new o7thDB();
		// sett all our properties
		$db->Host = $args['DB']['DBHost'];
		$db->Name = $args['DB']['DBName'];
		$db->User = $args['DB']['DBUser'];
		$db->Pass = $args['DB']['DBPass'];
		// Set a query to run	
		$db->Query = $args['Query']['SQL'];
		// Set some parameters to be run against the query
		$db->Params = $args['Query']['Parameters'];
		// clean up the methods arguments as they are not necessary past this point
		$args = null;
		unset($args);
		// Execute the query, and return if it was successful or not
		$ret = $db->Execute();		
		if($db->LastID > 0){
			$lastId = $db->LastID;
		}
		// If there is an exception, show it
		echo $db->Exceptions;
		// Clean up
		$db = null;
		unset($db);
		// Return whether the execution was successful or not
		return $ret;
	}
	/* ------------------------------------------------------------------ */

?>

 

Now, by all means, if you can make this better, leave me some comments with your suggestions, and as I figure out better ways to do this, I will post them here.

Have fun coding!

~Kevin

Categories


Let Us Help

Get You Online

Contact Us Today

Important Cookie Information
Our website uses cookies. By continuing to browse the site you are agreeing to our use of cookies. For more details about cookies and their use, please see our Cookie Policy.