PHP classes for PDO

PDO classes.md

PDO Classes for db connect and manipulation

Below is a description on how to use the classes in the below script

###Include script and set up db variables

include 'zdb.php';

//setup db variables
define("DB_HOST", "127.8.221.129");
define("DB_USER", "danferth");
define("DB_PASS", "");
define("DB_NAME", "c9");

###create new db object

$database = new db();

---

###single insert

$database->query('INSERT INTO test (name, age, description) VALUES(:name, :age, :description)');

#####bind query variables

$database->bind(':name', 'Melissa');
$database->bind(':age', '39');
$database->bind(':description', 'the real boss');

#####execute query

$database->execute();

---

###multiple INSERTS using transactions

#####begin transaction

$database->beginTransaction();

#####the query

$database->query('INSERT INTO test (name, age, description) VALUES (:name, :age, :description)');

#####Insert 1

$database->bind(':name','Sam');
$database->bind(':age','34');
$database->bind(':description','The Boss');

#####execute insert 1

$database->execute();

#####insert 2

$database->bind(':name','Karen');
$database->bind(':age','48');
$database->bind(':description','Administration');

#####execute insert 2

$database->execute();

#####end transaction

$database->endTransaction();

---

###Select Single Row

#####set up query

$database->query("SELECT name, age, description FROM test WHERE name = :name");

#####bind data

$database->bind(':name','dan');

#####Run single method

$row = $database->single();

#####Print result

echo '<pre>';
print_r($row);
echo '</pre>';

###Select Multiple rows

#####set up query

$database->query('SELECT * FROM test');
$row = $database->resultset();

#####Print result

echo '<pre>';
print_r($row);
echo '</pre>';

#####echo number of records returned

echo $database->rowCount();

zdb.php

<?php

class db{
  private $host = DB_HOST;
  private $dbName = DB_NAME;
  private $user = DB_USER;
  private $pass = DB_PASS;
  
  private $dbh;
  private $error;
  private $qError;
  
  private $stmt;
  
  public function __construct(){
      //dsn for mysql
    $dsn = "mysql:host=".$this->host.";dbname=".$this->dbName;
    $options = array(
        PDO::ATTR_PERSISTENT    => true,
        PDO::ATTR_ERRMODE       => PDO::ERRMODE_EXCEPTION
        );
    
    try{
        $this->dbh = new PDO($dsn, $this->user, $this->pass, $options);
    }
    //catch any errors
    catch (PDOException $e){
        $this->error = $e->getMessage();
    }
    
  }
  
  public function query($query){
      $this->stmt = $this->dbh->prepare($query);
  }
  
  public function bind($param, $value, $type = null){
      if(is_null($type)){
          switch (true){
              case is_int($value):
                $type = PDO::PARAM_INT;
                break;
              case is_bool($value):
                  $type = PDO::PARAM_BOOL;
                  break;
              case is_null($value):
                  $type = PDO::PARAM_NULL;
                  break;
              default:
                  $type = PDO::PARAM_STR;
          }
      }
    $this->stmt->bindValue($param, $value, $type);
  }
  
  public function execute(){
      return $this->stmt->execute();
      
      $this->qError = $this->dbh->errorInfo();
        if(!is_null($this->qError[2])){
	        echo $this->qError[2];
        }
        echo 'done with query';
  }
  
  public function resultset(){
      $this->execute();
      return $this->stmt->fetchAll(PDO::FETCH_ASSOC);
  }
  
  public function single(){
      $this->execute();
      return $this->stmt->fetch(PDO::FETCH_ASSOC);
  }
  
  public function rowCount(){
      return $this->stmt->rowCount();
  }
  
  public function lastInsertId(){
      return $this->dbh->lastInsertId();
  }
  
  public function beginTransaction(){
      return $this->dbh->beginTransaction();
  }
  
  public function endTransaction(){
      return $this->dbh->commit();
  }
  
  public function cancelTransaction(){
      return $this->dbh->rollBack();
  }
  
  public function debugDumpParams(){
      return $this->stmt->debugDumpParams();
  }
  
  public function queryError(){
      $this->qError = $this->dbh->errorInfo();
      if(!is_null($qError[2])){
          echo $qError[2];
      }
  }
  
}//end class db

?>