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"
}
}
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();
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.
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.
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);
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);
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);
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).
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);
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']);
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']);
The fetchOne() method returns the first row as an associative array where the keys are the column names.
$result = $connection->fetchOne($stm, $bind);
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);
The fetchValue() method returns the value of the first row in the first column.
$result = $connection->fetchValue($stm, $bind);
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:
This is the yielding equivalent of fetchAll().
foreach ($connection->yieldAll($stm, $bind) as $row) {
// ...
}
This is the yielding equivalent of fetchColumn().
foreach ($connection->yieldColumn($stm, $bind) as $val) {
// ...
}
This is the yielding equivalent of fetchKeyPair().
foreach ($connection->yieldPairs($stm, $bind) as $key => $val) {
// ...
}
This is the yielding equivalent of fetchObjects().
$class = 'ClassName';
$args = ['arg0', 'arg1', 'arg2'];
foreach ($connection->yieldObjects($stm, $bind, $class, $args) as $object) {
// ...
}
This is the yielding equivalent of fetchUnique().
foreach ($connection->yieldUnique($stm, $bind) as $key => $row) {
// ...
}
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 startedfinish: when the query finishedduration: how long the query tookperformed: whether or not the query was actually perfomed; useful
for seeing if a COMMIT actually occurredstatement: the query statement stringvalues: the array of bound valuestrace: an exception trace showing where the query was issuedWhen 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.
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.
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.