I've made a PDO Wrapper Class here.
Features :
CREATE TABLE SELECTINSERTUPDATEDELETETRUNCATEDROP TABLEAnd ofcourse, you can also run free form queries.PDO prepared statements are used here, so.... We have an advantageThanks to the original source :
https://github.com/mikehenrty/thin-pdo-wrapperI modified it and added a lot more features.Class Source :
<?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
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/51e75be5656cbHere is a better view of the sample test :
http://www.scriptings.tk/paste/51e758a96d3c4Any 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!!!
Show Posts
