PDO (PHP Data Objects)

PDO is PHP's database-agnostic data access layer. Rather than providing one extension per database engine with its own quirks and function names, PDO gives you a single PDO class with a consistent set of methods, and lets a separate driver underneath handle the differences between MySQL, PostgreSQL, SQLite, SQL Server, and others. This page covers connecting, configuring error handling, the difference between ->query() and ->prepare(), and fetching results — the core vocabulary you'll use on almost every PDO page that follows.

Connecting with a DSN

A PDO connection is described by a DSN (Data Source Name) — a single string that tells PDO which driver to use and how to reach the database. The driver name comes first (mysql:, pgsql:, sqlite:), followed by driver-specific options.

Connecting to MySQL

PHP

<?php
$dsn = 'mysql:host=localhost;port=3306;dbname=shop;charset=utf8mb4';

$pdo = new PDO($dsn, 'app_user', 'secret');

Connecting to SQLite (just a file path — no server)

PHP

<?php
$pdo = new PDO('sqlite:/var/data/shop.sqlite');

Notice that the SQLite example doesn't take a username, password, or host — there's no server to authenticate against, since SQLite reads and writes a single file directly. That's the DSN doing its job: the driver-specific details live inside the string, while the rest of your code that runs queries against $pdo stays identical no matter which driver is in use.

Making PDO throw exceptions on errors

By default, PDO's error mode is fairly quiet — some failures return false instead of raising anything, which makes them easy to miss. Setting PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION makes any database error throw a PDOException instead, so failures surface immediately and can be handled with normal try/catch blocks rather than manual if checks after every call.

Enabling exception mode at connection time

PHP

<?php
try {
    $pdo = new PDO(
        'mysql:host=localhost;dbname=shop;charset=utf8mb4',
        'app_user',
        'secret',
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]
    );
} catch (PDOException $e) {
    // Log the real message; never show it to the visitor
    error_log('Database connection failed: ' . $e->getMessage());
    die('Something went wrong. Please try again later.');
}

Setting a default fetch mode saves repetition

Passing `PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC` in the options array means every subsequent fetch call returns associative arrays by default, without needing to repeat `PDO::FETCH_ASSOC` as an argument each time you call `->fetch()`.

->query() for fixed SQL, ->prepare() for anything with input

->query() runs a SQL string immediately and hands back a PDOStatement you can iterate over. It's appropriate only when the SQL contains no data from outside your own code — a static report, a SHOW TABLES, or similar.

A query with no external input

PHP

<?php
$statement = $pdo->query('SELECT COUNT(*) AS total FROM products');
$row = $statement->fetch();

echo 'Total products: ' . $row['total'];

Total products: 128

Don't reach for ->query() once a variable is involved

As soon as a value from a form, URL, or any other outside source needs to appear in the SQL, switch to `->prepare()` with bound parameters instead. Building the string with concatenation — even for what looks like a harmless numeric id — opens the door to SQL injection. The prepared statements page covers this in depth, but the short version is: `->prepare()` plus bound parameters, always, whenever a query includes outside input.

The same idea, done safely with prepare()

PHP

<?php
$minPrice = $_GET['min_price'] ?? 0;

$statement = $pdo->prepare('SELECT id, name, price FROM products WHERE price >= ?');
$statement->execute([$minPrice]);

$products = $statement->fetchAll(); // an array of associative arrays

Fetching results

PDO::FETCH_ASSOC is the most commonly used fetch mode: each row comes back as an associative array keyed by column name. ->fetch() returns one row at a time (or false when there are no more), while ->fetchAll() returns every remaining row as a single array, which is convenient for smaller result sets.

Row-by-row vs all-at-once

PHP

<?php
$statement = $pdo->query('SELECT id, name FROM products');

// One row at a time
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
    echo $row['name'] . PHP_EOL;
}

// Or, all rows in one call
$statement = $pdo->query('SELECT id, name FROM products');
$allProducts = $statement->fetchAll(PDO::FETCH_ASSOC);

Switching database drivers with minimal code change

This is the core value proposition of PDO: because ->prepare(), ->execute(), and ->fetch() behave the same no matter which driver is loaded, moving an application from MySQL to PostgreSQL — or spinning up a lightweight SQLite copy for local development or testing — is often just a matter of changing the DSN string.

Same query code, different DSN

PHP

<?php
// Production
$pdo = new PDO('mysql:host=db.prod;dbname=shop;charset=utf8mb4', $user, $pass);

// Local development
$pdo = new PDO('sqlite:' . __DIR__ . '/dev.sqlite');

// The rest of the application's code is unchanged:
$statement = $pdo->prepare('SELECT * FROM products WHERE id = ?');
$statement->execute([$productId]);

The caveat is that this only holds for SQL that's portable across engines. Vendor-specific syntax — MySQL's LIMIT x, y shorthand, PostgreSQL's RETURNING clause, or engine-specific functions — will still need adjusting if you actually change databases. PDO makes the connection and fetching layer portable; it doesn't rewrite your SQL for you.

Build the DSN for your driver, then create the PDO object with a username and password.
Set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION so failures throw instead of failing silently.
Use ->query() only for SQL with no outside input; use ->prepare() plus ->execute() for everything else.
Fetch with PDO::FETCH_ASSOC (or set it as the default fetch mode) to get column-name-keyed arrays.

Tip

Set `PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION` on every connection you create, in every project. It costs one array entry and turns silent, easy-to-miss database failures into exceptions that your normal error handling will actually catch.