Sunday, 13 November 2016

Create PHP PDO wrapper class

Create PHP PDO wrapper class

Step 1: Create Config file config.php
<?php

ini_set("display_errors", 1);  
define('DB_HOST', "localhost");
define('DB_NAME', "pdoTest");
define('DB_USER', "root");
define('DB_PASS', "root");
?>


Step 2: Now create wrapper class Database.php
<?php class Database {

    private $conn;
private $stmt;

    function __construct(){ 
include_once 'config.php';
try{
$this->conn = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME."", DB_USER, DB_PASS); 
$this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $this->conn;
}catch(PDOException $e){
            return $e->getMessage();
        }
}
public function query($query){
$query = trim($query);
try {
$this->stmt = $this->conn->prepare($query);
return $this;
}catch (PDOException $e) {
return $e->getMessage();
}
}
public function execute(){
return $this->stmt->execute();
}
public function deleteQuery($query)
{
$this->query($query);
$this->execute();
return $this->affected_rows();
}
public function resultset() {
        $this->execute();
        return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
    }
public function rowCount()
{
$this->execute();
        return $this->stmt->rowCount();
}
public function insertTable($table,$values,$return_id=TRUE){
if(!is_array($values)){
return FALSE;
}
$table = preg_replace('/[^\da-z_]/i', '', $table);
$fld = $vals = array();

foreach($values as $k=>$v){
$fld[]= "`$k`=?";
$vals[]= trim($v);
}
$set = implode(',', $fld);
$qry = "INSERT INTO `$table` SET $set";
try {
$this->query($qry);
$this->stmt->execute($vals);
return $this->dbh->lastInsertId();
}catch (PDOException $e) {
return $e->getMessage();
}
}
public function single() {
        $this->execute();
        return $this->stmt->fetch();
    }
public function affected_rows(){
        return $this->stmt ? $this->stmt->rowcount() : false;
    }
public function update($table, array $fields, $cond){
$table = preg_replace('/[^\da-z_]/i', '', $table);
$query = "UPDATE `$table` SET";
$values = array();
foreach ($fields as $name => $value) {
$query .= ' '.$name.' = :'.$name.','; // the :$name part is the placeholder, e.g. :zip
$values[':'.$name] = $value; // save the placeholder
}
$condition = (isset($cond))?" Where $cond":'';
$query = substr($query, 0, -1).$condition.';'; // remove last , and add a ;
try {
$this->query($query);
$this->stmt->execute($values);
return $this->affected_rows();
}catch (PDOException $e) {
return $e->getMessage();
}
}
public function closeConnection(){
try {
            $this->conn = null; //Closes connection
        } catch(PDOException $e) {
return $e->getMessage();  
        }
}

} ?>

Step 3: Now create index.php file and access result.
<?php 
include_once 'Database.php';
$db_conntect = new Database();

// Select Query
$result = $db_conntect->query("Select * from menus")->resultset();
echo "<pre>"; print_r($result);

//Insert Query
$userArray = array('username'=>'sudhir','email'=>'sudhir@xyz.com','password'=>'123456');
$db->insertTable('admin',$userArray);

//Update Query
$updateArray = array('email'=>'sudhir@xyz.com');
$condition = "username='admin'";
$db->update('admin',$updateArray,$condition);


// Create query, bind values and return a single row.
$row = $db->query('SELECT col1, col2, col3 FROM mytable WHERE id > ? LIMIT ?')
   ->bind(1, 2)
   ->bind(2, 1)
   ->single();

// Update the LIMIT and get a resultset.
$db->bind(2,2);
$rs = $db->resultset();
   

?>

0 comments:

Post a Comment