1.1.1. PDO Connection

1.1.1.1. Installation

This package is installable and autoloadable via Composer as atlas/pdo.

composer require atlas/pdo ^2.0

1.1.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();

If you want to make sure certain SQL queries are run at connection time, you can create your own callable factory:

use Atlas\Pdo\Connection;

// define a callable factory that creates a Connection and executes a query
$factory = function () {
    $connection = Connection::new('sqlite::memory:');
    $connection->exec('...');
    return $connection;
}

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

1.1.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.

1.1.1.4. Performing Queries

Instead of issuing prepare(), a series of bindValue() calls, and then execute(), you can bind values and get back a PDOStatement result in one call using the Connection perform() method:

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

$sth = $connection->perform($stm, $bind);

1.1.1.5. Fetching Results

The Connection provides several fetch*() methods to help reduce boilerplate code; these all use perform() internally.

1.1.1.5.1. fetchAffected()

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

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

1.1.1.5.2. fetchAll()

The fetchAll() method returns a sequential array of all rows; each row is an associative array where the keys are the column names.

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

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

1.1.1.5.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);

1.1.1.5.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).

1.1.1.5.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);

1.1.1.5.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']);

1.1.1.5.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']);

1.1.1.5.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);

1.1.1.5.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);

1.1.1.5.10. fetchValue()

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

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

1.1.1.6. 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.

1.1.1.6.1. yieldAll()

This is the yielding equivalent of fetchAll().

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

1.1.1.6.2. yieldColumn()

This is the yielding equivalent of fetchColumn().


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

1.1.1.6.3. yieldKeyPair()

This is the yielding equivalent of fetchKeyPair().

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

1.1.1.6.4. yieldObjects()

This is the yielding equivalent of fetchObjects().

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

1.1.1.6.5. yieldUnique()

This is the yielding equivalent of fetchUnique().

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

1.1.1.7. Explicit Bind Value Types

When binding values on a perform()-based query (which includes all fetch*() and yield*() queries), the Connection will use PDO::PARAM_STR as the value type by default.

If you want to override the default type, pass the value as an array where the first element is the value and the second element is the PDO param type. For example:

// PDO::PARAM_STR by default
$bind['foo'] = 1;

// force to PDO::PARAM_INT
$bind['foo'] = [1, PDO::PARAM_INT]

Note:

If you pass a boolean value and force the type to PDO::PARAM_BOOL, the Connection will store the value as a string '0' for false or string '1' for true. This addresses issues with a long-standing behavior in PDO.

1.1.1.8. 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

1.1.1.8.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.

1.1.1.8.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.

1.1.1.9. 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.