Ads Inside Post

Search in This Blog

Mysql Database access using PDO

How to access Mysql Database using PDO:

PHP is changing day by day. in old days we were use mysql_connect(...) but now in current days it is not supported. current days we can use mysqli_connect(...). but it is better if we use PDO (PHP Data Object).

PHP Data Objects (PDO) is a very lightweight, consistent interface for accessing different types of databases in PHP. for this you simply tell the PDO database driver.

by using PDO you use the same functions to issue queries and fetch data in different types of database. PDO also remove all possibility of sql injections. for this it provide bind parameter facility.

Example:

Listed below full code that insert update delete list of all records. this code is in OOP based.


File: Db.php

<?php
class Db {
    private $pdo;
    private $stmt;

    function __construct() {
// set database setting here
$host='localhost'; $db='student_db'; $u='root'; $p='';
        try {
$this->pdo = new PDO("mysql:host={$host};dbname={$db}", $u,$p);
          $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            $this->pdo = null;
echo $e->getMessage();
            return 'oops: ' . $e->getMessage();
        }
    }

    function __destruct() {
// release all resource
        $this->stmt = null;
        $this->pdo = null;
    }

// initialize prepare statement
    public function prepare_statement($sql) {
        $this->stmt = $this->pdo->prepare($sql);
    }

// bind parameter
    public function set_val($field_nm, $field_val) {
        $this->stmt->bindParam($field_nm, $field_val);
    }

// initialize prepare statement for update data using array
public function update_by_array($tbl_name, $where, $array_data) {
$sets="Update {$tbl_name} set ";
foreach ($array_data as $field => $value) {
$sets .= "{$field} = :{$field}, ";
}
$sets= substr($sets,0,-2).$where;
$this->prepare_statement($sets);
foreach ($array_data as $field => $value) {
$this->set_val(":{$field}", $value);
}
        return $this->exe_update_insert();
    }

//////// execute statement and return records from mysql
    public function get_record_exe_stat() {
        try {
            $this->stmt->execute();
            return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch(PDOException $e) {
echo $e->getMessage();
            $this->pdo = null;
            return 'oops: ' . $e->getMessage();
        }
    }

/// execute statement and return updated/inserted no of rows/row
    public function exe_update_insert() {
        try {
           
$this->stmt->execute();
//$this->stmt->debugDumpParams();
            return $this->stmt->rowCount();
        } catch(PDOException $e) {
//$this->stmt->debugDumpParams();
//echo $e->getMessage();
            $this->pdo = null;
            return 'oops: ' . $e->getMessage();
        }
    }

/// Return number of rows that are after delete/update/insert
    public function get_row_count() {
        try {
            $this->stmt->rowCount();
        } catch(PDOException $e) {
echo $e->getMessage();
            $this->pdo = null;
            return 0;
        }
    }
/// Return last auto increment id after insert record
    public function get_last_id() {
        try {
            return $this->pdo->lastInsertId();
        } catch(PDOException $e) {
$this->pdo = null;
            return 0;
        }
    }
/// Return records of sql query
    public function get_record_by_sql($sql)
    {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch(PDOException $e) {
echo $e->getMessage();
            $this->pdo = null;
            return 'oops: ' . $e->getMessage();
        }
    }
/// Insert record and return number of rows effected
    public function insert_by_sql($sql)
    {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            return $stmt->rowCount();
        } catch(PDOException $e) {
echo $e->getMessage();
            $this->pdo = null;
            return 'oops: ' . $e->getMessage();
        }
    }
}



test.php

<?php
require_once('Db.php');
$db= new Db();
$i=0;

///// simple insert //////////
$number_of_rows_updated=$db->insert_by_sql("insert into std_info(std_full_nm,address,phone,class_cat) values ('xyz name','address123','1231231234','class 3')");

/////////////////////////insert by bind parameter prepare statement /////////////////////////////////////////////////////////
for($i=1;$i<=10;$i++) {
$std_full_nm = "AA".$i;
$address = "12/76 my address".$i;
$phone='12345678'.$i;
$class_cat='class'.$i;
$db->prepare_statement("insert into std_info(std_full_nm,address,phone,class_cat) values (:std_full_nm,:address,:phone,:class_cat)");
$db->set_val(":std_full_nm", $std_full_nm);
$db->set_val(":address", $address);
$db->set_val(":phone", $phone);
$db->set_val(":class_cat", $class_cat);
$up = $db->exe_update_insert();
if($up>=1){
$id=$db->get_last_id();
echo "Last id is =". $id."<br/>";
}
}

//////////////// return multiple row by prepare statement ////////////////////////////////////////////////////////////////
$id = "1";
$db->PrepareStatement("SELECT * FROM std_info WHERE std_id>= :id");
$db->set_val(":id", $id);
$userData = $db->get_record_exe_stat();
print_r($userData);
if (count($userData) < 1)
return;
for($i=0;$i<count($userData);$i++) {
echo $userData[$i]['std_id']."<br/>";
echo $userData[$i]['std_full_nm']."<br/>";
echo $userData[$i]['address']."<br/>";
echo $userData[$i]['phone']."<br/>";
echo $userData[$i]['class_cat']."<br/>";
echo "<hr/>";
}

