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/>";
}