My PDO Wrapper Class 07-18-2013, 04:43 AM
#1
I've made a PDO Wrapper Class here.
Features :
CREATE TABLE
SELECT
INSERT
UPDATE
DELETE
TRUNCATE
DROP TABLE
And ofcourse, you can also run free form queries.
PDO prepared statements are used here, so.... We have an advantage
Thanks to the original source : https://github.com/mikehenrty/thin-pdo-wrapper
I modified it and added a lot more features.
Class Source :
Test Source :
Here is a better view of the class source : http://www.scriptings.tk/paste/51e75be5656cb
Here is a better view of the sample test : http://www.scriptings.tk/paste/51e758a96d3c4
Any changes that are to be made?? Please suggest.
You're welcome to criticize.
I'll use it later on for a huge project.
Thanks for reading. Enjoy PHP!!!
Features :
CREATE TABLE
SELECT
INSERT
UPDATE
DELETE
TRUNCATE
DROP TABLE
And ofcourse, you can also run free form queries.
PDO prepared statements are used here, so.... We have an advantage
Thanks to the original source : https://github.com/mikehenrty/thin-pdo-wrapper
I modified it and added a lot more features.
Class Source :
PHP Code:
<?php
/**
* PDO Wrapper: A simple database client utilizing PHP PDO.
*/
/** All types of database supported(thanks to PDO) */
/**
* Queries that can be executed :
* CREATE TABLE
* SELECT
* INSERT
* UPDATE
* DELETE
* TRUNCATE
* DROP TABLE
* /
/**
* Wrapper object for a PDO connection to the database
*
* @package PDO Wrapper
*/
class PDOWrapper {
/**
* Hardcoded database configuration
*/
const DB_DSN_PREFIX_MASTER = '';
const DB_HOST_MASTER = '';
const DB_NAME_MASTER = '';
const DB_USER_MASTER = '';
const DB_PASSWORD_MASTER = '';
const DB_PORT_MASTER = '';
const SLAVE1_DSN_PREFIX = '';
const SLAVE1_HOST = '';
const SLAVE1_NAME = '';
const SLAVE1_USER = '';
const SLAVE1_PASSWORD = '';
const SLAVE1_PORT = '';
const SLAVE2_DSN_PREFIX = '';
const SLAVE2_HOST = '';
const SLAVE2_NAME = '';
const SLAVE2_USER = '';
const SLAVE2_PASSWORD = '';
const SLAVE2_PORT = '';
// note: to add more, stick with the naming convention
/**
* Write all errors to error log
*
* @var boolean
*/
public static $LOG_ERRORS = true;
/**
* Automatically add/update created/updated fields
*
* @var boolean
*/
public static $TIMESTAMP_WRITES = false;
/**
* Dynamic master config creds
*
* @var Array - representing config details
*/
protected $config_master;
/**
* Dynamic slave config creds
*
* @var Array of Arrays - associative arrays of slave creds
*/
protected $config_slaves;
/**
* The PDO objects for the master connection
*
* @var PDO - the Pear Data Object
*/
protected $pdo_master;
/**
* The PDO objects for the slave connection
*
* @var PDO - the Pear Data Object
*/
protected $pdo_slave;
/**
* We will cache any PDO errors in case we want to get out them externally
*
* @var PDOException - for keeping track of any exceptions in PDO
*/
protected $pdo_exception;
/**
* A reference to the singleton instance
*
* @var PDOWrapper
*/
protected static $instance = null;
/**
* method instance.
* - static, for singleton, for creating a global instance of this object
*
* @return - PDOWrapper Object
*/
public static function instance() {
if (!isset(self::$instance)) {
self::$instance = new PDOWrapper();
}
return self::$instance;
}
/**
* Constructor.
* - make protected so only subclasses and self can create this object (singleton)
*/
protected function __construct() {}
/**
* method configMaster
* - configure connection credentials to the master db server
*
* @param host - the host name of the db to connect to
* @param name - the database name
* @param user - the user name
* @param password - the users password
* @param port (optional) - the port to connect using, default to 3306
* @param driver - the dsn prefix
*/
public function configMaster($host, $name, $user, $password, $port=null, $driver='mysql') {
if (!$this->validateDriver($driver)) {
throw new Exception('DATABASE WRAPPER::error, the database you wish to connect to is not supported by your install of PHP.');
}
if (isset($this->pdo_master)) {
error_log('DATABASE WRAPPER::warning, attempting to config master after connection exists');
}
$this->config_master = array(
'driver' => $driver,
'host' => $host,
'name' => $name,
'user' => $user,
'password' => $password,
'port' => $port
);
}
/**
* method configSlave
* - configure a connection to a slave (can be called multiple times)
*
* @param host - the host name of the db to connect to
* @param name - the database name
* @param user - the user name
* @param password - the users password
* @param port (optional) - the port to connect using, default to 3306
* @param driver - the dsn prefix
*/
public function configSlave($host, $name, $user, $password, $port=null, $driver='mysql') {
if (!$this->validateDriver($driver)) {
throw new Exception('DATABASE WRAPPER::error, the database you wish to connect to is not supported by your install of PHP.');
}
if (isset($this->pdo_slave)) {
error_log('DATABASE WRAPPER::warning, attempting to config slave after connection exists');
}
if (!isset($this->config_slaves)) {
$this->config_slaves = array();
}
$this->config_slaves[] = array(
'driver' => $driver,
'host' => $host,
'name' => $name,
'user' => $user,
'password' => $password,
'port' => $port
);
}
/**
* method createConnection.
* - create a PDO connection using the credentials provided
*
* @param driver - the dsn prefix
* @param host - the host name of the db to connect to
* @param name - the database name
* @param user - the user name
* @param password - the users password
* @param port (optional) - the port to connect using, default to 3306
* @return PDO object with a connection to the database specified
*/
protected function createConnection($driver, $host, $name, $user, $password, $port=null) {
if (!$this->validateDriver($driver)) {
throw new Exception('DATABASE WRAPPER::error, the database you wish to connect to is not supported by your install of PHP.');
}
// attempt to create pdo object and connect to the database
try {
//@TODO the following drivers are NOT supported yet: odbc, ibm, informix, 4D
// build the connection string from static constants based on the selected PDO Driver.
if ($driver == "sqlite" || $driver == "sqlite2") {
$connection_string = $driver.':'.$host;
} elseif ($driver == "sqlsrv") {
$connection_string = "sqlsrv:Server=".$host.";Database=".$name;
} elseif ($driver == "firebird" || $driver == "oci") {
$connection_string = $driver.":dbname=".$name;
} else {
$connection_string = $driver.':host='.$host.';dbname='.$name;
}
// add the port if one was specified
if (!empty($port)) {
$connection_string .= "port=$port";
}
// initialize the PDO object
$new_connection = new PDO($connection_string, $user, $password);
// set the error mode
$new_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// return the new connection
return $new_connection;
}
// handle any exceptions by catching them and returning false
catch (PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
}
/**
* method getMaster.
* - grab the PDO connection to the master DB
*/
protected function getMaster() {
// if we have not been configured, use hard coded values
if (!isset($this->config_master)) {
$this->config_master = array(
'driver' => self::DB_DSN_PREFIX_MASTER,
'host' => self::DB_HOST_MASTER,
'name' => self::DB_NAME_MASTER,
'user' => self::DB_USER_MASTER,
'password' => self::DB_PASSWORD_MASTER,
'port' => self::DB_PORT_MASTER
);
}
// if we have not created the master db connection yet, create it now
if (!isset($this->pdo_master)) {
$this->pdo_master = $this->createConnection(
$this->config_master['driver'],
$this->config_master['host'],
$this->config_master['name'],
$this->config_master['user'],
$this->config_master['password'],
$this->config_master['port']
);
}
return $this->pdo_master;
}
/**
* method getSlave.
* - grab the PDO connection to the slave DB, create it if not there
*/
protected function getSlave() {
// if we have not created a slave db connection, create it now
if (!isset($this->pdo_slave)) {
// if no slaves were configured, use hardcoded values
if (!isset($this->config_slaves)) {
$i = 1;
while (defined('self::SLAVE' . $i . '_HOST')
&& constant('self::SLAVE' . $i . '_HOST')) {
$this->config_slaves[] = array(
'driver' => constant('self::SLAVE' . $i . '_DSN_PREFIX'),
'host' => constant('self::SLAVE' . $i . '_HOST'),
'name' => constant('self::SLAVE' . $i . '_NAME'),
'user' => constant('self::SLAVE' . $i . '_USER'),
'password' => constant('self::SLAVE' . $i . '_PASSWORD'),
'port' => constant('self::SLAVE' . $i . '_PORT'),
);
$i++;
}
}
// if no slaves are configured, use the master connection
if (empty($this->config_slaves)) {
$this->pdo_slave = $this->getMaster();
}
// if we have slaves, randomly choose one to use for this request and connect
else {
$random_slave = $this->config_slaves[array_rand($this->config_slaves)];
$this->pdo_slave = $this->createConnection(
$random_slave['driver'],
$random_slave['host'],
$random_slave['name'],
$random_slave['user'],
$random_slave['password'],
$random_slave['port']
);
}
}
return $this->pdo_slave;
}
/**
* method select.
* - retrieve information from the database, as an array
*
* @param string $table - the name of the db table we are retreiving the rows from
* @param array $show - the columns that are to be fetched
* @param array $params - associative array representing the WHERE clause filters
* @param array $compare - array containing the comparison operatios for WHERE clause filters
* @param array $logic - array of logical operators after every condition
* @param int $limit (optional) - the amount of rows to return
* @param int $start (optional) - the row to start on, indexed by zero
* @param array $order_by (optional) - an array with order by clause
* @param bool $use_master (optional) - use the master db for this read
* @return mixed - associate representing the fetched table row, false on failure
*/
public function select($table, $showcols = null, $params = array(),
$compare = null, $logic = null, $limit = null,
$start = null, $group_by = null, $order_by = null,
$use_master = false) {
// building query string
$sql_str = "SELECT";
if (!empty($showcols)) {
$add_comma = false;
foreach ($showcols as $col) {
if ($add_comma) {
$sql_str .= ', ';
}
else {
$add_comma = true;
}
$sql_str .= " $col";
}
}
else {
$sql_str .= " * ";
}
$sql_str .= " FROM $table";
// append WHERE if necessary
$sql_str .= ( count($params)>0 ? ' WHERE ' : '' );
$add_logic = false;
// add each clause using parameter array
if (empty($params)) {
$params = array();
}
$i = 0;
$j = 0;
foreach ($params as $key=>$val) {
// all AND OR after the first clause item has been appended
if ($add_logic) {
if(!empty($logic[$j])) {
$sql_str .= ' '.$logic[$j].' ';
$j++;
}
else{
$sql_str .= ' AND ';
}
}
else {
$add_logic = true;
}
// append clause item
if(!empty($compare[$i])) {
$sql_str .= "$key ".$compare[$i]." :$key";
$i++;
}
else {
$sql_str .= "$key = :$key";
}
}
// add the group by clause if we have one
if (!empty($group_by)) {
$sql_str .= ' GROUP BY';
$add_comma = false;
foreach ($group_by as $group) {
if ($add_comma) {
$sql_str .= ', ';
}
else {
$add_comma = true;
}
$sql_str .= " $group";
}
}
// add the order by clause if we have one
if (!empty($order_by)) {
$sql_str .= ' ORDER BY';
$add_comma = false;
foreach ($order_by as $column => $order) {
if ($add_comma) {
$sql_str .= ', ';
}
else {
$add_comma = true;
}
$sql_str .= " $column $order";
}
}
// now we attempt to retrieve the row using the sql string
try {
// decide which database we are selecting from
$pdo_connection = $use_master ? $this->getMaster() : $this->getSlave();
$pdoDriver = $pdo_connection->getAttribute(PDO::ATTR_DRIVER_NAME);
//@TODO MS SQL Server & Oracle handle LIMITs differently, for now its disabled but we should address it later.
$disableLimit = array("sqlsrv", "mssql", "oci");
// add the limit clause if we have one
if (!is_null($limit) && !in_array($pdoDriver, $disableLimit)) {
$sql_str .= ' LIMIT '.(!is_null($start) ? "$start, ": '')."$limit";
}
$pstmt = $pdo_connection->prepare($sql_str);
// bind each parameter in the array
foreach ($params as $key=>$val) {
$pstmt->bindValue(':'.$key, $val);
}
$pstmt->execute();
// now return the results, depending on if we want all or first row only
if ( !is_null($limit) && $limit == 1 ) {
return $pstmt->fetch(PDO::FETCH_ASSOC);
} else {
return $pstmt->fetchAll(PDO::FETCH_ASSOC);
}
}
catch(PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
}
/**
* method rowcount.
* - retrieve number of rows from a desired table under conditions
*
* @param string $table - the name of the db table we are retreiving the rows from
* @param array $show - the columns that are to be fetched
* @param array $params - associative array representing the WHERE clause filters
* @param array $compare - array containing the comparison operatios for WHERE clause filters
* @param array $logic - array of logical operators after every condition
* @param int $limit (optional) - the amount of rows to return
* @param int $start (optional) - the row to start on, indexed by zero
* @param array $order_by (optional) - an array with order by clause
* @param bool $use_master (optional) - use the master db for this read
* @return mixed - associate representing the fetched table row, false on failure
*/
public function rowcount($table, $selcol = null, $params = array(),
$compare = null, $logic = null, $group_by = null,
$use_master = false) {
// building query string
$sql_str = "SELECT COUNT(";
if (!empty($selcol)) {
$sql_str .= " $selcol ";
}
else {
$sql_str .= " * ";
}
$sql_str .= ") as count FROM $table";
// append WHERE if necessary
$sql_str .= ( count($params)>0 ? ' WHERE ' : '' );
$add_logic = false;
// add each clause using parameter array
if (empty($params)) {
$params = array();
}
$i = 0;
$j = 0;
foreach ($params as $key=>$val) {
// all AND OR after the first clause item has been appended
if ($add_logic) {
if(!empty($logic[$j])) {
$sql_str .= ' '.$logic[$j].' ';
$j++;
}
else{
$sql_str .= ' AND ';
}
}
else {
$add_logic = true;
}
// append clause item
if(!empty($compare[$i])) {
$sql_str .= "$key ".$compare[$i]." :$key";
$i++;
}
else {
$sql_str .= "$key = :$key";
}
}
// add the group by clause if we have one
if (!empty($group_by)) {
$sql_str .= ' GROUP BY';
$add_comma = false;
foreach ($group_by as $group) {
if ($add_comma) {
$sql_str .= ', ';
}
else {
$add_comma = true;
}
$sql_str .= " $group";
}
}
// now we attempt to retrieve the row using the sql string
try {
// decide which database we are selecting from
$pdo_connection = $use_master ? $this->getMaster() : $this->getSlave();
$pdoDriver = $pdo_connection->getAttribute(PDO::ATTR_DRIVER_NAME);
//@TODO MS SQL Server & Oracle handle LIMITs differently, for now its disabled but we should address it later.
$disableLimit = array("sqlsrv", "mssql", "oci");
$pstmt = $pdo_connection->prepare($sql_str);
// bind each parameter in the array
foreach ($params as $key=>$val) {
$pstmt->bindValue(':'.$key, $val);
}
if($pstmt->execute()) {
if(empty($group_by)) {
$result = $pstmt->fetch(PDO::FETCH_ASSOC);
return $result['count'];
}
else {
return $pstmt->fetchAll(PDO::FETCH_ASSOC);
}
}
else {
return false;
}
}
catch(PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
}
/**
* method select_max.
* - retrieve maximum value of a column from a table
*
* @param string $table - the name of the db table we are retreiving the rows from
* @param string $selcol - the column whose maximum is to be fetched
* @param array $params - associative array representing the WHERE clause filters
* @param array $compare - array containing the comparison operatios for WHERE clause filters
* @param array $logic - array of logical operators after every condition
* @param int $limit (optional) - the amount of rows to return
* @param int $start (optional) - the row to start on, indexed by zero
* @param array $order_by (optional) - an array with order by clause
* @param bool $use_master (optional) - use the master db for this read
* @return mixed - associate representing the fetched table row, false on failure
*/
public function select_max($table, $selcol, $params = array(),
$compare = null, $logic = null, $group_by = null,
$use_master = false) {
// building query string
$sql_str = "SELECT MAX($selcol) as max FROM $table";
// append WHERE if necessary
$sql_str .= ( count($params)>0 ? ' WHERE ' : '' );
$add_logic = false;
// add each clause using parameter array
if (empty($params)) {
$params = array();
}
$i = 0;
$j = 0;
foreach ($params as $key=>$val) {
// all AND OR after the first clause item has been appended
if ($add_logic) {
if(!empty($logic[$j])) {
$sql_str .= ' '.$logic[$j].' ';
$j++;
}
else{
$sql_str .= ' AND ';
}
}
else {
$add_logic = true;
}
// append clause item
if(!empty($compare[$i])) {
$sql_str .= "$key ".$compare[$i]." :$key";
$i++;
}
else {
$sql_str .= "$key = :$key";
}
}
// add the group by clause if we have one
if (!empty($group_by)) {
$sql_str .= ' GROUP BY';
$add_comma = false;
foreach ($group_by as $group) {
if ($add_comma) {
$sql_str .= ', ';
}
else {
$add_comma = true;
}
$sql_str .= " $group";
}
}
// now we attempt to retrieve the row using the sql string
try {
// decide which database we are selecting from
$pdo_connection = $use_master ? $this->getMaster() : $this->getSlave();
$pdoDriver = $pdo_connection->getAttribute(PDO::ATTR_DRIVER_NAME);
//@TODO MS SQL Server & Oracle handle LIMITs differently, for now its disabled but we should address it later.
$disableLimit = array("sqlsrv", "mssql", "oci");
$pstmt = $pdo_connection->prepare($sql_str);
// bind each parameter in the array
foreach ($params as $key=>$val) {
$pstmt->bindValue(':'.$key, $val);
}
if($pstmt->execute()) {
if(empty($group_by)) {
$result = $pstmt->fetch(PDO::FETCH_ASSOC);
return $result['max'];
}
else {
return $pstmt->fetchAll(PDO::FETCH_ASSOC);
}
}
else {
return false;
}
}
catch(PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
}
/**
* method select_min.
* - retrieve minimum value of a column from a table
*
* @param string $table - the name of the db table we are retreiving the rows from
* @param string $selcol - the column whose minimum is to be fetched
* @param array $params - associative array representing the WHERE clause filters
* @param array $compare - array containing the comparison operatios for WHERE clause filters
* @param array $logic - array of logical operators after every condition
* @param int $limit (optional) - the amount of rows to return
* @param int $start (optional) - the row to start on, indexed by zero
* @param array $order_by (optional) - an array with order by clause
* @param bool $use_master (optional) - use the master db for this read
* @return mixed - associate representing the fetched table row, false on failure
*/
public function select_min($table, $selcol, $params = array(),
$compare = null, $logic = null, $group_by = null,
$use_master = false) {
// building query string
$sql_str = "SELECT MIN($selcol) as min FROM $table";
// append WHERE if necessary
$sql_str .= ( count($params)>0 ? ' WHERE ' : '' );
$add_logic = false;
// add each clause using parameter array
if (empty($params)) {
$params = array();
}
$i = 0;
$j = 0;
foreach ($params as $key=>$val) {
// all AND OR after the first clause item has been appended
if ($add_logic) {
if(!empty($logic[$j])) {
$sql_str .= ' '.$logic[$j].' ';
$j++;
}
else{
$sql_str .= ' AND ';
}
}
else {
$add_logic = true;
}
// append clause item
if(!empty($compare[$i])) {
$sql_str .= "$key ".$compare[$i]." :$key";
$i++;
}
else {
$sql_str .= "$key = :$key";
}
}
// add the group by clause if we have one
if (!empty($group_by)) {
$sql_str .= ' GROUP BY';
$add_comma = false;
foreach ($group_by as $group) {
if ($add_comma) {
$sql_str .= ', ';
}
else {
$add_comma = true;
}
$sql_str .= " $group";
}
}
// now we attempt to retrieve the row using the sql string
try {
// decide which database we are selecting from
$pdo_connection = $use_master ? $this->getMaster() : $this->getSlave();
$pdoDriver = $pdo_connection->getAttribute(PDO::ATTR_DRIVER_NAME);
//@TODO MS SQL Server & Oracle handle LIMITs differently, for now its disabled but we should address it later.
$disableLimit = array("sqlsrv", "mssql", "oci");
$pstmt = $pdo_connection->prepare($sql_str);
// bind each parameter in the array
foreach ($params as $key=>$val) {
$pstmt->bindValue(':'.$key, $val);
}
if($pstmt->execute()) {
if(empty($group_by)) {
$result = $pstmt->fetch(PDO::FETCH_ASSOC);
return $result['min'];
}
else {
return $pstmt->fetchAll(PDO::FETCH_ASSOC);
}
}
else {
return false;
}
}
catch(PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
}
/**
* method selectMaster.
* - retrieve information from the master database, as an array
*
* @param string $table - the name of the db table we are retreiving the rows from
* @param array $show - the columns that are to be fetched
* @param array $params - associative array representing the WHERE clause filters
* @param array $compare - array containing the comparison operatios for WHERE clause filters
* @param array $logic - array of logical operators after every condition
* @param int $limit (optional) - the amount of rows to return
* @param int $start (optional) - the row to start on, indexed by zero
* @param array $order_by (optional) - an array with order by clause
* @param bool $use_master (optional) - use the master db for this read
* @return mixed - associate representing the fetched table row, false on failure
*/
public function selectMaster($table, $showcols = null, $params = array(),
$compare = null, $logic = null, $limit = null,
$start = null, $group_by = null, $order_by=null) {
return $this->select($table, $showcols, $params, $compare, $logic, $limit, $start, $group_by, $order_by, true);
}
/**
* method selectFirst.
* - retrieve the first row returned from a select statement
*
* @param string $table - the name of the db table we are retreiving the rows from
* @param array $show - the columns that are to be fetched
* @param array $params - associative array representing the WHERE clause filters
* @param array $compare - array containing the comparison operatios for WHERE clause filters
* @param array $logic - array of logical operators after every condition
* @param int $limit (optional) - the amount of rows to return
* @param int $start (optional) - the row to start on, indexed by zero
* @param array $order_by (optional) - an array with order by clause
* @param bool $use_master (optional) - use the master db for this read
* @return mixed - associate representing the fetched table row, false on failure
*/
public function selectFirst($table, $showcols = null, $params = array(),
$compare = null, $logic = null, $limit = null,
$start = null, $group_by = null, $order_by = null,
$use_master = false) {
return $this->select($table, $showcols, $params, $compare, $logic, 1, null, $group_by, $order_by);
}
/**
* method selectFirstMaster.
* - retrieve the first row returned from a select statement using the master database
*
* @param string $table - the name of the db table we are retreiving the rows from
* @param array $show - the columns that are to be fetched
* @param array $params - associative array representing the WHERE clause filters
* @param array $compare - array containing the comparison operatios for WHERE clause filters
* @param array $logic - array of logical operators after every condition
* @param int $limit (optional) - the amount of rows to return
* @param int $start (optional) - the row to start on, indexed by zero
* @param array $order_by (optional) - an array with order by clause
* @param bool $use_master (optional) - use the master db for this read
* @return mixed - associate representing the fetched table row, false on failure
*/
public function selectFirstMaster($table, $showcols = null, $params = array(),
$compare = null, $logic = null, $limit = null,
$start = null, $group_by = null, $order_by = null,
$use_master = true) {
return $this->select($table, $showcols, $params, $compare, $logic, 1, null, $group_by, $order_by, true);
}
/**
* method delete.
* - deletes rows from a table based on the parameters
*
* @param table - the name of the db table we are deleting the rows from
* @param array params - associative array representing the WHERE clause filters
* @param array $compare - array containing the comparison operatios for WHERE clause filters
* @param array $logic - array of logical operators after every condition
* @return bool - associate representing the fetched table row, false on failure
*/
public function delete($table, $params = array(), $compare = array(), $logic = array()) {
// building query string
$sql_str = "DELETE FROM $table";
// append WHERE if necessary
$sql_str .= ( count($params)>0 ? ' WHERE ' : '' );
$add_logic = false;
// add each clause using parameter array
$i = 0;
$j = 0;
foreach ($params as $key=>$val) {
// all AND OR after the first clause item has been appended
if ($add_logic) {
if(!empty($logic[$j])) {
$sql_str .= ' '.$logic[$j].' ';
$j++;
}
else{
$sql_str .= ' AND ';
}
}
else {
$add_logic = true;
}
// append clause item
if(!empty($compare[$i])) {
$sql_str .= "$key ".$compare[$i]." :$key";
$i++;
}
else {
$sql_str .= "$key = :$key";
}
}
// now we attempt to retrieve the row using the sql string
try {
$pstmt = $this->getMaster()->prepare($sql_str);
// bind each parameter in the array
foreach ($params as $key=>$val) {
$pstmt->bindValue(':'.$key, $val);
}
// execute the delete query
$successful_delete = $pstmt->execute();
// if we were successful, return the amount of rows updated, otherwise return false
return ($successful_delete == true) ? $pstmt->rowCount() : false;
}
catch(PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
}
/**
* method update.
* - updates a row to the specified table
*
* @param string $table - the name of the db table we are adding row to
* @param array $params - associative array representing the columns and their respective values to update
* @param array $wheres (Optional) - the where clause of the query
* @param bool $timestamp_this (Optional) - if true we set date_created and date_modified values to now
* @return int|bool - the amount of rows updated, false on failure
*/
public function update($table, $params, $wheres=array(), $compare=array(), $logic=array(), $timestamp_this=null) {
if (is_null($timestamp_this)) {
$timestamp_this = self::$TIMESTAMP_WRITES;
}
// build the set part of the update query by
// adding each parameter into the set query string
$add_comma = false;
$set_string = '';
foreach ($params as $key=>$val) {
// only add comma after the first parameter has been appended
if ($add_comma) {
$set_string .= ', ';
} else {
$add_comma = true;
}
// now append the parameter
$set_string .= "$key=:param_$key";
}
// add the timestamp columns if neccessary
if ($timestamp_this === true) {
$set_string .= ($add_comma ? ', ' : '') . 'date_modified='.time();
}
// lets add our where clause if we have one
$where_string = 'WHERE ';
if(!empty($wheres)) {
$add_logic = false;
// add each clause using parameter array
$i = 0;
$j = 0;
foreach ($wheres as $key=>$val) {
// all AND OR after the first clause item has been appended
if ($add_logic) {
if(!empty($logic[$j])) {
$where_string .= ' '.$logic[$j].' ';
$j++;
}
else{
$where_string .= ' AND ';
}
}
else {
$add_logic = true;
}
// append clause item
if(!empty($compare[$i])) {
$where_string .= "$key ".$compare[$i]." :where_$key";
$i++;
}
else { //Build up the where clause
$where_string .= "$key = :where_$key";
}
}
}
// build final update string
$sql_str = "UPDATE $table SET $set_string $where_string";
// now we attempt to write this row into the database
try {
$pstmt = $this->getMaster()->prepare($sql_str);
// bind each parameter in the array
foreach ($params as $key=>$val) {
$pstmt->bindValue(':param_'.$key, $val);
}
// bind each where item in the array
foreach ($wheres as $key=>$val) {
$pstmt->bindValue(':where_'.$key, $val);
}
// execute the update query
$successful_update = $pstmt->execute();
// if we were successful, return the amount of rows updated, otherwise return false
return ($successful_update == true) ? $pstmt->rowCount() : false;
}
catch(PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
}
/**
* method insert.
* - adds a row to the specified table
*
* @param string $table - the name of the db table we are adding row to
* @param array $params - associative array representing the columns and their respective values
* @param bool $timestamp_this (Optional), if true we set date_created and date_modified values to now
* @return mixed - new primary key of inserted table, false on failure
*/
public function insert($table, $params = array(), $timestamp_this = null) {
if (is_null($timestamp_this)) {
$timestamp_this = self::$TIMESTAMP_WRITES;
}
// first we build the sql query string
$columns_str = '(';
$values_str = 'VALUES (';
$add_comma = false;
// add each parameter into the query string
foreach ($params as $key=>$val) {
// only add comma after the first parameter has been appended
if ($add_comma) {
$columns_str .= ', ';
$values_str .= ', ';
} else {
$add_comma = true;
}
// now append the parameter
$columns_str .= "$key";
$values_str .= ":$key";
}
// add the timestamp columns if neccessary
if ($timestamp_this === true) {
$columns_str .= ($add_comma ? ', ' : '') . 'date_created, date_modified';
$values_str .= ($add_comma ? ', ' : '') . time().', '.time();
}
// close the builder strings
$columns_str .= ') ';
$values_str .= ')';
// build final insert string
$sql_str = "INSERT INTO $table $columns_str $values_str";
// now we attempt to write this row into the database
try {
$pstmt = $this->getMaster()->prepare($sql_str);
// bind each parameter in the array
foreach ($params as $key=>$val) {
$pstmt->bindValue(':'.$key, $val);
}
$pstmt->execute();
$newID = $this->getMaster()->lastInsertId();
// return the new id
return $newID;
}
catch(PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
}
/**
* method insertMultiple.
* - adds multiple rows to a table with a single query
*
* @param string $table - the name of the db table we are adding row to
* @param array $columns - contains the column names
* @param bool $timestamp_these (Optional), if true we set date_created and date_modified values to NOW() for each row
* @return mixed - new primary key of inserted table, false on failure
*/
public function insertMultiple($table, $columns = array(), $rows = array(), $timestamp_these = null) {
if (is_null($timestamp_these)) {
$timestamp_these = self::$TIMESTAMP_WRITES;
}
// generate the columns portion of the insert statment
// adding the timestamp fields if needs be
if ($timestamp_these) {
$columns[] = 'date_created';
$columns[] = 'date_modified';
}
$columns_str = '(' . implode(',', $columns) . ') ';
// generate the values portions of the string
$values_str = 'VALUES ';
$add_comma = false;
foreach ($rows as $row_index => $row_values) {
// only add comma after the first row has been added
if ($add_comma) {
$values_str .= ', ';
} else {
$add_comma = true;
}
// here we will create the values string for a single row
$values_str .= '(';
$add_comma_forvalue = false;
foreach ($row_values as $value_index => $value) {
if ($add_comma_forvalue) {
$values_str .= ', ';
} else {
$add_comma_forvalue = true;
}
// generate the bind variable name based on the row and column index
$values_str .= ':'.$row_index.'_'.$value_index;
}
// append timestamps if necessary
if ($timestamp_these) {
$values_str .= ($add_comma_forvalue ? ', ' : '') . time().', '.time();
}
$values_str .= ')';
}
// build final insert string
$sql_str = "INSERT INTO $table $columns_str $values_str";
// now we attempt to write this multi inster query to the database using a transaction
try {
$this->getMaster()->beginTransaction();
$pstmt = $this->getMaster()->prepare($sql_str);
// traverse the 2d array of rows and values to bind all parameters
foreach ($rows as $row_index => $row_values) {
foreach ($row_values as $value_index => $value) {
$pstmt->bindValue(':'.$row_index.'_'.$value_index, $value);
}
}
// now lets execute the statement, commit the transaction and return
$pstmt->execute();
$this->getMaster()->commit();
return true;
}
catch(PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
$this->getMaster()->rollback();
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
$this->getMaster()->rollback();
return false;
}
}
/**
* method truncate.
* - executes a query that truncates a table
*
* @param string $table - the table we are truncating
* @return mixed - true if done, false on failure
*/
public function truncate($table) {
$sql_str = "TRUNCATE TABLE $table";
return $this->execute($sql_str);
}
/**
* method drop.
* - executes a query that drops a table
*
* @param string $table - the table we are truncating
* @return mixed - true if done, false on failure
*/
public function drop($table) {
$sql_str = "DROP TABLE IF EXISTS $table";
return $this->execute($sql_str);
}
/**
* method execute.
* - executes a query that modifies the database
*
* @param string $query - the SQL query we are executing
* @param bool $use_master (Optional) - whether or not to use the master connection
* @return mixed - the affected rows, false on failure
*/
public function execute($query, $params=array()) {
try {
// use the master connection
$pdo_connection = $this->getMaster();
// prepare the statement
$pstmt = $pdo_connection->prepare($query);
// bind each parameter in the array
foreach ((array)$params as $key=>$val) {
$pstmt->bindValue($key, $val);
}
// execute the query
$result = $pstmt->execute();
// only if return value is false did this query fail
return ($result == true) ? true : false;
}
catch(PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
}
/**
* method create.
* - creates a table in a database
*
* @param string $table - the name of the table we are creating
* @param array $columns - the name of the columns and their respective types
* @param string $primary_key - the primary key for the table
* @return mixed - true on success, false on failure
*/
public function create($tbl_name, $columns, $primary_key=null) {
//Building the query
$query = "CREATE TABLE IF NOT EXISTS $tbl_name (";
$add_comma = false;
foreach($columns as $col => $type) {
if($add_comma) {
$query.= ", ";
}
else {
$add_comma = true;
}
$query.= "$col $type";
}
if(!empty($primary_key)) {
$query.= ", PRIMARY KEY ($primary_key)";
}
$query.= ")";
return $this->execute($query);
}
/**
* method query.
* - returns data from a free form select query
*
* @param string $query - the SQL query we are executing
* @param array $params - a list of bind parameters
* @param bool $use_master (Optional) - whether or not to use the master connection
* @return mixed - the affected rows, false on failure
*/
public function query($query, $params=array(), $use_master=false) {
try {
// decide which database we are selecting from
$pdo_connection = $use_master ? $this->getMaster() : $this->getSlave();
$pstmt = $pdo_connection->prepare($query);
// bind each parameter in the array
foreach ((array)$params as $key=>$val) {
$pstmt->bindValue($key, $val);
}
// execute the query
$pstmt->execute();
// now return the results
return $pstmt->fetchAll(PDO::FETCH_ASSOC);
}
catch(PDOException $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
catch(Exception $e) {
if (self::$LOG_ERRORS == true) {
error_log('DATABASE WRAPPER::'.print_r($e, true));
}
$this->pdo_exception = $e;
return false;
}
}
/**
* method queryFirst.
* - returns the first record from a free form select query
*
* @param string $query - the SQL query we are executing
* @param array $params - a list of bind parameters
* @param bool $use_master (Optional) - whether or not to use the master connection
* @return mixed - the affected rows, false on failure
*/
public function queryFirst($query, $params=array(), $use_master=false) {
$result = $this->query($query, $params, $use_master);
if (empty($result)) {
return false;
}
else {
return $result[0];
}
}
/**
* method getErrorMessage.
* - returns the last error message caught
*/
public function getErrorMessage() {
if ($this->pdo_exception)
return $this->pdo_exception->getMessage();
else
return 'Database temporarily unavailable';
}
/**
* method getError.
* - returns the actual PDO exception
*/
public function getPDOException() {
return $this->pdo_exception;
}
/**
* Validate the database in question is supported by your installation of PHP.
* @param string $driver The DSN prefix
* @return boolean true, the database is supported; false, the database is not supported.
*/
private function validateDriver($driver) {
if (!in_array($driver, PDO::getAvailableDrivers())) {
return false;
} else {
return true;
}
}
/**
* Destructor.
* - release the PDO db connections
*/
function __destruct() {
unset($this->pdo_master);
unset($this->pdo_slave);
}
}
?>Test Source :
PHP Code:
<?php
require('PDOWrapper.php');
$db = PDOWrapper::instance();
/* MySQL Database */
$db->configMaster('localhost', 'test', 'root', '');
$db->configSlave('localhost', 'test', 'root', '');
/* SQLite Database
$pdo->configMaster('/var/www/db/test.sqlite', '', '', '', null, 'sqlite');
$pdo->configSlave('/var/www/db/test.sqlite', '', '', '', null, 'sqlite');*/
/* PgSQL Database
$pdo->configMaster('localhost', 'test', 'somebody', '', null, 'pgsql');
$pdo->configSlave('localhost', 'test', 'somebody', '', null, 'pgsql'); */
echo $db->create("testing",
array("id" => "int not null auto_increment",
"name" => "varchar(20)",
"email" => "varchar(20)",
"stamp" => "timestamp not null default current_timestamp"),
"id");
for($i = 0; $i < 9; $i++) {
$rand = rand();
$db->insert('testing',
array('name' => 'bob'.$rand,
'email' => 'bob'.$rand.'@email.com'));
}
$result = $db->select('testing',
array("id", "name", "email"),
array("id" => 1, "name" => "bob%", "email" => "%3%"),
array(">", "LIKE", "LIKE"),
array("AND","AND"),
20,
null,
array('id'),
array('id' => ''),
false);
echo $db->select_max("testing","id")."<br />";
echo $db->select_min("testing","id",array("name" => "%3%"), array("LIKE"));
print_r($db->rowcount('testing',
null,
array("id" => 1, "name" => "bob%", "email" => "%3%"),
array(">", "LIKE", "LIKE"),
array("AND","AND")));
echo $db->update('testing',
array("id" => 2),
array("id" => 3));
if(empty($result[0])) {
print_r($result);
}
else {
foreach($result as $key => $value) {
echo "Id: ".$value['id']."<br />";
echo "Name: ".$value['name']."<br />";
echo "Email: ".$value['email']."<br />";
echo "<hr />";
}
}
echo $db->delete('testing',
array("id" => 1, "name" => "bob%"),
array("=", "LIKE"),
array("AND"));
print_r($db->query("SELECT COUNT(*) FROM testing WHERE name LIKE :name",array("name" => "%3%")));
echo $db->truncate("testing");
echo $db->drop("testing");
?>Here is a better view of the class source : http://www.scriptings.tk/paste/51e75be5656cb
Here is a better view of the sample test : http://www.scriptings.tk/paste/51e758a96d3c4
Any changes that are to be made?? Please suggest.
You're welcome to criticize.
I'll use it later on for a huge project.
Thanks for reading. Enjoy PHP!!!
![[Image: 2YpkRjy.png]](http://i.imgur.com/2YpkRjy.png)
![[+]](https://sinister.li/images/modern/collapse_collapsed.png)