///////////////////// return single row //////////////
$id = "1";
$db->PrepareStatement("SELECT * FROM std_info WHERE std_id= :id");
$db->set_val(":id", $id);
$userData = $db->get_record_exe_stat();
if (count($userData)>= 1) {
echo $userData[0]['std_id']."<br/>";
echo $userData[0]['std_full_nm']."<br/>";
echo $userData[0]['address']."<br/>";
echo $userData[0]['phone']."<br/>";
echo $userData[0]['class_cat']."<br/>";
echo "<hr/>";
}

///////////// update by using bind parameter ////////////////
$std_id = "1"; // Some ID to update
$std_full_nm = "zz";
$address = "55/76 my address";
$phone='0987654321';
$class_cat='10 class';

$db->prepare_statement("UPDATE std_info SET std_full_nm=:std_full_nm,address=:address,phone=:phone,class_cat=:class_cat WHERE std_id=:std_id");
$db->set_val(":std_id", $std_id);
$db->set_val(":std_full_nm", $std_full_nm);
$db->set_val(":address", $address);
$db->set_val(":phone", $phone);
$db->set_val(":class_cat", $class_cat);
$updated_row = $db->exe_update_insert();

///////////// update by using array ////////////////
$tbl_name='std_info';
$where=' WHERE std_id=1';
$sets = "";
$array_data = array(
'std_full_nm' => 'somename1',
'address' => 'someaddress1',
'phone' => 'Somephone1',
'class_cat' => 'Somecat1'
);
$db->update_by_array($tbl_name,$where, $array_data);

/////////////////// return records by simple sql //////////////
$id = "1";
$userData = $db->get_record_by_sql("SELECT * FROM std_info");
if (count($userData) < 1)
return;
for($i=0;$i<count($userData);$i++) {
echo $userData[$i]['std_id']."<br/>";
echo $userData[$i]['std_full_nm']."<br/>";
echo $userData[$i]['address']."<br/>";
echo $userData[$i]['phone']."<br/>";
echo $userData[$i]['class_cat']."<br/>";
echo "<hr/>";
}

PDOException' with message 'SQLSTATE[42000 Syntax error or access violation: 1064

Problem:


How to fix following PHP PDO error
exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line


Solution:


It means you are missing small brackets ')' in your sql statement.

Video Watermark Remover

Problem:

Some time you have many videos that has watermark. you just hate that watermark and need to delete it. There are many watermark remover software but all are not free. so if you want a free video watermark remover then you are at present here right place.

Solution:


I have developed Video watermark remover software. it is 64 Bit Windows 7 desktop application, so you needed to .net framework 4.5 to run this application.




You can download it from here:

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part01.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part02.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part03.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part04.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part05.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part06.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part07.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part08.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part09.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part10.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part11.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part12.rar

http://sourceforge.net/projects/unique-softwares/files/VideoWaterMarkRemover.part13.rar



Microsoft Office Interop Outlook

Problem:

Where to find library of Microsoft.Office.Interop.Outlook?

Solution:

To locate library of Microsoft.Office.Interop.Outlook you should look into below 2 folders:
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office14
C:\Program Files (x86)\Microsoft Visual Studio 12.0\Visual Studio Tools for Office\PIA\Office15


Please note that folder could be "C:\Program Files\"

mongoDB on windows 7

Problem:


While installing mongoDB on windows 7 you will get the same message as below:

I CONTROL  Hotfix KB2731284 or later update is not installed, will zero-out data files
I STORAGE  [initandlisten] exception in initAndListen: Data directory C:\data\db\ not found., terminating
I CONTROL  [initandlisten] dbexit:  rc: 100Solution:


Solution:

you needed o create "data\db" folder under "C:\mongodb\bin" and run mongod.exe file then after it will automatically create "C:\data\db\"

how to check if visitor is a robot or bot

Problem:

how to check if visitor is a robot or bot?

Solution:

Please use below code

Example:

$user_agent='';
if( !isset( $_SERVER['HTTP_USER_AGENT'])){
echo 'i m a Robot!';
die();
} else {
$user_agent=strtolower($_SERVER['HTTP_USER_AGENT']);
}

$robot='N';
if(strpos($user_agent,'bot')>0 || strpos($user_agent,'Bot')>0) {
    $robot='Y';
}

if($robot!='Y')
echo 'I am not a Robot';
else
echo 'I am a Robot';




Currency convertor

Problem:  

Some time you need to convert currency into different countries, so what you will do?

Solution: 

You can use yahooapi

Example: 

Use below code. the code is in PHP ou can easily convert it into VB.Net, Java, C# etc if you are good programmer.


function curr_convert($ffrom,$tto,$iinput) {
$yql_url = "http://query.yahooapis.com/v1/public/yql";
    $yql_query = 'select * from yahoo.finance.xchange where pair in ("'.$ffrom.$tto.'")';
    $yql_query_full_url = $yql_url . "?q=" . urlencode($yql_query);
    $yql_query_full_url .= "&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys";
    $yql_curl = curl_init($yql_query_full_url);
    curl_setopt($yql_curl, CURLOPT_RETURNTRANSFER,true);
    $yqlcurl_exec = curl_exec($yql_curl);
    $yql_json =  json_decode($yqlcurl_exec,true);
    $curr_output = (float) $iinput*$yql_json['query']['results']['rate']['Rate'];
    return $curr_output;
}
$result = curr_convert($ffrom = "USD", $tto = "INR", $iinput = 1);
echo '1 USD  = '.$result.' INR';