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

?>

  $this->qError = $this->dbh->errorInfo();
  if(!is_null($this->qError[2])){
      echo $this->qError[2];
  }

change

Please consider adding methods that catch errors

This "class" makes no sense. It adds no functionality compared to vanilla PDO.
At the same time it limits the existing functionality to a crippled subset