Connecting with PDO

Open a database connection with PDO, set it to throw on errors, and keep the password out of your source files.

Syntaxnew PDO(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null)

A database is a separate program that stores data in tables and answers questions about it. PHP does not store your data itself; it asks the database. PDO, which stands for PHP Data Objects, is the part of PHP that opens a connection to a database and sends instructions over it. One PDO object is one open connection.

The DSN

A DSN (data source name) is one string that says which kind of database, where it is, and which database on it to use. For MySQL it looks like mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4. The word before the colon is the driver, and it decides what the rest of the string may contain, so a PostgreSQL DSN is written differently. Put the character set in the DSN rather than sending a separate query for it.

Options worth setting

PDO::ATTR_ERRMODE set to PDO::ERRMODE_EXCEPTION makes a failed query throw a PDOException instead of returning false quietly. Since PHP 8.0 that is already the default; setting it says out loud what the file depends on. PDO::ATTR_DEFAULT_FETCH_MODE set to PDO::FETCH_ASSOC makes each row an array keyed by column name, instead of the default PDO::FETCH_BOTH, which returns every value twice.

Credentials

The username and password are separate arguments, not part of the DSN. Read them from the environment so they are never in a file you commit. PHP 8.4 added PDO::connect(): it takes the same arguments as new PDO() and opens the same connection, but returns a driver-specific subclass such as Pdo\Mysql.

Example

Try it yourself
Loading editor…
Press Run to execute the code.

When to use it

  • Write the single config file every page in a project pulls its database connection from.
  • Move a hard-coded password out of a committed file and into an environment variable.
  • Fix 'Fatal error: Uncaught PDOException: could not find driver' by naming the right driver in the DSN.
  • Make every query throw on failure instead of silently returning false.

More examples

The whole connection file

This is the file every other file gets its connection from: $pdo = require __DIR__ . '/config/database.php';. The three options are the ones that change PDO's behaviour for the whole connection. ATTR_EMULATE_PREPARES set to false asks MySQL to prepare the statement on the server rather than letting PDO assemble it. The catch logs the real message and shows the visitor nothing, because the real message names your host and user.

Example · php
<?php

// config/database.php
// Returns one connection. Include it once per request and pass the object around.

declare(strict_types=1);

$dsn = sprintf(
    'mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4',
    getenv('DB_HOST') ?: '127.0.0.1',
    getenv('DB_PORT') ?: '3306',
    getenv('DB_NAME') ?: throw new RuntimeException('DB_NAME is not set'),
);

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    return new PDO(
        $dsn,
        getenv('DB_USER') ?: null,
        getenv('DB_PASSWORD') ?: null,
        $options,
    );
} catch (PDOException $e) {
    error_log('database connection failed: ' . $e->getMessage());

    http_response_code(500);
    exit('The site is temporarily unavailable.');
}

Shorthand and longhand: PDO::connect()

Both lines open the same connection and both objects are usable everywhere a PDO is expected. PDO::connect(), added in PHP 8.4, picks a subclass based on the driver in the DSN, which is how driver-only methods such as Pdo\Mysql::getWarningCount() become reachable. Use new PDO() unless you need one of those methods, because it works on every version you may be deployed to.

Example · php
<?php

declare(strict_types=1);

$dsn = 'mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4';
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

// Longhand, every PHP version: a plain PDO object.
$a = new PDO($dsn, 'shop_app', getenv('DB_PASSWORD'), $options);
var_dump($a instanceof PDO); // true

// PHP 8.4 and later: the same connection, as the driver's own subclass.
$b = PDO::connect($dsn, 'shop_app', getenv('DB_PASSWORD'), $options);
var_dump($b instanceof PDO);        // true
var_dump($b instanceof Pdo\Mysql);  // true

Other drivers, same code afterwards

The DSN is the one place the choice of database shows up in PHP. Writing your queries through prepare() and execute() keeps the rest of the code portable, which is what makes it possible to run tests against SQLite and production against MySQL. The SQL itself is not fully portable, so keep to plain statements where you can.

Example · php
<?php

declare(strict_types=1);

// PostgreSQL. No charset in the DSN; the database decides its own encoding.
$pg = 'pgsql:host=127.0.0.1;port=5432;dbname=shop';

// SQLite. One file on disk, and no username or password at all.
$sqlite = 'sqlite:' . __DIR__ . '/storage/shop.sqlite';

// The DSN is the only line that changes. Everything after it is identical:
// $pdo->prepare(...), ->execute(...), ->fetch() behave the same way.
$dsn = $pg;

Related

php pdo preparedphp sql basicsphp pdo errorsphp secrets configphp config prod

Discussion

  • Be the first to comment on this lesson.