Simple yet efficient PDO wrapper

  1. The problem #1, verbosity.
  2. The problem #2, availability
  3. The true OOP way
  4. The static solution for the Object Oriented Code
  5. The blunt singleton, for your procedural code
  6. Examples
  7. Comments (19)

Although there are not too much reasons to create a wrapper (as PDO already being one), there are still some issues that might bug a programmer.

The problem #1, verbosity.

The main problem with both PDO and mysqli prepared statements is that the mechanism were designed for the multiple execution, when prepare and bind being called only once, and then execute is called multiple times with different data sets. That's why the code is so much verbose. However, in reality, multiple execution is used too seldom, while most of time prepared statements are used to execute the query only once. As a result, for the every single query we are bound to write the same repetitive code again and again:

$stmt $pdo->prepare("SELECT * FROM users WHERE sex=?");
$stmt->execute([$sex]);
$data $stmt->fetchAll();

It would have been better, if execute() returned the statement, allowing the neat method chaining:

$data $pdo->prepare($sql)->execute($params)->fetch();

But alas - execute() is returning a boolean.

Either way, even such an approach would be superfluous most of time, as we don't need separate prepare and execute calls.

To solve this inconvenience, it would be a good idea to add a new method to PDO, similar to Postgres' pg_query_params() to run both prepare and execute in one call.

I prefer less typing for the frequently called functions, I'd create a function called run(), which will accept a query and an array of values, pass them to prepare/execute and return the statement. And from this statement we'll be able to get the data in any format we want. For this we need only few lines of code:

class MyPDO extends PDO
{
    public function 
__construct($dsn$username NULL$password NULL$options = [])
    {
        
$default_options = [
            
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            
PDO::ATTR_EMULATE_PREPARES => false,
            
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        ];
        
$options array_replace($default_options$options);
        
parent::__construct($dsn$username$password$options);
    }
    public function 
run($sql$args NULL)
    {
        if (!
$args)
        {
             return 
$this->query($sql);
        }
        
$stmt $this->prepare($sql);
        
$stmt->execute($args);
        return 
$stmt;
    }
}

and now we've got a new class MyPDO which works as the old PDO in either way save for the addition of one new method that let us run a prepared query in a single call:

$data $pdo->run("SELECT * FROM users WHERE sex=?",[$sex])->fetchAll();

Also we just included most useful configuration options by default, though they can be overwritten.

The problem #2, availability

Another problem is the availability of PDO instance. Just like any other variable, it is bound to its scope and by default unavailable in any function or class' method. There are may approaches to solve this problem. Let's consider some of them

The true OOP way

For simplicity, Dependency injection stands for just passing all the required services through class constructor.

So if your code is object-oriented, then you may stick to the above MyPDO class solution, passing its instance around usint other classes' constructors:

class User
{
    
/* @var MyPDO */
    
protected $db;

    protected 
$data;

    public function 
__construct(MyPDO $db)
    {
        
$this->db $db;
    }

    public function 
find($id)
    {        
        
$this->data $this->db->run("SELECT * FROM users WHERE id = ?", [$id])->fetch();

    }
}

And then use it this way:

$mypdo = new MyPDO('mysql:host=localhost;dbname=test;charset=utf8');
$user = new User($mypdo);
$user->find($id);

The static solution for the Object Oriented Code

But sometimes it's hard to pass the database instance around this way, especially if some classes are created dynamically. As a simplest tradeoff solution we could create a class that can return its instance through static function:

class MyPDO
{
    protected static 
$instance;
    protected 
$pdo;

    protected function 
__construct() {
        
$opt  = array(
            
PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
            
PDO::ATTR_EMULATE_PREPARES   => FALSE,
        );
        
$dsn 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHAR;
        
$this->pdo = new PDO($dsnDB_USERDB_PASS$opt);

    }

    
// a classical static method to make it universally available
    
public static function instance()
    {
        if (
self::$instance === null)
        {
            
self::$instance = new self;
        }
        return 
self::$instance;
    }

    
// a proxy to native PDO methods
    
public function __call($method$args)
    {
        return 
call_user_func_array(array($this->pdo$method), $args);
    }

    
// a helper function to run prepared statements smoothly
    
public function run($sql$args = [])
    {
        
$stmt $this->pdo->prepare($sql);
        
$stmt->execute($args);
        return 
$stmt;
    }
}

