2.6.1. PDO Connection

2.6.1.1. Installation

This package is installable and autoloadable via Composer as atlas/pdo. Add the following lines to your composer.json file, then call composer update.

{
    "require": {
        "atlas/pdo": "~1.0"
    }
}

2.6.1.2. Instantiation

The easiest way to create a Connection is to use its static new() method, either with PDO connection arguments, or with an actual PDO instance:

use Atlas\Pdo\Connection;

// pass PDO constructor arguments ...
$connection = Connection::new(
    'mysql:host=localhost;dbname=testdb',
    'username',
    'password'
);

// ... or a PDO instance.
$connection = Connection::new($pdo);

If you need a callable factory to create a Connection and its PDO instance at a later time, such as in a service container, you can use the Connection::factory() method:

use Atlas\Pdo\Connection;

// get a callable factory that creates a Connection
$factory = Connection::factory('sqlite::memory:');

// later, call the factory to instantiate the Connection
$connection = $factory();

2.6.1.3. Calling PDO Methods

The Connection acts as a proxy to the decorated PDO instance, so you can call any method on the Connection that you would normally call on PDO.

2.6.1.4. Fetching Results

The Connection provides several fetch*() methods to help reduce boilerplate code. Instead of issuing prepare(), a series of bindValue() calls, execute(), and then fetch*() on a PDOStatement, you can bind values and fetch results in one call on Connection directly.

2.6.1.4.1. fetchAll()

The plain-old PDO way to fetch all rows looks like this:

use PDO;

$pdo = new PDO('sqlite::memory:');

$stm  = 'SELECT * FROM test WHERE foo = :foo AND bar = :bar';
$bind = ['foo' => 'baz', 'bar' => 'dib'];
$sth = $pdo->prepare($stm);
$sth->execute($bind);
$result = $sth->fetchAll(PDO::FETCH_ASSOC);

This is how to do the same thing with an Atlas PDO Connection:

use Atlas\Pdo\Connection;

$connection = Connection::new('sqlite::memory:');

$stm  = 'SELECT * FROM test WHERE foo = :foo AND bar = :bar';
$bind = ['foo' => 'baz', 'bar' => 'dib'];

$result = $connection->fetchAll($stm, $bind);

2.6.1.4.2. fetchAffected()

The fetchAffected() method returns the number of affected rows.

$stm = "UPDATE test SET incr = incr + 1 WHERE foo = :foo AND bar = :bar";
$rowCount = $connection->fetchAffected($stm, $bind);

2.6.1.4.3. fetchColumn()

The fetchColumn() method returns a sequential array of the first column from all rows.

$result = $connection->fetchColumn($stm, $bind);

You can choose another column number with an optional third argument (columns are zero-indexed):

// use column 3 (i.e. the 4th column)
$result = $connection->fetchColumn($stm, $bind, 3);

2.6.1.4.4. fetchGroup()

The fetchGroup() method is like fetchUnique() except that the values aren't wrapped in arrays. Instead, single column values are returned as a single dimensional array and multiple columns are returned as an array of arrays.

$result = $connection->fetchGroup($stm, $bind, $style = PDO::FETCH_COLUMN)

Set $style to PDO::FETCH_NAMED when values are an array (i.e. there are more than two columns in the select).

2.6.1.4.5. fetchKeyPair()

The fetchKeyPair() method returns an associative array where each key is the first column and each value is the second column

$result = $connection->fetchKeyPair($stm, $bind);

2.6.1.4.6. fetchObject()

The fetchObject() method returns the first row as an object of your choosing; the columns are mapped to object properties. an optional 4th parameter array provides constructor arguments when instantiating the object.

$result = $connection->fetchObject($stm, $bind, 'ClassName', ['ctor_arg_1']);

2.6.1.4.7. fetchObjects()

The fetchObjects() method returns an array of objects of your choosing; the columns are mapped to object properties. An optional 4th parameter array provides constructor arguments when instantiating the object.

