README

You are reading the documentation for Silex 2.x. Switch to the documentation for Silex 1.x.

PHP Data Objects

The PdoServiceProvider provides integration with the PHP Data Objects (PDO) extension.

Parameters

• pdo.dsn (optional): The Data Source Name, or DSN, contains the information required to connect to the database.
• pdo.driver (optional): The PDO driver implementation to use.
• pdo.connection (optional): A collection of driver-specific connection parameters for specifying the connection string.
• pdo.username (optional): The user name for the DSN string.
• pdo.password (optional): The password for the DSN string.
• pdo.options (optional): A collection of driver-specific connection options.
• pdo.attributes (optional): A collection of attributes to set.

The parameters pdo.driver and pdo.connection are ignored if pdo.dsn is set.

Services

• pdo: The PDO connection instance. The main way of interacting with PDO.
• pdo.connections: The collection of PDO connection instances. See section on using multiple databases for details.
• pdo.connect: Factory for PDO connection instances.

Registering

Example #1 Connecting to MySQL

$app->register(new \LiquidBox\Silex\Provider\PdoServiceProvider(), array(
    'pdo.dsn' => 'mysql:host=localhost;dbname=test',
    'pdo.username' => $user,
    'pdo.password' => $passwd,
));

// or

$app->register(new \LiquidBox\Silex\Provider\PdoServiceProvider(), array(
    'pdo.connection' => array(
        'host'   => 'localhost',
        'dbname' => 'test'
    ),
    'pdo.username' => $user,
    'pdo.password' => $passwd,
));

The two registered connections are equivalent.

Example #2 Connecting to SQLite

$app->register(new \LiquidBox\Silex\Provider\PdoServiceProvider(), array(
    'pdo.dsn' => 'sqlite::memory:',
));

// or

$app->register(new \LiquidBox\Silex\Provider\PdoServiceProvider(), array(
    'pdo.driver' => 'sqlite',
    'pdo.connection' => ':memory:',
));

Example #3 Using Doctrine service names

$app->register(new \LiquidBox\Silex\Provider\PdoServiceProvider('db', 'dbs'), array(
    'pdo.driver' => 'pdo_pgsql',
    'pdo.connection' => array(
        'host'   => 'localhost',
        'dbname' => 'test',
    ),
    'pdo.username' => $user,
    'pdo.password' => $passwd,
));

The services pdo and pdo.connections will be renamed db and dbs respectively.

Add PDO as a dependency:

composer require liquidbox/silex-pdo:^2.0

Usage

Example #1 Demonstrate query

$sql = 'SELECT name, color, calories FROM fruit ORDER BY name';

foreach ($app['pdo']->query($sql) as $row) {
    echo implode("\t", $row) . PHP_EOL;
}

Example #2 Prepare an SQL statement with named parameters

/* Execute a prepared statement by passing an array of values */
$sql = <<<heredoc
SELECT name, color, calories
    FROM fruit
    WHERE calories < :calories AND color = :color
heredoc;

$pdoStatement = $app['pdo']->prepare($sql, array(
    \PDO::ATTR_CURSOR => \PDO::CURSOR_FWDONLY,
));

$pdoStatement->execute(array(':calories' => 150, ':color' => 'red'));

$red = $pdoStatement->fetchAll();

$pdoStatement->execute(array(':calories' => 175, ':color' => 'yellow'));

$yellow = $pdoStatement->fetchAll();

Using multiple databases

The PDO provider can allow access to multiple databases. In order to configure the data sources, use pdo.dsn as an array of configurations where keys are connection names and values are parameters:

$app->register(new LiquidBox\Silex\Provider\PdoServiceProvider(), array(
    'pdo.dsn' => array(
        'mysql_read' => array(
            'connection' => array(
                'host'    => 'mysql_read.someplace.tld',
                'dbname'  => 'my_database',
                'charset' => 'utf8mb4',
            ),
            'username' => 'my_username',
            'password' => 'my_password',
        ),
        'mysql_write' => array(
            'connection' => array(
                'host'    => 'mysql_write.someplace.tld',
                'dbname'  => 'my_database',
                'charset' => 'utf8mb4',
            ),
            'username' => 'my_username',
            'password' => 'my_password',
        ),
    ),
));

The first registered connection is the default and can simply be accessed as you would if there was only one connection. Given the above configuration, these two lines are equivalent:

$app['pdo']->query('SELECT * FROM table')->fetchAll();

$app['pdo.connections']['mysql_read']->query('SELECT * FROM table')->fetchAll();

You can use different drivers for each connection.

$app->register(
    new LiquidBox\Silex\Provider\PdoServiceProvider(null, 'pdo.dbs'),
    array(
        'pdo.dsn' => array(
            'member_db' => array(
                'driver'     => 'pdo_pgsql',
                'connection' => array(
                    'host'    => 'member_data.someplace.tld',
                    'dbname'  => 'membership',
                ),
                'username' => $pgsql_user,
                'password' => $pgsql_passwd,
            ),
            'content_db' => array(
                'connection' => array(
                    'host'    => 'content_data.someplace.tld',
                    'dbname'  => 'media_info',
                    'charset' => 'utf8',
                ),
                'username' => $mysql_user,
                'password' => $mysql_passwd,
            ),
            'session_storage' => array('dsn' => 'sqlite::memory:'),
        ),
    )
);

This registers $app['pdo.dbs']['member_db'], $app['pdo.dbs']['content_db'], and $app['pdo.dbs']['session_storage'] using PostgreSQL, MySQL, and SQLite drivers respectively.

Traits

LiquidBox\Silex\Application\PdoTrait adds the following shortcut:

• prepare: Prepares a statement for execution and returns a statement object.

/* Execute a prepared statement by passing an array of values */
$pdoStatement = $app->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');

$pdoStatement->execute(array(150, 'red'));

$red = $pdoStatement->fetchAll();

$pdoStatement->execute(array(175, 'yellow'));

$yellow = $pdoStatement->fetchAll();

For more information, check out the official PDO documentation.