Simple yet efficient PDO wrapper

  1. Making your code shorter.
  2. Using PDO in other classes

Making your code shorter.

The main problem with both PDO and mysqli prepared statements is that the mechanism was designed for the multiple execution, when prepare and bind are 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 seldom used, 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 routine 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 have a function similar to Postgres' pg_query_params() (or mysqli's execute_query) to encapsulate the prepare/execute routine and return the statement.

A simple PDO function

The simplest solution would be to create a function that accepts a PDO object, an SQL query and an optional array with parameters for execute() and returns a PDOStatement object: 

function pdo($pdo, $sql, $args = NULL)
{
    $stmt = $pdo->prepare($sql);
    $stmt->execute($args);
    return $stmt;
}

It would serve if your code is mostly procedural. Given you can chain any PDOStatement method directly to this function's call, most of queries will become just one-liners:

// getting the number of rows in the table
$count = pdo($pdo, "SELECT count(*) FROM users")->fetchColumn();

// the user data based on email
$user = pdo($pdo, "SELECT * FROM users WHERE email=?", [$email])->fetch();

// getting many rows from the table
$data = pdo($pdo, "SELECT * FROM users WHERE salary > ?", [$salary])->fetchAll();

// getting the number of affected rows from DELETE/UPDATE/INSERT
$deleted = pdo($pdo, "DELETE FROM users WHERE id=?", [$id])->rowCount();

// insert
pdo($pdo, "INSERT INTO users VALUES (null, ?,?,?)", [$name, $email, $password]);

// named placeholders are also welcome though I find them a bit too verbose
pdo($pdo, "UPDATE users SET name=:name WHERE id=:id", ['id'=>$id, 'name'=>$name]);

// using a sophisticated fetch mode, indexing the returned array by id
$indexed = pdo($pdo, "SELECT id, name FROM users")->fetchAll(PDO::FETCH_KEY_PAIR);

A simple PDO class

The next step would be, however, to create a class that would contain all the PDO connection code, as well as the useful run() function. Note that for sake of usability this class can be used with mysql database only. 

class DB
{
    public $pdo;

    public function __construct($db, $username = NULL, $password = NULL, $host = '127.0.0.1', $port = 3306, $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);
        $dsn = "mysql:host=$host;dbname=$db;port=$port;charset=utf8mb4";

        try {
            $this->pdo = new \PDO($dsn, $username, $password, $options);
        } catch (\PDOException $e) {
            throw new \PDOException($e->getMessage(), (int)$e->getCode());
        }
    }
    public function run($sql, $args = NULL)
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }
}

Now this class can be included as a constructor parameter in all other classes that require a database interaction. Notice that PDO variable is made public. That's because we will need to access the full PDO functionality through this variable.

To use this class first create an instance, like this

$db = new DB($dbname, $user, $password);

and then used just like the function but instead of rather clumsy pdo($pdo, $sql); we can writh it as $db->run($sql);

$db->run("INSERT INTO users VALUES (null, ?,?,?)", [$name, $email, $password]);
$id = $db->pdo->lastInsertId();

Extending PDO

Another way to access the full PDO functionality would be to extend our object from PDO.

Let's extend PDO and add a new method called run() (as I prefer short names for the frequently called functions). Also, for convenience, we can add the most essential connection options to the constructor:

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)
    {
        $stmt = $this->prepare($sql);
        $stmt->execute($args);
        return $stmt;
    }
}

and now we've got a new class MyPDO which works as 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.

Using PDO in other classes

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 but only one should be really used:

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 using 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);

Note that this approach will do as long as all your classes are instantiated manually. In case some classes are instantiated dynamically, you will need a Dependency Injection Container, that will store the single instance of the database connection class, and inject it in all created classes if they need it.


Related articles:



Got a question?

I am the only person to hold a gold badge in , and on Stack Overflow and I am eager to show the right way for PHP developers.

Besides, your questions let me make my articles even better, so you are more than welcome to ask any question you have.

Click here to ask!

Latest article:
PHP Error Reporting

SEE ALSO:

Latest comments: