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 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 PDOStatement, allowing 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 Database class

The next step would be, however, to create a class that would contain the necessary PDO connection code, as well as useful run() function. Note that for simplicity this class made to work 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();

Note that in case we need a native PDO method or property, it can be accessed through $pdo property.

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, but they can be overwritten at runtime.

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
{
    protected MyPDO $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);

Creating classes dynamically

The above approach will do as long as all your classes are instantiated manually. However, oftimes a PHP application implements a router, which creates required classes on the fly, based on the routing rules. In this case you will need a Dependency Injection Container, that will store single instance of the database connection class, and provide it as a parameter (inject it) in all classes it creates, in case they have Database class in their constructir.


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: