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

Integrating GoogleMap with Multiple Location Markers in Your Website

In your website,Their may arise situation to show multiple locations in your Contact  Google map. May be  you want to show all the branches of the corresponding companies in it.

So In this post I will explain How to do that.

Include this script in your header:-

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js" type="text/javascript"></script> <script src="http://maps.google.com/maps/api/js?sensor=false" type="text/javascript"></script> <script type="text/javascript">     $(document).ready(function() { initialize(); });
    function initialize() {        var map_options = {            center: new google.maps.LatLng(24.4667,54.3667),            zoom: 13,            mapTypeId: google.maps.MapTypeId.ROADMAP        };
        var google_map = new google.maps.Map(document.getElementById("map_canvas"), map_options);
        var info_window = new google.maps.InfoWindow({            content: 'loading'        });
        var t = [];        var x = [];        var y = [];        var h = [];

//add latitude,longitude of locations here 

        t.push('Location Name 1');        x.push(24.4667);        y.push(54.3667);        h.push('<p><strong>Location Name 1</strong><br/>Address 1</p>');
        t.push('Location Name 2');        x.push(24.4690);        y.push(54.3690);        h.push('<p><strong>Location Name 2</strong><br/>Address 2</p>');
 t.push('Location Name 3');        x.push(24.4700);        y.push(54.3700);        h.push('<p><strong>Location Name 2</strong><br/>Address 2</p>');
 t.push('Location Name 4');        x.push(24.4800);        y.push(54.3800);        h.push('<p><strong>Location Name 2</strong><br/>Address 2</p>');

        var i = 0;        for ( item in t ) {            var m = new google.maps.Marker({                map:       google_map,                animation: google.maps.Animation.DROP,                title:     t[i],                position:  new google.maps.LatLng(x[i],y[i]),                html:      h[i]            });
            google.maps.event.addListener(m, 'click', function() {                info_window.setContent(this.html);                info_window.open(google_map, this);            });            i++;        }    }</script> 

After that  include

<div id="map_canvas" style="width:auto;height:400px;">Google Map</div> 

this above line in the place where you want to show your map.

ENJOY !!!! Have a Great day!!!!

Creating Zip file of a folder in PHP

During large php projects , we have to create zip files dynamically. Iam explaining  in this post how to create zip file of a folder dynamicaly using PHP. In PHP we didn't want to  add external libraries for creating zip files. We have ZIPARCHIVE library available in php library itself.

So I will explain step by step in how to acheive this...

1) Create a folder named zip or any name you like in the root directory. This will be the directory which will store the zip files.

2) Create a folder named samplesite & place all the files inside this folder. This folder will be converted to zip file.

3) Create a php page and paste the below code

<?php

$filename="Filename.zip"; // name of the zip file to create

// Adding files to a .zip file, no zip file exists it creates a new ZIP file

// increase script timeout value

ini_set('max_execution_time', 5000);

// create object

$zip = new ZipArchive();

// open archive 

if ($zip->open('zip/'.$filename, ZIPARCHIVE::CREATE) !== TRUE) {

    die ("Could not open archive");

}

// initialize an iterator

// pass it the directory to be processed

$iterator = new RecursiveIteratorIterator(new RecursiveDirectoryIterator("samplesite/"));

//give the folder name to be zipped

// iterate over the directory

// add each file found to the archive

foreach ($iterator as $key=>$value) {

    $zip->addFile(realpath($key), $key) or die ("ERROR: Could not add file: $key");

}

// close and save archive

$zip->close();

echo "Archive created successfully.";

?>

After Executing this script, check the folder zip, it contains the zip file filename.zip....

Hope this post helps... For any help.. pls let me know...

Solving the Forbidden access Error of wamp server in windows8

When you installed your wamserver in your windows8 Pc & tried accessing localhost you may gone through an error like this.

Inorder to resolve this problem, please follow the following steps.

1) Click on the wampserver tray icon. From it click on Apache-> htppd.conf

Open the file in edit mode then.

Find these lines

<Directory />
    Options FollowSymLinks
    AllowOverride None
    Order deny,allow
    Deny from all
</Directory>

Change it to

<Directory />
     Options Indexes FollowSymLinks MultiViews
    AllowOverride all
    Order Deny,Allow
    Allow from all
</Directory>

2)Find another line in the same file

#   onlineoffline tag - don't remove

    Order Deny,Allow

    Deny from all

    Allow from 127.0.0.1

change it as

#   onlineoffline tag - don't remove

    Order Deny,Allow

    Allow from all

    Allow from 127.0.0.1

3)Now your server will be working. But when you try to access the phpmyadmin,it will show error.

So inorder to rectify it, we have to make changes in phpmyadmin config file. So for this :-
explore to \wamp\alias\phpmyadmin.conf
Open this file and find this code

<Directory "c:/wamp/apps/phpmyadmin3.4.10.1/">
    Options Indexes FollowSymLinks MultiViews
    AllowOverride all
        Order Deny,Allow
    Deny from all
    Allow from 127.0.0.1

</Directory>

change it to

<Directory "D:/Server/wamp/apps/phpmyadmin3.4.10.1/">    Options Indexes FollowSymLinks MultiViews    AllowOverride all        Order Deny,Allow Allow from all</Directory>

Now you can see that everything is working fine..