Here is little DB class that I've written on myself with PDOs so that the end developer is able to use the methods inside. Could some experts throw some comments on the code
The directory structure is as follows
config/config.ini,
config/Config.php
Dataset.php
DB.php
config/config.ini
Code: Select all
; Database parameters
[DB]
driver="mysql"
host="localhost"
port="3306"
dbname="test"
dbuser="root"
dbpass=""
;Other sections could be added here
[MAIL]
smtp_host="localhost"
smtp_port="25"
Code: Select all
<?php
/*
* Created on 12-Aug-08
*
* To change the template for this generated file go to
* Window - Preferences - PHPeclipse - PHP - Code Templates
*/
Class Config
{
const CONFIG_FILE ='config.ini';
public $properties = array();
function __construct()
{
return $this->properties = parse_ini_file(self::CONFIG_FILE,true);
}
}
?>Code: Select all
<?php
/*
* Created on 13-Aug-08
*
* To change the template for this generated file go to
* Window - Preferences - PHPeclipse - PHP - Code Templates
*/
class Dataset
{
function __set($name, $value)
{
$this->$name = $value;
}
function __get($name)
{
return $this->$name;
}
}
?>Code: Select all
<?php
/*
* Created on 13-Aug-08
*
* To change the template for this generated file go to
* Window - Preferences - PHPeclipse - PHP - Code Templates
*/
/**
* For db configuration to be loaded
*/
require_once('config/Config.php');
/**
* For SQL retrieved data to be in dataset Object format
*/
require_once('Dataset.php');
/* This class is DB class used for database connection, executions of queries and retrieving data
*
* @version: 0.1
* @package Core
* @subpackage DB
*/
class DB
{
/**
* Dsn database connection parameter in order to connect to various kind of databases
* @access private
* @var string
*/
private $dsn;
/**
* database user name
* @access private
* @var string
*/
private $dbuser;
/**
* database user password
* @access private
* @var string
*/
private $dbpassword;
/**
* A connection string variable for the database
* @access private
* @var string
*/
private $connectionString;
/**
* DB constructor method
* @access public
* @return void;
*/
function __construct()
{
$config = new Config();
$this->getDBConnect($config->properties);
}
/**
* getDBConnect method for connecting to a database
* @access private
*
* @return void
*/
private function getDBConnect($iniArray)
{
if(isset($iniArray['DB'])){
$driver = $iniArray['DB']['driver'];
switch($driver) {
case 'mysql':
$host = $iniArray['DB']['host'];
$port = $iniArray['DB']['port'];
$dbname = $iniArray['DB']['dbname'];
$this->dsn = $driver.":host=".$host.";port=".$port.";dbname=".$dbname;
break;
case 'oci':
// code for oracle compatibility
break;
default:
throw new Exception("No database driver details found");
exit;
}
$this->dbuser = $iniArray['DB']['dbuser'];
$this->dbpassword = $iniArray['DB']['dbpass'];
try{
$this->connectionString = new PDO($this->dsn,$this->dbuser,$this->dbpassword,array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true));
$this->connectionString->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch (PDOException $e){
echo $e->getMessage();
exit;
}
}
}
/**
* getSQLData is used for retrival of data from a table or for execution of a stored procedure
* @access public
* @param string $query
* @param array $params
*
* @return object $result Dataset Object
*/
public function getSQLData($query,$params=array())
{
try{
$stmt = $this->connectionString->prepare($query);
$stmt->execute($params);
} catch(PDOException $e){
echo $e->getMessage();
exit;
}
$result = null;
for($i=0; $i<$stmt->rowCount();$i++){
$result[] = $stmt->fetchObject('Dataset');
}
return $result;
}
/**
* Not in Use
* validateSQLValues is used for preventing SQL injections
* @access private
* @param string $data
*
* @return string $data
*/
private function validateSQLValues( $data )
{
if(preg_match('/\'+/si',$data)){
$data = preg_replace("/\'+/si","\\'", $data);
}
return $data;
}
/**
* beginTransaction is used whenever a tranasaction is started
* @access public
*
* @return boolean true
*/
public function beginTransaction()
{
try{
if ($this->connectionString->getAttribute(PDO::ATTR_AUTOCOMMIT)===TRUE) {
$this->connectionString->setAttribute(PDO::ATTR_AUTOCOMMIT,FALSE);
if(!$this->connectionString->getAttribute(PDO::ATTR_AUTOCOMMIT)===FALSE){
throw new Exception("Tranasaction failed to begin");
}
}
echo $this->connectionString->getAttribute(PDO::ATTR_AUTOCOMMIT);
//$this->connectionString->beginTransaction();
}catch (PDOException $e){
echo $e->getMessage();
exit;
}
return true;
}
/**
* commitTransaction is used whenever a tranasaction is commited
* @access public
*
* @return boolean true
*/
public function commitTransaction()
{
try{
//$this->connectionString->commit();
if ($this->connectionString->getAttribute(PDO::ATTR_AUTOCOMMIT)===FALSE){
$this->connectionString->setAttribute(PDO::ATTR_AUTOCOMMIT,TRUE);
if(!$this->connectionString->getAttribute(PDO::ATTR_AUTOCOMMIT)===TRUE){
throw new Exception("Tranasaction failed to commit");
}
}
}catch (PDOException $e){
echo $e->getMessage();
exit;
}
return true;
}
/**
* rollBackTransaction restores database to old state before the transaction started
* @access public
*
* @return boolean true
*/
public function rollBackTransaction()
{
try{
$this->connectionString->rollBack();
}catch (PDOException $e){
echo $e->getMessage();
exit;
}
return true;
}
/**
* buildSQL builds an insert or update query when a table name, columnanmes and values are passed
* @access public
* @param string $table
* @param array $columnnames
* @param array $rowvals
* @param string $type
*
* @return string $querystr
*/
public function buildSQL($table, $columnnames, $rowvals,$type,$condition=NULL)
{
if(count($columnnames) != count($rowvals)){
throw new Exception ("fieldnames and values count does not match");
exit;
}
switch($type){
case 'insert':
$colnameslist = "(" ;
$valuelist = "(" ;
$count = 0 ;
for ($count = 0 ; $count < count($columnnames) ; $count ++) {
if ($count > 0) {
$colnameslist .= "," ;
$valuelist .= "," ;
}
$colnameslist .= $columnnames[$count] ;
$valuelist .= ($this->quote($rowvals[$count])) ;
}
$colnameslist .= ")";
$valuelist .= ")";
$querystr = "insert into " . $table . " " . $colnameslist . " values " . $valuelist;
break;
case 'update':
if(count($columnnames) !=0 ){
$querystr = "update $table set ";
}else{
return;
}
$count = 0;
for($count = 0; $count < count($columnnames); $count++){
$querystr .= $columnnames[$count]."=".$this->quote($rowvals[$count]).",";
}
$querystr = substr($querystr,0,-1);
if($condition !== NULL) {
$querystr .= " ".$condition;
}
break;
}
return $querystr;
}
public function buildMultiInsertSQL($table, $coloumnnames, $rowvals){
$data = "";
for($i=0; $i<count($rowvals); $i++){
$actual_rowvals = array_values($rowvals[$i]);
if(count($coloumnnames) != count($actual_rowvals)){
$msg = true;
}
if(!$msg){
$data .= "(".implode(',',$actual_rowvals)."),";
}
}
if($msg == true){
throw new Exception("An array field count and column names does not match");
exit;
}
$dbcolumnames = implode(',',$coloumnnames);
$sql = "insert into $table ($dbcolumnames) values $data";
$sql = substr($sql,0,-1);
return $sql;
}
/**
* Note: This is a database specific function, it may not be supported in all kind of databases
* For more information on this please refer: http://in2.php.net/manual/en/pdo.quote.php
*
* Quote function escapes the variable.
* This can be used where values could not be bound. More in insert and update queries
* @access public
* @param string $variable
*
* @return string $quoted_variable
*/
public function quote($variable){
$quoted_variable = $this->connectionString->quote($variable);
return $quoted_variable;
}
/**
* This is used for exclusively to run select statements
* @access public
* @param string $select_stmt
*
* @return array $resultset
*/
public function executeSelect($select_stmt){
if(!empty($select_stmt)){
$stmt = $this->connectionString->query($select_stmt);
while ($row = $stmt->fetchObject('Dataset')) {
$resultset[] = $row;
}
}
return $resultset;
}
/**
* This is used for execution of insert, delete, update queries
* @access public
* @param string $query
*
* @return int $affected_rows
*/
public function executeQuery($query){
try{
$pass = false;
$query = trim($query);
if($query === NULL || empty($query)){
return true;
}
if(!$pass){
$affected_rows = $this->connectionString->exec($query);
}
}catch (PDOException $e){
echo "Caught Exception:".$e->getMessage();
}
return $affected_rows;
}
/**
* This will return the last insertID
* @access public
* @param string $name
*
* @return $id;
*/
public function getLastInsertId($name=''){
$id = $this->connectionString->lastInsertId($name);
return $id;
}
public function executeSelectArray($select_stmt){
if(!empty($select_stmt)){
$stmt = $this->connectionString->query($select_stmt);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$resultset[] = $row;
}
}
return $resultset;
}
public function closeDB(){
$this->connectionString = null;
}
/**
* This methods the TestDB Class. Should be removed when DB class is fully developed
*
*/
public function TestDB(){
$createSQL = "create table Administrator ( adminId varchar(20) not null, surname varchar(20), firstname varchar(20) not null,".
" department varchar(20),primary key(adminId))ENGINE=InnoDB;";
//$this->executeQuery($createSQL);
//Testing executeSelect
$firstSELECT = "SELECT * from administrator";
echo "<pre>";
print_r($this->executeSelect($firstSELECT));
//Testing buildSQL for insert
$table = 'administrator';
$fields1 = array('adminId','surname','firstname','department');
$values1 = array('user1','Bill',' Krishna','WebDevelopment');
$values2 = array('user2','Steve','Johns','WebDevelopment');
$values3 = array('user3','Bauer','Chris','ProjectManagement');
$values4 = array('user4','Papa','Shango','ProjectManagement');
$values5 = array('user5','Man','Hit','Operations');
//$this->executeQuery($this->buildSQL($table,$fields1,$values1,'insert'));
//$this->executeQuery($this->buildSQL($table,$fields1,$values2,'insert'));
//$this->executeQuery($this->buildSQL($table,$fields1,$values3,'insert'));
//$this->executeQuery($this->buildSQL($table,$fields1,$values4,'insert'));
//$this->executeQuery($this->buildSQL($table,$fields1,$values5,'insert'));
//Testing buildSQL for update
$update_fields1 = array('surname'); $update_values1 = array('P'); $condition1 = " WHERE adminId='user1'";
$update_fields2 = array('firstname'); $update_values2 = array('Basha'); $condition2 = " WHERE adminId='user2'";
$update_fields3 = array('department'); $update_values3 = array('Architect'); $condition3 = " WHERE adminId='user3'";
$update_fields4 = array('department'); $update_values4 = array('Architect'); $condition4 = " WHERE adminId='user4'";
$update_fields5 = array('department'); $update_values5 = array('ProjectManagement'); $condition5 = " WHERE adminId='user5'";
$this->executeQuery($this->buildSQL($table,$update_fields1,$update_values1,'update',$condition1));
$this->executeQuery($this->buildSQL($table,$update_fields2,$update_values2,'update',$condition2));
$this->executeQuery($this->buildSQL($table,$update_fields3,$update_values3,'update',$condition3));
$this->executeQuery($this->buildSQL($table,$update_fields4,$update_values4,'update',$condition4));
$this->executeQuery($this->buildSQL($table,$update_fields5,$update_values5,'update',$condition5));
$delete1 = "DELETE from administrator WHERE adminId='user1'";
$this->executeQuery($delete1);
$this->beginTransaction();
$this->executeQuery($this->buildSQL($table,$fields1,$values1,'insert'));
echo "Last Insert ID:".$this->getLastInsertId();
$secondSELECT = "SELECT * from administrator where adminId='user1'";
echo "After Insert <pre>";
print_r($this->executeSelectArray($secondSELECT));
$this->executeQuery($this->buildSQL($table,$update_fields1,$update_values1,'update',$condition1));
$thirdSELECT = "SELECT * from administrator where adminId='user1'";
echo "After Update <pre>";
print_r($this->executeSelect($thirdSELECT));
//$this->rollBackTransaction();
$this->commitTransaction();
$this->executeQuery("UPDATE administrator set adminId='user10' where adminId='user1'");
//Handling exceptions
$dropTable = "DROP TABLE Administrator";
//$this->executeQuery($dropTable);
}
}
?>