Posted: June 1, 2013
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 ;-PWithout any further ado:
db.class.php
<?phpclass 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