$result = $connection->fetchObjects($stm, $bind, 'ClassName', ['ctor_arg_1']);

2.6.1.4.8. fetchOne()

The fetchOne() method returns the first row as an associative array where the keys are the column names.

$result = $connection->fetchOne($stm, $bind);

2.6.1.4.9. fetchUnique()

The fetchUnique() method returns an associative array of all rows where the key is the value of the first column, and the row arrays are keyed on the remaining column names.

$result = $connection->fetchUnique($stm, $bind);

2.6.1.4.10. fetchValue()

The fetchValue() method returns the value of the first row in the first column.

$result = $connection->fetchValue($stm, $bind);

2.6.1.5. Yielding Results

The Connection provides several yield*() methods to help reduce memory usage. Whereas fetch*() methods may collect all the query result rows before returning them all at once, the equivalent yield*() methods generate one result row at a time. For example:

2.6.1.5.1. yieldAll()

This is the yielding equivalent of fetchAll().

foreach ($connection->yieldAll($stm, $bind) as $row) {
    // ...
}

2.6.1.5.2. yieldColumn()

This is the yielding equivalent of fetchColumn().


foreach ($connection->yieldColumn($stm, $bind) as $val) {
    // ...
}

2.6.1.5.3. yieldKeyPair()

This is the yielding equivalent of fetchKeyPair().

foreach ($connection->yieldPairs($stm, $bind) as $key => $val) {
    // ...
}

2.6.1.5.4. yieldObjects()

This is the yielding equivalent of fetchObjects().

$class = 'ClassName';
$args = ['arg0', 'arg1', 'arg2'];
foreach ($connection->yieldObjects($stm, $bind, $class, $args) as $object) {
    // ...
}

2.6.1.5.5. yieldUnique()

This is the yielding equivalent of fetchUnique().

foreach ($connection->yieldUnique($stm, $bind) as $key => $row) {
    // ...
}

2.6.1.6. Query Logging

It is sometimes useful to see a log of all queries passing through a Connection. To do so, call its logQueries() method, issue your queries, and then call getQueries().

// start logging
$connection->logQueries(true);

// at this point, all query(), exec(), perform(), fetch*(), and yield*()
// queries will be logged.

// get the query log entries
$queries = $connection->getQueries();

// stop logging
$connection->logQueries(false);

Each query log entry will be an array with these keys:

  • start: when the query started
  • finish: when the query finished
  • duration: how long the query took
  • performed: whether or not the query was actually perfomed; useful for seeing if a COMMIT actually occurred
  • statement: the query statement string
  • values: the array of bound values
  • trace: an exception trace showing where the query was issued

2.6.1.6.1. Logged Statements

When queries are not being logged, Connection::prepare() will return a normal PDOStatement. However, when queries are being logged, Connection::prepare() will return an Atlas\Pdo\LoggedStatement instance instead.

The LoggedStatement is an extension of PDOStatement, so it works the same way, but it has the added behavior of recording to the log when its execute() method is called.

2.6.1.6.2. Custom Loggers

You may wish to set a custom logger on the Connection. To do so, call setQueryLogger() and pass a callable with the signature function (array $entry) : void.

class CustomDebugger
{
    public function __invoke(array $entry) : void
    {
        // call an injected logger to record the entry
    }
}

$customDebugger = new CustomDebugger();
$connection->setQueryLogger($customDebugger);
$connection->logQueries(true);

// now the Connection will send query log entries to the CustomDebugger

Note:

If you set a custom logger, the Connection will no longer retain its own query log entries; they will all go to the custom logger. This means that getQueries() on the Connection not show any new entries.

2.6.1.7. Persistent Connections

Unlogged persistent Connection instances are fully supported, via the PDO::ATTR_PERSISTENT option at construction time.

Logged persistent Connection instances are almost fully supported. The only exception to full support is that, on calling Connection::prepare(), the returned statement instance (PersistentLoggedStatement) does not honor the PDOStatement::bindColumn() method. All other methods and behaviors are fully supported.