Databases with PDO

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against PHP 8.5 (8.5.11), checked on 2026-10-05; PDO::connect() and the driver subclasses need PHP 8.4. Every example ran against MySQL 8.4.11, PostgreSQL 17.11 and SQLite 3.46 from the official php:8.5-fpm image (with pdo_mysql, pdo_pgsql and pdo_sqlite), and the outputs are the real ones. SQL itself (joins, indexes, transactions, isolation levels) is covered in the SQL section; this page is about using it from PHP. In Laravel, the query builder and Eloquent sit on top of PDO (see Database and Query Builder).

Connecting

PDO (PHP Data Objects) is one interface for many databases; a driver (pdo_mysql, pdo_pgsql, pdo_sqlite, …) does the database-specific work. A connection is described by a DSN (data source name): the driver prefix and its parameters.

<?php
declare(strict_types=1);

$dsns = [
    'sqlite' => ['sqlite:/tmp/bookshelf.sqlite', null, null],
    'mysql'  => ['mysql:host=mysql;port=3306;dbname=bookshelf;charset=utf8mb4', 'bookshelf', getenv('DB_PASSWORD')],
    'pgsql'  => ['pgsql:host=pg;port=5432;dbname=bookshelf', 'bookshelf', getenv('DB_PASSWORD')],
];
foreach ($dsns as $driver => [$dsn, $user, $password]) {
    $pdo = PDO::connect($dsn, $user, $password);          // 8.4: returns the driver subclass
    try {
        $emulate = var_export($pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES), true);
    } catch (PDOException) {
        $emulate = 'n/a';                                   // SQLite has no emulation setting
    }
    printf("%-6s %-11s errmode=%d emulate=%-5s server=%s\n", $driver, $pdo::class,
        $pdo->getAttribute(PDO::ATTR_ERRMODE), $emulate, $pdo->getAttribute(PDO::ATTR_SERVER_VERSION));
}
var_dump(new PDO('sqlite::memory:') instanceof Pdo\Sqlite);  // false: new PDO stays a plain PDO

sqlite Pdo\Sqlite  errmode=2 emulate=n/a   server=3.46.1
mysql  Pdo\Mysql   errmode=2 emulate=true  server=8.4.11
pgsql  Pdo\Pgsql   errmode=2 emulate=false server=17.11
bool(false)

Source: PDO::connect, PDO — Connections, PDO_MYSQL DSN, PDO_PGSQL DSN and PDO_SQLITE DSN

What the output shows:

PDO::connect() (8.4) returns an instance of the driver’s subclass, Pdo\Mysql, Pdo\Pgsql or Pdo\Sqlite, which carry the driver-specific methods: Pdo\Pgsql::escapeIdentifier(), copyFromArray() and getNotify(); Pdo\Sqlite::createFunction(), createCollation() and setAuthorizer(). new PDO(...) still works but returns a plain PDO. The driver-specific constants have moved too (Pdo\Mysql::ATTR_INIT_COMMAND instead of PDO::MYSQL_ATTR_INIT_COMMAND, which is deprecated since 8.5).
errmode=2 is PDO::ERRMODE_EXCEPTION, the default since PHP 8.0: every failed query throws PDOException. Before 8.0 the default was silent, and every query had to be checked.
MySQL emulates prepared statements by default (emulate=true): PDO interpolates the escaped values into the SQL itself and sends one query, instead of a real server-side prepare. It is safe when the connection charset is set in the DSN, but it changes how some parameters behave (see LIMIT below). PostgreSQL uses real prepared statements.

For MySQL and MariaDB, always put charset=utf8mb4 in the DSN. MySQL’s utf8 is a 3-byte subset that cannot store emoji and some CJK characters; utf8mb4 is real UTF-8 (the title The Fifth Season 📚 below round-trips intact). Credentials come from the environment (getenv()), never from the source code or the repository.

Prepared statements and SQL injection

SQL injection happens when input is pasted into SQL text, so that the input can change the query’s structure. The fix is to send the SQL and the values separately: a prepared statement with placeholders, positional (?) or named (:title), and values passed to execute() or bound with bindValue().

<?php
declare(strict_types=1);