So now we can change our class User this way

class User
{
    
/* @var MyPDO */
    
protected $db;

    protected 
$data;

    public function 
__construct()
    {
        
$this->db MyPDO::instance();
    }

    public function 
find($id)
    {        
        
$this->data $this->db->run("SELECT * FROM users WHERE id = ?", [$id])->fetch();

    }
}

It will be less flexible in case you will need different database providers for different User instances but at least it is using the centralized $db class variable.

The blunt singleton, for your procedural code

If you came here just looking for the replacement to the good old mysql_query function to be used in your all-procedural code, it's ok to use an otherwise despised Singleton pattern.

<?php
define
('DB_HOST''localhost');
define('DB_NAME''test');
define('DB_USER''root');
define('DB_PASS''');
define('DB_CHAR''utf8');

class 
DB
{
    protected static 
$instance null;

    protected function 
__construct() {}
    protected function 
__clone() {}

    public static function 
instance()
    {
        if (
self::$instance === null)
        {
            
$opt  = array(
                
PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                
PDO::ATTR_EMULATE_PREPARES   => FALSE,
            );
            
$dsn 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHAR;
            
self::$instance = new PDO($dsnDB_USERDB_PASS$opt);
        }
        return 
self::$instance;
    }

    public static function 
__callStatic($method$args)
    {
        return 
call_user_func_array(array(self::instance(), $method), $args);
    }

    public static function 
run($sql$args = [])
    {
        if (!
$args)
        {
             return 
self::instance()->query($sql);
        }
        
$stmt self::instance()->prepare($sql);
        
$stmt->execute($args);
        return 
$stmt;
    }
}

just put this code in the file called, say, database.php, put it somewhere in your site and don't forget to include it anywhere you need a database.

Examples

The examples below demonstrate the use of the singleton approach above. The code is ready to use. You may copy and paste the code below and run it as is. Just don't forget to create a 'database.php' file from the chapter above.

<?php
require_once 'database.php';

# Table creation
DB::query("CREATE temporary TABLE pdowrapper (id int auto_increment primary key, name varchar(255))");

# Prepared statement multiple execution
$stmt DB::prepare("INSERT INTO pdowrapper VALUES (NULL, ?)");
foreach ([
'Sam','Bob','Joe'] as $name)
{
    
$stmt->execute([$name]);
}
var_dump(DB::lastInsertId());
//string(1) "3"

# Getting rows in a loop
$stmt DB::run("SELECT * FROM pdowrapper");
while (
$row $stmt->fetch(PDO::FETCH_LAZY))
{
    echo 
$row['name'],",";
    echo 
$row->name,",";
    echo 
$row[1], PHP_EOL;
}
/*
Sam,Sam,Sam
Bob,Bob,Bob
Joe,Joe,Joe
*/

# Getting one row
$id  1;
$row DB::run("SELECT * FROM pdowrapper WHERE id=?", [$id])->fetch();
var_export($row);
/*
array (
  'id' => '1',
  'name' => 'Sam',
)
*/

# Getting single field value
$name DB::run("SELECT name FROM pdowrapper WHERE id=?", [$id])->fetchColumn();
var_dump($name);
//string(3) "Sam"

# Getting array of rows
$all DB::run("SELECT name, id FROM pdowrapper")->fetchAll(PDO::FETCH_KEY_PAIR);
var_export($all);
/*
array (
  'Sam' => '1',
  'Bob' => '2',
  'Joe' => '3',
)
*/

# Update
$new 'Sue';
$stmt DB::run("UPDATE pdowrapper SET name=? WHERE id=?", [$new$id]);
var_dump($stmt->rowCount());
//int(1)

Related articles: