This package is installable and autoloadable via Composer as atlas/pdo.
composer require atlas/pdo ^2.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();
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();
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.
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);
The Connection provides several fetch*() methods to help reduce boilerplate
code; these all use perform() internally.
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);
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);
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.
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->yieldKeyPair($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) {
// ...
}
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' forfalseor string '1' fortrue. This addresses issues with a long-standing behavior in PDO.
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.