$pdo = PDO::connect('mysql:host=mysql;dbname=bookshelf;charset=utf8mb4', getenv('DB_USER'), getenv('DB_PASSWORD'),
    [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);

$input = "x' OR '1'='1";                                     // from a search form

// WRONG: the input becomes part of the SQL
echo count($pdo->query("SELECT * FROM books WHERE title = '$input'")->fetchAll()), " rows by concatenation\n";

// RIGHT: the input is only ever a value
$stmt = $pdo->prepare('SELECT * FROM books WHERE title = ?');
$stmt->execute([$input]);
echo count($stmt->fetchAll()), " rows with a prepared statement\n";

// Named placeholders, reused for many rows
$insert = $pdo->prepare('INSERT INTO books (author_id, title, published_year) VALUES (:author, :title, :year)');
$insert->execute(['author' => 3, 'title' => 'The Fifth Season 📚', 'year' => 2015]);

4 rows by concatenation
0 rows with a prepared statement

Source: PDO — Prepared statements and stored procedures and PHP Manual — SQL Injection

The concatenated query returned every book in the table: ' OR '1'='1 turned the condition into one that is always true. The prepared statement looked for a book titled, literally, x' OR '1'='1. Escaping functions (addslashes(), real_escape_string(), PDO::quote()) are not a substitute: they depend on the charset and the context, and one forgotten call is enough.

Placeholders stand for values only. Table and column names, ASC/DESC and SQL keywords cannot be bound; when they come from the user (a sort column), map the input to a fixed list, as the repository below does.

LIMIT and types under emulation

With MySQL’s emulated prepares, values passed to execute([...]) are bound as strings and quoted, which breaks LIMIT ?:

<?php
declare(strict_types=1);

try {
    $stmt = $pdo->prepare('SELECT title FROM books ORDER BY title LIMIT ?');
    $stmt->execute(['2']);
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";   // SQLSTATE[42000]: Syntax error or access violation: 1064 ... (LIMIT '2')
}

$stmt = $pdo->prepare('SELECT title FROM books ORDER BY title LIMIT :limit');
$stmt->bindValue('limit', 2, PDO::PARAM_INT);              // bound as an integer: works
$stmt->execute();
print_r($stmt->fetchAll(PDO::FETCH_COLUMN));               // [0] => Kindred  [1] => Parable of the Sower

var_dump($pdo->query('SELECT id, published_year FROM books LIMIT 1')->fetch());
// array(2) { ["id"]=> int(1) ["published_year"]=> int(1979) }

Source: PDOStatement::bindValue and PDO::ATTR_EMULATE_PREPARES

Bind numeric parameters with PDO::PARAM_INT, or turn emulation off (PDO::ATTR_EMULATE_PREPARES => false) to use real server-side prepares. Integer columns come back as PHP int with mysqlnd (the last dump); older guides that expect strings were written before PHP 8.1 changed that for emulated prepares.

Fetch modes

fetch() returns one row, fetchAll() all of them, fetchColumn() one column of the next row; the fetch mode decides the shape. PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC (set in the connection above) avoids the default FETCH_BOTH, which returns every column twice, by name and by number.

<?php
declare(strict_types=1);

print_r($pdo->query('SELECT id, title FROM books ORDER BY id')->fetchAll(PDO::FETCH_KEY_PAIR));
// [1] => Kindred  [2] => Parable of the Sower  [3] => The Dispossessed  [4] => The Fifth Season 📚

print_r($pdo->query('SELECT a.name, b.title FROM books b JOIN authors a ON a.id = b.author_id ORDER BY b.id')
    ->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN));
// [Octavia E. Butler] => [Kindred, Parable of the Sower]  [Ursula K. Le Guin] => [The Dispossessed]  ...

Source: PDOStatement::fetchAll and PDO constants (fetch modes)

PDO::FETCH_CLASS (and fetchObject()) creates objects of a class, assigning each column to the property of the same name before calling the constructor; FETCH_PROPS_LATE calls the constructor first. Neither works with readonly classes, the natural shape for row objects in modern PHP:

<?php
declare(strict_types=1);

final readonly class Book
{
    public function __construct(public int $id, public string $title, public ?int $published_year) {}
}

foreach ([PDO::FETCH_CLASS, PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE] as $mode) {
    try {
        $pdo->query('SELECT id, title, published_year FROM books LIMIT 1')->fetchAll($mode, Book::class, [0, '', null]);
    } catch (Error $e) {
        echo $e->getMessage(), "\n";
    }
}
// Cannot modify readonly property Book::$id
// Cannot modify readonly property Book::$published_year

// Works: fetch associative rows and construct with named arguments
$books = array_map(fn(array $row) => new Book(...$row),
    $pdo->query('SELECT id, title, published_year FROM books ORDER BY id LIMIT 2')->fetchAll(PDO::FETCH_ASSOC));
var_dump($books[0]);   // object(Book) { ["id"]=> int(1) ["title"]=> "Kindred" ["published_year"]=> int(1979) }

Source: PDOStatement::fetchObject and PDO::FETCH_PROPS_LATE

Spreading an associative row into a constructor (new Book(...$row)) maps columns to parameters by name, so the SQL column aliases must match the parameter names. FETCH_CLASS remains fine for classes with plain, writable properties.

Transactions

