Advanced PDO Techniques: Prepared Statements, Fetch Modes, and Transactions

1. Why PDO remains the right choice over mysqli

PDO (PHP Data Objects) has been the database-agnostic abstraction layer since PHP 5.1, offering the same object-oriented API style for MySQL, PostgreSQL, SQLite, and other drivers. The main advantage over mysqli is not raw performance, which barely differs in benchmarks, but consistency: once you master PDO, the same code runs against a different database with minimal adjustments. For projects that run on MySQL today and might switch to PostgreSQL tomorrow, that is a genuine architectural benefit.

The second reason to choose PDO is its consistent support for prepared statements across all drivers, including named placeholders, which mysqli does not offer in this form. This article is deliberately not about the basics of PDO, but about the techniques that separate a tutorial example from robust production code: correct attribute configuration, deliberate fetch strategies, transaction boundaries, and avoiding the typical performance traps that appear with large datasets.

2. Configuring the PDO connection correctly

A PDO connection is often set up with default values that are not the right ones in practice. The most important attribute is PDO::ATTR_ERRMODE, which should be set to PDO::ERRMODE_EXCEPTION instead of the default PDO::ERRMODE_SILENT. Without this setting, PDO simply returns false on an error, which is almost always overlooked in real code and leads to silent data loss. Equally important is PDO::ATTR_DEFAULT_FETCH_MODE, which sets the fetch mode project wide instead of specifying it on every call individually.

The DSN string (Data Source Name) should always include the character set explicitly, for example charset=utf8mb4 for MySQL. If the character set is not set in the DSN but only afterward via SET NAMES, a short window opens where the connection operates with the wrong character set, which can become relevant in certain attack scenarios. A clean PDO configuration bundles all these settings in one central place, ideally in a dedicated factory class or a dependency injection container.

<?php

declare(strict_types=1);

final class DatabaseConnectionFactory
{
    public function __construct(
        private readonly string $host,
        private readonly string $database,
        private readonly string $username,
        private readonly string $password,
    ) {
    }

    public function create(): PDO
    {
        // Explicit charset in DSN avoids a window where the connection
        // temporarily uses the wrong character set
        $dsn = sprintf(
            'mysql:host=%s;dbname=%s;charset=utf8mb4',
            $this->host,
            $this->database,
        );

        return new PDO($dsn, $this->username, $this->password, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::ATTR_STRINGIFY_FETCHES => false,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = '+00:00'",
        ]);
    }
}

3. Prepared statements: named vs. positional placeholders

Prepared statements are the reason PDO should be used for every query that touches user input. Instead of inserting values directly into the SQL string, PDO first sends the structure of the query to the database server and only afterward passes the values separately. The server can therefore never interpret a value as SQL code, which makes SQL injection structurally impossible regardless of what the value contains.

PDO supports two placeholder styles: positional (?) and named (:name). Named placeholders are considerably more readable and more robust against ordering mistakes in queries with many parameters, since the mapping happens by name rather than by position. One important detail: the same named placeholder cannot be bound twice with different values in the same PDO query, only repeating it under a different name or switching to positional placeholders solves that.

<?php

declare(strict_types=1);

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

    // Named placeholders keep readability high with many parameters
    public function findActiveByRole(string $role, int $minAge): array
    {
        $statement = $this->pdo->prepare(
            'SELECT id, email, role, age FROM users
             WHERE role = :role AND age >= :min_age AND is_active = 1
             ORDER BY created_at DESC'
        );

        $statement->bindValue(':role', $role, PDO::PARAM_STR);
        $statement->bindValue(':min_age', $minAge, PDO::PARAM_INT);
        $statement->execute();

        return $statement->fetchAll();
    }

    // bindParam binds by reference, useful in loops with a mutable variable
    public function insertBatch(array $emails): void
    {
        $statement = $this->pdo->prepare(
            'INSERT INTO newsletter_subscribers (email) VALUES (:email)'
        );

        $email = '';
        $statement->bindParam(':email', $email, PDO::PARAM_STR);

        foreach ($emails as $email) {
            $statement->execute();
        }
    }
}

4. Fetch modes in depth

PDO offers far more fetch modes than the usual FETCH_ASSOC and FETCH_OBJ. PDO::FETCH_CLASS instantiates a custom class directly for each row and assigns column values to constructor properties before the constructor is even called, unless PDO::FETCH_PROPS_LATE is set. That is a common pitfall: without FETCH_PROPS_LATE, PDO overwrites properties the constructor already set with the raw database values again.

PDO::FETCH_LAZY only loads values on actual access and suits very wide tables where not every column is needed in every use case. PDO::FETCH_COLUMN extracts a single column across all rows, for example to get a list of IDs without a detour through array_column(). PDO::FETCH_KEY_PAIR builds an associative array directly from two selected columns, which saves considerable code for lookup tables.

<?php

declare(strict_types=1);

final readonly class ProductStock
{
    public function __construct(
        public string $sku,
        public int $quantity,
        public string $warehouse,
    ) {
    }
}

$statement = $pdo->query('SELECT sku, quantity, warehouse FROM stock');

// FETCH_CLASS instantiates ProductStock per row via constructor arguments
$stocks = $statement->fetchAll(PDO::FETCH_CLASS, ProductStock::class);

// FETCH_COLUMN pulls a single column across all rows, no array_column needed
$skuList = $pdo->query('SELECT sku FROM stock')->fetchAll(PDO::FETCH_COLUMN);

// FETCH_KEY_PAIR builds an associative array from two selected columns
$skuToWarehouse = $pdo
    ->query('SELECT sku, warehouse FROM stock')
    ->fetchAll(PDO::FETCH_KEY_PAIR);

// FETCH_LAZY defers column access, useful for very wide tables
$row = $pdo->query('SELECT * FROM stock LIMIT 1')->fetch(PDO::FETCH_LAZY);
echo $row->sku; // only this column is actually resolved

5. Transactions and savepoints

Transactions with PDO start with beginTransaction() and end either with commit() or rollBack(). The most common mistake is drawing the transaction boundary too wide, so that slow external calls end up inside the transaction and database locks are held unnecessarily long. The transaction boundary should always be as tight as possible around the actual write operations, never around HTTP calls or filesystem operations.

PDO does not natively support nested transactions, but MySQL itself knows savepoints, which can be addressed through PDO::exec(). That allows a part of a transaction to be rolled back deliberately without abandoning the entire outer transaction. This is particularly useful in library code that may be called from several places within an already running transaction and should not need to know whether it is the outermost level.

<?php

declare(strict_types=1);

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

    public function placeOrder(int $customerId, array $items): int
    {
        $this->pdo->beginTransaction();

        try {
            $orderId = $this->insertOrder($customerId);

            foreach ($items as $item) {
                $this->reserveStock($item['sku'], $item['quantity']);
                $this->insertOrderLine($orderId, $item);
            }

            $this->pdo->commit();

            return $orderId;
        } catch (Throwable $exception) {
            $this->pdo->rollBack();

            throw new RuntimeException(
                'Order could not be placed: ' . $exception->getMessage(),
                previous: $exception,
            );
        }
    }

    // Savepoint isolates a single stock reservation inside the outer transaction
    private function reserveStock(string $sku, int $quantity): void
    {
        $this->pdo->exec('SAVEPOINT stock_reservation');

        $statement = $this->pdo->prepare(
            'UPDATE stock SET quantity = quantity - :qty
             WHERE sku = :sku AND quantity >= :qty'
        );
        $statement->execute(['qty' => $quantity, 'sku' => $sku]);

        if ($statement->rowCount() === 0) {
            $this->pdo->exec('ROLLBACK TO SAVEPOINT stock_reservation');

            throw new RuntimeException("Insufficient stock for {$sku}");
        }
    }
}

6. Bulk inserts and performance optimization

A common performance mistake with PDO is inserting thousands of rows in a loop with a single INSERT statement each time. Every execution produces a roundtrip to the database server, which for ten thousand rows means ten thousand network roundtrips. A multi row insert, which combines several value tuples in a single INSERT statement, is considerably faster, limited only by the server's maximum packet size.

For very large datasets, combining prepared statement reuse with an explicit batch size, typically 500 to 1000 rows per batch, inside a shared transaction pays off. That reduces both the number of roundtrips and the transaction overhead without a single transaction growing so large it holds locks for minutes. For MySQL, LOAD DATA INFILE is an additional option that works outside of PDO through a CSV file and remains the fastest variant for millions of rows.

<?php

declare(strict_types=1);

