PHP Connecting with PDO

PDO (PHP Data Objects) is PHP’s standard interface for talking to databases. Instead of learning a different API for MySQL, PostgreSQL, SQLite, and every other database, you learn PDO once and it works the same way across all of them. This lesson covers exactly how to open a PDO connection, what the connection string actually means, which options matter, and the mistakes that trip up almost every beginner.

Overview: How PDO Connections Work

PDO is a database abstraction layer, not a database driver itself. When you write new PDO(...), PHP looks at the prefix of the connection string (called the DSN, or Data Source Name) to decide which underlying driver to load — pdo_mysql, pdo_sqlite, pdo_pgsql, and so on. These drivers are compiled PHP extensions that must be enabled in your php.ini (on most modern installs, pdo_mysql and pdo_sqlite are enabled by default).

Under the hood, three things happen when you construct a PDO object:

PHP parses the DSN string to identify the driver and its parameters (host, port, database name, charset, or a file path for SQLite).
The chosen driver opens a real network socket or file handle to the database server and performs the authentication handshake using the username and password you supplied.
If the handshake succeeds, PHP hands you back a PDO object that wraps that live connection. If it fails, PDO always throws a PDOException — connection failures are the one place PDO throws regardless of your error-mode setting.

That last point matters: unlike queries (which by default fail silently unless you configure PDO otherwise), a bad connection always throws. You should always wrap connection code in a try/catch block.

A single PDO object represents one open connection. You typically create it once (often in a small bootstrap file or a dedicated class) and reuse it for every query in the request, rather than opening a new connection every time you need to talk to the database.

Syntax

$pdo = new PDO(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null);

$dsn — the Data Source Name: a string that tells PDO which driver to use and how to reach the database, e.g. "mysql:host=localhost;dbname=shop;charset=utf8mb4".
$username — the database user to authenticate as (omit for drivers like SQLite that don’t need one).
$password — the password for that user.
$options — an associative array of PDO::ATTR_* constants that configure connection behavior (error mode, fetch mode, charset, persistence, etc.).

Common DSN Formats

Driver	Example DSN
MySQL / MariaDB	`mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4`
PostgreSQL	`pgsql:host=127.0.0.1;port=5432;dbname=shop`
SQLite	`sqlite:/var/www/data/app.db`
SQLite (in-memory)	`sqlite::memory:`

Key PDO::ATTR_* Options

Constant	Purpose
`PDO::ATTR_ERRMODE`	How PDO reports errors. Set to `PDO::ERRMODE_EXCEPTION` almost always.
`PDO::ATTR_DEFAULT_FETCH_MODE`	Default shape of fetched rows, e.g. `PDO::FETCH_ASSOC`.
`PDO::ATTR_EMULATE_PREPARES`	Whether PDO emulates prepared statements instead of using the database’s native ones. Set to `false` for real prepared statements and correct type handling.
`PDO::ATTR_PERSISTENT`	Reuses a connection across requests when supported by the SAPI. Use with caution.

Examples

Example 1: A Basic MySQL Connection

<?php
$host = '127.0.0.1';
$db   = 'shop';
$user = 'app_user';
$pass = 'secret';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