A transaction groups statements so that either all of them take effect or none does. beginTransaction(), commit() and rollBack() control it; in between, a failure must roll back and re-throw. The repository method below adds an author and their books atomically; the second title violates a CHECK constraint, and the author inserted a moment earlier disappears with the rollback:

<?php
declare(strict_types=1);

final class BookRepository
{
    // ... constructor, find(), page() and after() below ...

    /** Add an author and their books atomically: all rows or none. */
    public function addAuthorWithBooks(string $name, array $titles): int
    {
        $this->pdo->beginTransaction();
        try {
            $this->pdo->prepare('INSERT INTO authors (name) VALUES (?)')->execute([$name]);
            $authorId = (int) $this->pdo->lastInsertId();
            $insert = $this->pdo->prepare('INSERT INTO books (author_id, title) VALUES (?, ?)');
            foreach ($titles as $title) {
                $insert->execute([$authorId, $title]);
            }
            $this->pdo->commit();
            return $authorId;
        } catch (Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

PDOException: SQLSTATE[23000]: Integrity constraint violation: 19 CHECK constraint failed: title <> ''
int(0)          # SELECT COUNT(*) FROM authors WHERE name = 'N. K. Jemisin': rolled back
3               # the retry with valid titles commits and returns the new author's id

Source: PDO — Transactions and auto-commit and PDO::lastInsertId

Statements that implicitly commit (MySQL DDL such as CREATE TABLE) end a transaction early, and with MyISAM tables there are no transactions at all; use InnoDB. On PostgreSQL, get generated keys with INSERT ... RETURNING id, which returns the value in the same round trip ($stmt->fetchColumn() gave int(1) in the test run). Isolation levels, locking and deadlocks are covered in SQL Transactions.

A repository for Bookshelf Lite

Keeping SQL in one class per table (a repository) gives the rest of the application methods instead of queries, and one place to review for injection. Bookshelf Lite’s connection is configured from the environment, with SQLite as the development default:

<?php
// src/db.php
declare(strict_types=1);

/** One connection per request, configured from the environment. */
function db(): PDO
{
    static $pdo = null;
    return $pdo ??= PDO::connect(
        getenv('DB_DSN') ?: 'sqlite:' . __DIR__ . '/../var/bookshelf.sqlite',
        getenv('DB_USER') ?: null,
        getenv('DB_PASSWORD') ?: null,
        [PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
    );
}

<?php
// src/BookRepository.php
declare(strict_types=1);

final readonly class Book
{
    public function __construct(public int $id, public string $title, public string $author, public ?int $year) {}
}

final class BookRepository
{
    private const array SORTABLE = ['title' => 'b.title', 'year' => 'b.published_year', 'author' => 'a.name'];

    public function __construct(private readonly PDO $pdo) {}

    public function find(int $id): ?Book
    {
        $stmt = $this->pdo->prepare(
            'SELECT b.id, b.title, a.name AS author, b.published_year AS year
               FROM books b JOIN authors a ON a.id = b.author_id WHERE b.id = :id');
        $stmt->execute(['id' => $id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row === false ? null : new Book(...$row);
    }

    /** @return list<Book> */
    public function page(string $sort = 'title', int $page = 1, int $perPage = 10): array
    {
        // Identifiers cannot be bound as parameters: map user input to a fixed list
        $column = self::SORTABLE[$sort] ?? throw new InvalidArgumentException("Cannot sort by $sort");
        $stmt = $this->pdo->prepare(
            "SELECT b.id, b.title, a.name AS author, b.published_year AS year
               FROM books b JOIN authors a ON a.id = b.author_id
              ORDER BY $column, b.id LIMIT :limit OFFSET :offset");
        $stmt->bindValue('limit', $perPage, PDO::PARAM_INT);
        $stmt->bindValue('offset', ($page - 1) * $perPage, PDO::PARAM_INT);
        $stmt->execute();
        return array_map(fn(array $row) => new Book(...$row), $stmt->fetchAll(PDO::FETCH_ASSOC));
    }

    /** Keyset ("seek") pagination: the next page starts after the last id seen. @return list<Book> */
    public function after(int $lastId, int $perPage = 10): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT b.id, b.title, a.name AS author, b.published_year AS year
               FROM books b JOIN authors a ON a.id = b.author_id
              WHERE b.id > :last ORDER BY b.id LIMIT :limit');
        $stmt->bindValue('last', $lastId, PDO::PARAM_INT);
        $stmt->bindValue('limit', $perPage, PDO::PARAM_INT);
        $stmt->execute();
        return array_map(fn(array $row) => new Book(...$row), $stmt->fetchAll(PDO::FETCH_ASSOC));
    }

    // addAuthorWithBooks(): see "Transactions" above
}

<?php
declare(strict_types=1);

$books = new BookRepository(db());

var_dump($books->find(1));
// object(Book) { ["id"]=> 1 ["title"]=> "Kindred" ["author"]=> "Octavia E. Butler" ["year"]=> 1979 }
echo implode(', ', array_map(fn(Book $b) => "$b->year $b->title", $books->page(sort: 'year', page: 1, perPage: 3))), "\n";
// 1969 The Left Hand of Darkness, 1974 The Dispossessed, 1979 Kindred
try {
    $books->page(sort: 'title; DROP TABLE books');
} catch (InvalidArgumentException $e) {
    echo $e->getMessage(), "\n";      // Cannot sort by title; DROP TABLE books
}
echo implode(', ', array_map(fn(Book $b) => "#$b->id", $books->after(lastId: 2, perPage: 2))), "\n";   // #3, #4

Source: PDO::prepare, PDOStatement::execute and PDOStatement::fetch

The two pagination methods differ in cost. LIMIT … OFFSET … makes the database read and discard every skipped row, so page 5,000 is slow, and rows inserted between requests shift pages. Keyset pagination (WHERE id > :last ORDER BY id LIMIT …) uses the index to jump straight to the next page and stays stable, at the cost of no "jump to page N". Both are compared in depth on Pagination Strategies.

mysqli

mysqli is the MySQL-only extension. It exposes MySQL features that PDO does not (multiple statements, asynchronous queries, mysqli_stmt::get_result() metadata), but has no named placeholders and ties code to one database. Since PHP 8.1 it throws mysqli_sql_exception by default, accepts parameters in execute(), and since 8.2 execute_query() prepares, binds and runs in one call:

<?php
declare(strict_types=1);

$db = new mysqli('mysql', getenv('DB_USER'), getenv('DB_PASSWORD'), 'bookshelf');
$db->set_charset('utf8mb4');

$result = $db->execute_query('SELECT title FROM books WHERE published_year > ? ORDER BY title', [1980]);  // 8.2
foreach ($result as $row) {
    echo $row['title'], "\n";          // Parable of the Sower, The Fifth Season 📚
}

$stmt = $db->prepare('SELECT COUNT(*) FROM books WHERE author_id = ?');
$stmt->execute([1]);                   // 8.1: parameters passed to execute()
var_dump($stmt->get_result()->fetch_row()[0]);   // int(2)

try {
    $db->query('SELECT * FROM no_such_table');
} catch (mysqli_sql_exception $e) {
    echo $e->getMessage(), "\n";       // Table 'bookshelf.no_such_table' doesn't exist
}

Source: mysqli::execute_query, mysqli — Prepared statements and PHP Manual — MySQLi

Use PDO unless a MySQL-specific feature is needed: the code moves between databases, and frameworks (Laravel included) use it underneath. The mysqli result methods fetch_assoc(), fetch_row(), fetch_object() and fetch_all() correspond to PDO’s fetch modes, and $db->insert_id to PDO::lastInsertId().

A few database-side habits belong with either extension. The application’s database user should have only the privileges it needs (GRANT SELECT, INSERT, UPDATE, DELETE on its own schema, with REVOKE for anything else), and migrations can run as a separate user that may change the schema. Table and column metadata comes from the standard INFORMATION_SCHEMA views in MySQL, MariaDB and PostgreSQL, which Laravel’s Schema::getColumns() also reads. LOAD DATA LOCAL INFILE lets the server read a file from the client, so PHP disables it by default (mysqli.allow_local_infile=0, and the PDO MySQL local-infile attribute is false); enable it only for a trusted import job. Formatting dates in SQL with MySQL’s DATE_FORMAT() ties the query to one database; selecting the column and formatting it with DateTimeImmutable::format() keeps the query portable.

Where older books and tutorials are outdated

mysql_connect() and the mysql_* functions (Wiley ch. 8) were removed in PHP 7.0; use PDO or mysqli.
String interpolation after real_escape_string() or addslashes() (Nixon ch. 10 and 27, Wiley ch. 8) is an injection risk; use prepared statements for every value. htmlentities() as SQL "scrubbing" (Kromann ch. 21) protects nothing.
PDO’s default error mode: Kromann ch. 28 states that PDO throws exceptions by default, which was not true for PHP 7 (the default was silent); it has been true since PHP 8.0.
Checking connect_error and every query result with mysqli (Powers ch. 13, Kromann ch. 27): mysqli throws by default since PHP 8.1.
utf8 as the MySQL charset, still common in tutorials, cannot store emoji; use utf8mb4 in the DSN and the table definitions, as Powers ch. 12 recommends.
new PDO with PDO::MYSQL_ATTR_* constants still works, but PHP 8.4 added PDO::connect() and the driver subclasses, and 8.5 deprecated the old driver constants on PDO in favour of Pdo\Mysql::ATTR_*.