Simple Library Functions to use MYSQLi in your PHP Project

For every projects developed in PHP the database used by most of the users are MYSQL. But developers have a bad news because Mysql queries are being depreciated by the latest versions of PHP. So We have to migrate our existing PHP Mysql projects to Mysqli or PDO.
I have explained with library functions how to migrate MYSQL to PDO in the previous post. In this post I will give you a library which can be used for Mysqli projects. You Just need to replace this library file in your lib folder if you are using the conventional library file that I have explained in one of my previous posts.


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 here you go. I will give you MYSQL lib file , just copy & paste in the lib file Mysql & everything will works fine....

<?php

class MySql
{

private $dbUser;
private $dbPass;
private $dbName;
private $dbHost;
private $dbConnection;
private $errorString;
private $filter;
private $util;
public static $instance;
public $query;
public $newCon;


function __construct(){

$this->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;
}

/*
* Connect to Mysql Database using set up data
* Set up data being hold on Constructor
* Modify the constrct params for connection change
*/

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"];

$dbConnection = new mysqli($this->dbHost,$this->dbUser,$this->dbPass,$this->dbName);
if($this->dbConnection->connect_error) {
echo 'Connection Error'.$conn->connect_error;
}
}
}

function getInstance(){
return $this->dbConnection;
}


function close()
{
if($this->dbConnection){
$this->dbConnection->close();

$this->dbConnection = null;
}else{
$this->dbConnection = null;
}
}


function fetchAll($query)
{
           include(CONST_BASEDIR.'/config.php') ;
$this->dbUser = $config["databaseUser"];
$this->dbPass = $config["databasePass"];
$this->dbName = $config["databaseName"];
$this->dbHost = $config["databaseHost"];

$dbConnection = new mysqli($this->dbHost,$this->dbUser,$this->dbPass,$this->dbName);
$fileds = array();
$resultSet = array();
$result=$dbConnection->query($query);
$arr = $result->fetch_all(MYSQLI_ASSOC);
return $arr;
}


function insert($options,$table)
{
include(CONST_BASEDIR.'/config.php') ;
$this->dbUser = $config["databaseUser"];
$this->dbPass = $config["databasePass"];
$this->dbName = $config["databaseName"];
$this->dbHost = $config["databaseHost"];

         $dbConnection = new mysqli($this->dbHost,$this->dbUser,$this->dbPass,$this->dbName);
$queryString = "";
$p = count($options);
$start = 0;
$fieldString = null;
$valueString = null;
foreach($options as $key=>$val){
$fieldString.=" `{$key}`";
$vk=$dbConnection->real_escape_string($val);
$valueString.=" '{$vk}' ";
if($start<$p-1){
$fieldString.=",";
$valueString.=",";
}
$start++;
}
$queryString = "INSERT INTO `{$table}` ({$fieldString}) VALUES ({$valueString}) ";

//echo "db".$queryString;

//$result = mysql_query($queryString) or $this->setError("Insert".mysql_error());

   $result = $dbConnection->query($queryString);
   //$last_inserted_id = $dbConnection->insert_id;

}

function update($options,$table,$condition)
{
include(CONST_BASEDIR.'/config.php') ;
$this->dbUser = $config["databaseUser"];
$this->dbPass = $config["databasePass"];
$this->dbName = $config["databaseName"];
$this->dbHost = $config["databaseHost"];

         $dbConnection = new mysqli($this->dbHost,$this->dbUser,$this->dbPass,$this->dbName);

$queryString = "";
$fieldString = "";
$p = count($options);
$start = 0;
foreach($options as $key=>$val){
$vk=$dbConnection->real_escape_string($val);
            $fieldString.=" `{$key}`='{$vk}'";
if($start<$p-1){
$fieldString.=",";
}
$start++;
}
$queryString = "UPDATE `{$table}` SET {$fieldString} ";
if(!empty($condition)){
$queryString.=" WHERE {$condition} ";
}
$this->query = $queryString;
$result = $dbConnection->query($queryString);


}


function delete($table,$condition)
{
include(CONST_BASEDIR.'/config.php') ;
$this->dbUser = $config["databaseUser"];
$this->dbPass = $config["databasePass"];
$this->dbName = $config["databaseName"];
$this->dbHost = $config["databaseHost"];

         $dbConnection = new mysqli($this->dbHost,$this->dbUser,$this->dbPass,$this->dbName);
$queryString = "DELETE FROM `{$table}` ";
if(!empty($condition)){
$queryString.=" WHERE {$condition} ";
}
$result = $dbConnection->query($queryString);
      return true;

}


function execute($query){
include(CONST_BASEDIR.'/config.php') ;
$this->dbUser = $config["databaseUser"];
$this->dbPass = $config["databasePass"];
$this->dbName = $config["databaseName"];
$this->dbHost = $config["databaseHost"];
   $dbConnection = new mysqli($this->dbHost,$this->dbUser,$this->dbPass,$this->dbName);
                $result = $dbConnection->query($queryString);


}

function addFilter($string){
return addslashes($string);

}

function removeFilter($string){
return stripslashes($string);
}


function escapeHtml($text){
return strip_tags($text);
}





}
?>

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{

}

?>

Now i will  explain how it is used:-
@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');

Simple Library Functions to use PDO in Your Project

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');