try {
    $pdo = new PDO($dsn, $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected to '$db' successfully.";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Output:

Connected to 'shop' successfully.

This builds the DSN using string interpolation, then passes it along with the credentials to the PDO constructor. The setAttribute() call after construction switches on exception-based error reporting for every future call on this connection — without it, query errors would fail silently by default.

Example 2: Connecting with an Options Array

<?php
$dsn = 'mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4';
$user = 'app_user';
$pass = 'secret';

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

echo "Preparing to connect with " . count($options) . " PDO options.\n";

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connection established.";
} catch (PDOException $e) {
    throw new PDOException($e->getMessage(), (int) $e->getCode());
}

Output:

Preparing to connect with 4 PDO options.
Connection established.

Passing the options array directly to the constructor (rather than calling setAttribute() afterward) is the preferred style: it configures error mode, fetch mode, and prepared-statement behavior in one place, and guarantees the connection is never used in a misconfigured state, even for the very first query.

Example 3: A Reusable Connection with a Singleton Class

<?php
final class Database
{
    private static ?PDO $instance = null;

    private function __construct() {}

    public static function connection(): PDO
    {
        return self::$instance ??= new PDO(
            'sqlite:' . __DIR__ . '/data/app.db',
            options: [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]
        );
    }
}

$pdo1 = Database::connection();
$pdo2 = Database::connection();

echo $pdo1 === $pdo2 ? "Same connection reused." : "Different connections.";

Output:

Same connection reused.

The private constructor prevents anyone from calling new Database(), forcing all access through connection(). The nullsafe-coalescing assignment ??= creates the PDO instance only the first time it’s called and returns the cached object on every later call, so the whole application shares one connection. Note the named argument options:, a PHP 8 feature that makes the call self-documenting without needing a temporary variable.

How It Works Step by Step

PHP parses the DSN string up to the first colon to determine the driver name (mysql, sqlite, pgsql, …).
PHP checks that the matching PDO driver extension is loaded; if not, it throws a PDOException immediately with a “could not find driver” message.
The driver parses the remaining key=value pairs in the DSN (host, port, dbname, charset) and opens a low-level connection — a TCP socket for network databases, or a file handle for SQLite.
If a username/password were supplied, the driver performs the database’s authentication handshake.
On success, PDO applies any attributes from the $options array and returns the fully configured PDO object.
On failure at any of these steps, PDO throws a PDOException containing the underlying driver’s error message and SQLSTATE code.

Common Mistakes

Mistake 1: Leaving the Default (Silent) Error Mode

By default, PDO’s error mode is PDO::ERRMODE_SILENT, meaning failed queries return false instead of throwing. New developers often assume PDO always throws, and end up with confusing bugs downstream.

Wrong:

<?php
$pdo = new PDO('mysql:host=127.0.0.1;dbname=shop', 'app_user', 'secret');
$stmt = $pdo->query('SELECT * FROM users WHERE id = 9999999');
$row = $stmt->fetch();
echo $row['name'];

If the query fails, $stmt is silently set to false, and calling ->fetch() on false triggers a fatal error that has nothing to do with the real underlying database problem.

Correct:

<?php
$pdo = new PDO('mysql:host=127.0.0.1;dbname=shop', 'app_user', 'secret');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $stmt = $pdo->query('SELECT * FROM users WHERE id = 9999999');
    $row = $stmt->fetch();
    echo $row['name'] ?? 'User not found';
} catch (PDOException $e) {
    echo 'Query failed: ' . $e->getMessage();
}

Mistake 2: Leaking Connection Errors to the Browser

Echoing the raw exception message straight to the page exposes internal details — hostnames, database names, sometimes even credentials in the error text — to anyone who can trigger a failed connection.

Wrong:

<?php
try {
    $pdo = new PDO('mysql:host=localhost;dbname=shop', 'root', 'P@ssw0rd123');
} catch (PDOException $e) {
    echo $e->getMessage();
}

Correct:

<?php
$host = getenv('DB_HOST');
$db   = getenv('DB_NAME');
$user = getenv('DB_USER');
$pass = getenv('DB_PASS');

try {
    $pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass);
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    echo 'A server error occurred. Please try again later.';
}

Credentials come from environment variables instead of being hardcoded, and the real error is logged server-side while the visitor only sees a generic message.

Best Practices

Always set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION so failures are impossible to silently ignore.
Always include charset=utf8mb4 in MySQL DSNs to avoid corrupted multi-byte text (emoji, accented characters) and certain SQL-injection edge cases tied to character encoding.
Never hardcode credentials; load them from environment variables or a configuration file kept outside your web root and version control.
Set PDO::ATTR_EMULATE_PREPARES to false so prepared statements use the database’s native implementation, which is both safer and correctly typed.
Create the connection once per request and reuse it, rather than opening a fresh PDO object before every query.
Never echo raw exception messages from a production connection failure back to the client.

Practice Exercises

Write a script that connects to a SQLite database file named practice.db in the current directory, sets the error mode to exceptions, and prints a success message if the connection works.
Modify Example 1 so the connection options (error mode, fetch mode, and disabling emulated prepares) are passed as the fourth constructor argument instead of via a separate setAttribute() call.
Write a Config class with a static method dsn(): string that builds a MySQL DSN string from four class constants (host, port, database name, charset), then use it to construct a PDO connection.

Summary

PDO is a database abstraction layer; the DSN prefix (mysql:, sqlite:, pgsql:) tells it which driver to load.
The constructor is new PDO($dsn, $username, $password, $options); connection failures always throw a PDOException.
Always enable PDO::ERRMODE_EXCEPTION, either via the options array or setAttribute(), so query errors aren’t silently swallowed.
Pass configuration through the options array when possible — it’s clearer and guarantees correct behavior from the very first call.
Keep credentials out of source code and never expose raw connection error messages to end users.

PHP Connecting with PDO

Overview: How PDO Connections Work

Syntax

Common DSN Formats

Key PDO::ATTR_* Options

Examples

Example 1: A Basic MySQL Connection

Example 2: Connecting with an Options Array

Example 3: A Reusable Connection with a Singleton Class

How It Works Step by Step

Common Mistakes

Mistake 1: Leaving the Default (Silent) Error Mode

Mistake 2: Leaking Connection Errors to the Browser

Best Practices

Practice Exercises

Summary

Table of Contents