Now Everyone knows that mysql queries are in the way of depreciation. They are no longer supported by the mysql project team. Main reason is because of the lack of improved security features in mysql query executions. So many of the mysql query execution are in the threat od phishing attacks. So here am providing a basic library of using PDO who wants to change their existing MYSQL projects directly to PDO by only changing one library file.
For this we have to create a config.php file in the root folder giving the connection details.It will look like this:-
<?php
$config["databaseHost"] = "hostname";
$config["databaseUser"] = "username";
$config["databasePass"] = "password";
$config["databaseName"]= "databasename";
?>
So After that Include Mysql.php file as I explained in my post check here
So the changed Mysql file for PDO will be:-
<?php
class MySql
{
/*
* Date:8-16-2014
* Login Form , entry to the application
* Auhthor : Litto chacko
* Email:littochackomp@gmail.com
*/
public $dbUser;
public $dbPass;
public $dbName;
public $dbHost;
private $dbConnection;
private $errorString;
private $filter;
private $util;
public static $instance;
public $query;
public $newCon;
function __construct(){
$dbConnection = null;
$this->filter = true;
$this->newCon = false;
}
function setNew(){
$this->newCon = true;
}
function noFilter()
{
$this->filter = false;
}
/*
* Setting Error Message on Db Operation
* Input String Message
* Called upon db operation
*/
function setError($string)
{
$this->errorString = $string;
//echo "MYSQL ERROR - ".$this->errorString;
}
/*
* get Error Message after a db operation
* Retrieves the current error Status
*/
function getError()
{
return $this->errorString;
}
function connect()
{
if(is_null($dbConnection)){
require_once(CONST_BASEDIR.'/config.php') ;
$this->dbUser = $config["databaseUser"];
$this->dbPass = $config["databasePass"];
$this->dbName = $config["databaseName"];
$this->dbHost = $config["databaseHost"];
try {
$dbConnection= new PDO('mysql:host='.$this->dbHost.';dbname='.$this->dbName.';charset=utf8', $this->dbUser, $this->dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
return $dbConnection;
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
}
}
function getInstance(){
return $dbConnection;
}
/*
* Close the Mysql Connection
*/
function addFilter($string){
return addslashes($string);
}
/*
* Remove added special chars on STring
*/
function removeFilter($string){
return stripslashes($string);
}
function escapeHtml($text){
return strip_tags($text);
}
function close()
{
if($dbConnection){
$dbConnection = null;
}else{
$dbConnection = null;
}
}
function fetchAll($query)
{
include(CONST_BASEDIR.'/config.php') ;
$dbUser = $config["databaseUser"];
$dbPass = $config["databasePass"];
$dbName = $config["databaseName"];
$dbHost = $config["databaseHost"];
try {
$db= new PDO('mysql:host='.$dbHost.';dbname='.$dbName.';charset=utf8', $dbUser, $dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$fileds = array();
$resultSet = array();
$stmt = $db->query($query);
$result= $stmt->fetchAll(PDO::FETCH_ASSOC);
return $result;
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
}
function insert($options,$table)
{
$queryString = "";
$p = count($options);
$start = 0;
$fieldString = null;
$valueString = array();
include(CONST_BASEDIR.'/config.php') ;
$dbUser = $config["databaseUser"];
$dbPass = $config["databasePass"];
$dbName = $config["databaseName"];
$dbHost = $config["databaseHost"];
try {
$db= new PDO('mysql:host='.$dbHost.';dbname='.$dbName.';charset=utf8', $dbUser, $dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
foreach($options as $key=>$val){
$fieldString.=" `{$key}`";
$valueString[":".$key.""]="{$val}";
$vs.=":".$key;
if($start<$p-1){
$fieldString.=",";
$vs.=",";
}
$start++;
}
//$dc=$valueString;
$queryString = "INSERT INTO `{$table}` ({$fieldString}) VALUES ({$vs}) ";
$stmt = $db->prepare($queryString);
$stmt->execute($valueString);
//$affected_rows = $stmt->rowCount();
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
}
function update($options,$table,$condition)
{
$queryString = "";
$fieldString = "";
$valueString = array();
$p = count($options);
$start = 0;
include(CONST_BASEDIR.'/config.php') ;
$dbUser = $config["databaseUser"];
$dbPass = $config["databasePass"];
$dbName = $config["databaseName"];
$dbHost = $config["databaseHost"];
try {
$db= new PDO('mysql:host='.$dbHost.';dbname='.$dbName.';charset=utf8', $dbUser, $dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
foreach($options as $key=>$val){
$vs=":".$key;
$fieldString.=" `{$key}`={$vs}";
$valueString[":".$key.""]="{$val}";
if($start<$p-1){
$fieldString.=",";
}
$start++;
}
$queryString = "UPDATE `{$table}` SET {$fieldString} ";
if(!empty($condition)){
echo $queryString.=" WHERE {$condition} ";
}
$stmt = $db->prepare($queryString);
$stmt->execute($valueString);
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
}
function delete($table,$condition)
{
include(CONST_BASEDIR.'/config.php') ;
$dbUser = $config["databaseUser"];
$dbPass = $config["databasePass"];
$dbName = $config["databaseName"];
$dbHost = $config["databaseHost"];
try {
$db= new PDO('mysql:host='.$dbHost.';dbname='.$dbName.';charset=utf8', $dbUser, $dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$queryString = "DELETE FROM `{$table}` ";
if(!empty($condition)){
$queryString.=" WHERE {$condition} ";
}
$result = $db->exec($queryString);
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
function execute($query){
include(CONST_BASEDIR.'/config.php') ;
$dbUser = $config["databaseUser"];
$dbPass = $config["databasePass"];
$dbName = $config["databaseName"];
$dbHost = $config["databaseHost"];
try {
$db= new PDO('mysql:host='.$dbHost.';dbname='.$dbName.';charset=utf8', $dbUser, $dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$result = $db->exec($query);
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
}
}
}
?>
Now I will explain how to use it. This Mysql.php will be your main library file.
So after this for your projects you want to write classes for each section like User section, company section.etc.
so you create classes like Company.php, User.php .etc..
So Inorder to implement functions from parent class you have to declare classes like
<?php
class Company extends MySql{
}
?>
@for deleting arecord you have to just call like this
for eg:-
$this->delete('cms_company','`company_id`='.$list[$i]);
@for updating a record you have to just call like this
for eg:
$this->update(array('status'=>'0'),"cms_company",'`company_id`='.$list[$i]);
@for fetching data ,you have to call like this.
for eg:
$query = "SELECT count(c.`company_id`) FROM `cms_company` c WHERE c.`company_id`!=''";
$query.=$qry;
$rec = $this->fetchAll($query);
@for inserting data you have to only do like this
for eg:
$insert = array('company_name'=>$txtTitle,'company_logo'=>$file,'company_banner'=>$file1,'company_address'=>$txtContent,'company_desc'=>$txtdesc,'company_web'=>$web);// array of values in table
$this->insert($insert,'cms_company');
For this we have to create a config.php file in the root folder giving the connection details.It will look like this:-
<?php
$config["databaseHost"] = "hostname";
$config["databaseUser"] = "username";
$config["databasePass"] = "password";
$config["databaseName"]= "databasename";
?>
So After that Include Mysql.php file as I explained in my post check here
So the changed Mysql file for PDO will be:-
<?php
class MySql
{
/*
* Date:8-16-2014
* Login Form , entry to the application
* Auhthor : Litto chacko
* Email:littochackomp@gmail.com
*/
public $dbUser;
public $dbPass;
public $dbName;
public $dbHost;
private $dbConnection;
private $errorString;
private $filter;
private $util;
public static $instance;
public $query;
public $newCon;
function __construct(){
$dbConnection = null;
$this->filter = true;
$this->newCon = false;
}
function setNew(){
$this->newCon = true;
}
function noFilter()
{
$this->filter = false;
}
/*
* Setting Error Message on Db Operation
* Input String Message
* Called upon db operation
*/
function setError($string)
{
$this->errorString = $string;
//echo "MYSQL ERROR - ".$this->errorString;
}
/*
* get Error Message after a db operation
* Retrieves the current error Status
*/
function getError()
{
return $this->errorString;
}
function connect()
{
if(is_null($dbConnection)){
require_once(CONST_BASEDIR.'/config.php') ;
$this->dbUser = $config["databaseUser"];
$this->dbPass = $config["databasePass"];
$this->dbName = $config["databaseName"];
$this->dbHost = $config["databaseHost"];
try {
$dbConnection= new PDO('mysql:host='.$this->dbHost.';dbname='.$this->dbName.';charset=utf8', $this->dbUser, $this->dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
return $dbConnection;
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
}
}
function getInstance(){
return $dbConnection;
}
/*
* Close the Mysql Connection
*/
function addFilter($string){
return addslashes($string);
}
/*
* Remove added special chars on STring
*/
function removeFilter($string){
return stripslashes($string);
}
function escapeHtml($text){
return strip_tags($text);
}
function close()
{
if($dbConnection){
$dbConnection = null;
}else{
$dbConnection = null;
}
}
function fetchAll($query)
{
include(CONST_BASEDIR.'/config.php') ;
$dbUser = $config["databaseUser"];
$dbPass = $config["databasePass"];
$dbName = $config["databaseName"];
$dbHost = $config["databaseHost"];
try {
$db= new PDO('mysql:host='.$dbHost.';dbname='.$dbName.';charset=utf8', $dbUser, $dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$fileds = array();
$resultSet = array();
$stmt = $db->query($query);
$result= $stmt->fetchAll(PDO::FETCH_ASSOC);
return $result;
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
}
function insert($options,$table)
{
$queryString = "";
$p = count($options);
$start = 0;
$fieldString = null;
$valueString = array();
include(CONST_BASEDIR.'/config.php') ;
$dbUser = $config["databaseUser"];
$dbPass = $config["databasePass"];
$dbName = $config["databaseName"];
$dbHost = $config["databaseHost"];
try {
$db= new PDO('mysql:host='.$dbHost.';dbname='.$dbName.';charset=utf8', $dbUser, $dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
foreach($options as $key=>$val){
$fieldString.=" `{$key}`";
$valueString[":".$key.""]="{$val}";
$vs.=":".$key;
if($start<$p-1){
$fieldString.=",";
$vs.=",";
}
$start++;
}
//$dc=$valueString;
$queryString = "INSERT INTO `{$table}` ({$fieldString}) VALUES ({$vs}) ";
$stmt = $db->prepare($queryString);
$stmt->execute($valueString);
//$affected_rows = $stmt->rowCount();
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
}
function update($options,$table,$condition)
{
$queryString = "";
$fieldString = "";
$valueString = array();
$p = count($options);
$start = 0;
include(CONST_BASEDIR.'/config.php') ;
$dbUser = $config["databaseUser"];
$dbPass = $config["databasePass"];
$dbName = $config["databaseName"];
$dbHost = $config["databaseHost"];
try {
$db= new PDO('mysql:host='.$dbHost.';dbname='.$dbName.';charset=utf8', $dbUser, $dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
foreach($options as $key=>$val){
$vs=":".$key;
$fieldString.=" `{$key}`={$vs}";
$valueString[":".$key.""]="{$val}";
if($start<$p-1){
$fieldString.=",";
}
$start++;
}
$queryString = "UPDATE `{$table}` SET {$fieldString} ";
if(!empty($condition)){
echo $queryString.=" WHERE {$condition} ";
}
$stmt = $db->prepare($queryString);
$stmt->execute($valueString);
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
}
function delete($table,$condition)
{
include(CONST_BASEDIR.'/config.php') ;
$dbUser = $config["databaseUser"];
$dbPass = $config["databasePass"];
$dbName = $config["databaseName"];
$dbHost = $config["databaseHost"];
try {
$db= new PDO('mysql:host='.$dbHost.';dbname='.$dbName.';charset=utf8', $dbUser, $dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$queryString = "DELETE FROM `{$table}` ";
if(!empty($condition)){
$queryString.=" WHERE {$condition} ";
}
$result = $db->exec($queryString);
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
function execute($query){
include(CONST_BASEDIR.'/config.php') ;
$dbUser = $config["databaseUser"];
$dbPass = $config["databasePass"];
$dbName = $config["databaseName"];
$dbHost = $config["databaseHost"];
try {
$db= new PDO('mysql:host='.$dbHost.';dbname='.$dbName.';charset=utf8', $dbUser, $dbPass,array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$result = $db->exec($query);
} catch(PDOException $ex) {
print "Error!: " . $ex->getMessage() . "<br/>";
//some_logging_function($ex->getMessage());
}
}
}
}
?>
Now I will explain how to use it. This Mysql.php will be your main library file.
So after this for your projects you want to write classes for each section like User section, company section.etc.
so you create classes like Company.php, User.php .etc..
So Inorder to implement functions from parent class you have to declare classes like
<?php
class Company extends MySql{
}
?>
@for deleting arecord you have to just call like this
for eg:-
$this->delete('cms_company','`company_id`='.$list[$i]);
@for updating a record you have to just call like this
for eg:
$this->update(array('status'=>'0'),"cms_company",'`company_id`='.$list[$i]);
@for fetching data ,you have to call like this.
for eg:
$query = "SELECT count(c.`company_id`) FROM `cms_company` c WHERE c.`company_id`!=''";
$query.=$qry;
$rec = $this->fetchAll($query);
@for inserting data you have to only do like this
for eg:
$insert = array('company_name'=>$txtTitle,'company_logo'=>$file,'company_banner'=>$file1,'company_address'=>$txtContent,'company_desc'=>$txtdesc,'company_web'=>$web);// array of values in table
$this->insert($insert,'cms_company');
No comments:
Post a Comment