final readonly class BulkInsertHelper
{
    public function __construct(private PDO $pdo, private int $batchSize = 500)
    {
    }

    // Multi-row INSERT drastically reduces network roundtrips
    public function insertRows(string $table, array $columns, array $rows): void
    {
        foreach (array_chunk($rows, $this->batchSize) as $batch) {
            $this->insertChunk($table, $columns, $batch);
        }
    }

    private function insertChunk(string $table, array $columns, array $batch): void
    {
        $columnList = implode(', ', $columns);
        $singleRowPlaceholder = '(' . implode(', ', array_fill(0, count($columns), '?')) . ')';
        $allPlaceholders = implode(', ', array_fill(0, count($batch), $singleRowPlaceholder));

        $statement = $this->pdo->prepare(
            "INSERT INTO {$table} ({$columnList}) VALUES {$allPlaceholders}"
        );

        $flatValues = array_merge(...$batch);
        $statement->execute($flatValues);
    }
}

7. Error handling with PDOException

With PDO::ERRMODE_EXCEPTION enabled, PDO throws a PDOException on every error, containing the native SQLSTATE code and the database specific error message. The SQLSTATE code is standardized across different databases, for example 23000 for integrity violations such as duplicate key errors, while the driver specific error code in errorInfo()[1] varies by database.

Robust error handling distinguishes between expected errors like unique constraint violations, which should be handled domain wise, and unexpected errors like connection drops, which should be propagated as technical failures. A common anti pattern is catching PDOException broadly and converting it into a generic error message without inspecting the SQLSTATE code, which makes domain and technical errors indistinguishable within the application.

<?php

declare(strict_types=1);

final readonly class UserRegistrationService
{
    private const string DUPLICATE_ENTRY_SQLSTATE = '23000';

    public function __construct(private PDO $pdo)
    {
    }

    public function register(string $email, string $passwordHash): void
    {
        $statement = $this->pdo->prepare(
            'INSERT INTO users (email, password_hash) VALUES (:email, :hash)'
        );

        try {
            $statement->execute(['email' => $email, 'hash' => $passwordHash]);
        } catch (PDOException $exception) {
            if ($exception->getCode() === self::DUPLICATE_ENTRY_SQLSTATE) {
                throw new EmailAlreadyRegisteredException($email, previous: $exception);
            }

            // Unexpected technical failure, re-throw for upstream handling
            throw $exception;
        }
    }
}

8. Emulated prepares and statement caching

PDO supports two modes for prepared statements: real server side prepares and emulated prepares, where PDO itself builds the values into the SQL string before sending it to the server. The historical default for MySQL is true for emulated prepares, which should be disabled in almost every case by setting PDO::ATTR_EMULATE_PREPARES to false. Real prepares use the native binary protocol, are more robust against certain injection variants, and return correct type information to the driver.

One downside of real prepares: the server has to re-prepare the query on every new connection, which adds overhead for very short lived connections. In long running processes with persistent connections, for example under Swoole or RoadRunner, this overhead amortizes quickly because the same prepared statement reference can be reused across many requests. Application level statement caching, where already prepared PDOStatement objects are stored in an array keyed by SQL hash, saves additional time in such environments.

9. PDO in direct comparison

The choice between the different ways of accessing a database in PHP has concrete consequences for security, portability, and maintainability. The following table compares PDO against the alternatives most commonly found in existing projects.

In practice, PDO remains the most pragmatic choice for projects that want full control over SQL while still expecting a clean, cross-driver API. A query builder or ORM often builds internally on top of PDO itself, so understanding PDO stays immediately useful even when working with higher abstraction levels.

10. Summary

Advanced PDO usage means going beyond query() and fetch() and applying the tools PDO provides for production code: correctly configured connection attributes, deliberate fetch modes instead of blanket FETCH_ASSOC, tightly scoped transaction boundaries with savepoints for sub-operations, and bulk inserts that minimize network roundtrips. Disabling PDO::ATTR_EMULATE_PREPARES and handling errors by differentiating SQLSTATE codes avoids the most common pitfalls in database access.

The biggest lever is not reinventing these techniques ad hoc in every repository class, but bundling them in a central data access layer. A factory for the connection, a helper for bulk operations, and unified error handling ensure that PDO is used consistently and safely throughout the project, regardless of which developer writes an individual query.

11. FAQ: Advanced PDO Techniques