PHP and MySQL with PDO

PDO gives plain PHP one connection and statement interface for database work. With MySQL, it keeps the SQL template separate from request values through prepared statements.

Picture PDO as the dispatch desk between the application and database. The SQL template is the delivery form, bound values are the sealed contents, and the PDO MySQL driver carries both to MySQL without pasting the contents into the form.

The Safe Shape

<?php

$pdo = new PDO(
    "mysql:host=127.0.0.1;dbname=app;charset=utf8mb4",
    $_ENV["DB_USER"],
    $_ENV["DB_PASS"],
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ],
);

$statement = $pdo->prepare(
    "SELECT id, email, created_at
     FROM users
     WHERE email = :email
     LIMIT 1",
);

$statement->execute([
    "email" => $email,
]);

$user = $statement->fetch();

The SQL template and the values are separate. PHP's PDO documentation is explicit here: parameter markers are for values, and prepared statements help prevent SQL injection by removing the need to manually quote and escape those values.

The connection attributes define the dispatch rules. PDO::ERRMODE_EXCEPTION makes query failures visible instead of letting the page continue with a false result. PDO::FETCH_ASSOC returns rows keyed by column name, and PDO::ATTR_EMULATE_PREPARES set to false asks the MySQL driver to use native prepared statements. The charset=utf8mb4 fragment decides character handling at connection time.

PHP 8 defaults PDO to exception mode, but setting it explicitly still pays for itself. It documents the expectation for the next maintainer, keeps older deployments from behaving differently, and gives tests a clean failure when a query or table name is wrong. Silent database errors were tolerable in throwaway scripts. In a database-backed app, they turn one mistake into a second mistake because the page keeps making decisions with missing data.

That last phrase has a boundary: parameters represent data literals, not table names, column names, keywords, or arbitrary SQL fragments. If the user can choose a sort column, use an allowlist in PHP, then insert the already-approved column name.

<?php

$allowedSorts = [
    "created" => "created_at",
    "email" => "email",
];

$sort = $allowedSorts[$_GET["sort"] ?? "created"] ?? "created_at";

$statement = $pdo->prepare(
    "SELECT id, email, created_at
     FROM users
     ORDER BY {$sort} DESC
     LIMIT 25",
);

$statement->execute();

The variable there is safe only because it came from your own map, not directly from the request.

The same rule applies to table names, directions, and optional filter fragments. ASC or DESC should come from a small map. A table switch should come from application logic, not $_GET["table"]. If the query shape changes based on user input, the part you insert into the SQL string needs to be something your code selected from known values. Prepared statements protect values; they do not turn request text into trusted SQL grammar.

When the SQL needs an integer, bind it as an integer. MySQL will often coerce strings for you, but that habit hides mistakes at the exact boundary where you want the code to be dull and explicit.

<?php

$statement = $pdo->prepare(
    "SELECT id, email, created_at
     FROM users
     ORDER BY created_at DESC
     LIMIT :limit OFFSET :offset",
);

$statement->bindValue("limit", 25, PDO::PARAM_INT);
$statement->bindValue("offset", 50, PDO::PARAM_INT);
$statement->execute();

$users = $statement->fetchAll();

MySQL reports changed rows for an UPDATE by default. Setting a column to its current value can therefore make rowCount() return zero even when the WHERE clause matched a row. Zero alone cannot distinguish an idempotent save from a missing record.

Existing PDO MySQL code can request matched-row behavior with PDO::MYSQL_ATTR_FOUND_ROWS. On PHP 8.4 and newer, the dedicated Pdo\Mysql subclass provides the same setting as Pdo\Mysql::ATTR_FOUND_ROWS. Choose that connection behavior deliberately because it changes what update counts mean across the application.

<?php

$statement = $pdo->prepare(
    "UPDATE users
     SET display_name = :display_name
     WHERE id = :id",
);

$statement->bindValue("display_name", $displayName);
$statement->bindValue("id", $userId, PDO::PARAM_INT);
$statement->execute();

if ($statement->rowCount() > 1) {
    throw new RuntimeException("Expected at most one user row to change.");
}

This check accepts zero changed rows, so saving an unchanged display name does not throw. If the application must distinguish an unchanged row from a missing user, enable matched-row behavior for the connection or check existence separately.

Use One Connection Boundary

Put connection setup in one place instead of copying the DSN and attributes into every page. Older PHP applications often scattered connection calls across files, which made a hostname, credential, or error-mode change easy to miss.

<?php

function db(): PDO
{
    static $pdo = null;

    if ($pdo instanceof PDO) {
        return $pdo;
    }

    $pdo = new PDO(
        "mysql:host=127.0.0.1;dbname=app;charset=utf8mb4",
        $_ENV["DB_USER"],
        $_ENV["DB_PASS"],
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ],
    );

    return $pdo;
}

This is not an argument against frameworks. Laravel and Symfony wrap this work in their own layers. The point is that the underlying boundary still matters. When something fails, you want to know where the connection is made, how errors surface, and which code owns the query.

Keep credentials out of the PHP file for the same reason. A local .env loader, server-level environment variables, or your host's secret store are all better than committing a username and password beside the code that uses them. The PDO example reads $_ENV because the connection helper should know how to connect, while the source file should not become the place where production secrets live forever.

Fetch the Shape You Need

Use fetch() when the query should return one row, fetchAll() when the result set is intentionally small, and iteration when the result might grow. A lookup by email, slug, or primary key should read like a single-row operation. A dashboard table can usually fetch a page of rows. A report export should not pull every matching row into memory before writing the first line.

<?php

$statement = db()->prepare(
    "SELECT id, email, created_at
     FROM users
     WHERE active = 1
     ORDER BY created_at DESC
     LIMIT :limit",
);

$statement->bindValue("limit", 100, PDO::PARAM_INT);
$statement->execute();

foreach ($statement as $user) {
    echo htmlspecialchars($user["email"], ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8");
}

That loop is not glamorous, which is why it is useful. It keeps the page from pretending the result size is always small, and it makes the database read path obvious during review.

There is another small habit worth keeping: handle the "no row" case where it happens. fetch() returns false when there are no more rows, so a lookup should branch before the template assumes an array exists.

<?php

$statement = db()->prepare(
    "SELECT id, email, created_at
     FROM users
     WHERE id = :id
     LIMIT 1",
);

$statement->bindValue("id", $userId, PDO::PARAM_INT);
$statement->execute();

$user = $statement->fetch();

if ($user === false) {
    http_response_code(404);
    exit("User not found");
}

That branch looks pedestrian, but it is the difference between an intentional 404 and a notice-filled page that leaks implementation detail.

Fetch Less than You Think

The old PHP habit was SELECT * because it was fast to type, but that creates a quiet tax. The query returns columns the page does not need, the template gets coupled to table shape, and later schema changes become riskier.

Prefer naming the columns the page actually renders:

SELECT id, email, created_at
FROM users
WHERE active = 1
ORDER BY created_at DESC
LIMIT 25;

The loose version below is easier to type and harder to maintain:

SELECT *
FROM users;

When the query starts joining tables, explicit columns become even more important. The SQL joins guide covers the join side of that problem.

Handle Writes with Transactions

If one user action writes to more than one table, wrap those writes in a transaction.

<?php

$pdo = db();
$pdo->beginTransaction();

try {
    $order = $pdo->prepare(
        "INSERT INTO orders (user_id, total_cents)
         VALUES (:user_id, :total_cents)",
    );
    $order->execute([
        "user_id" => $userId,
        "total_cents" => $totalCents,
    ]);

    $orderId = (int) $pdo->lastInsertId();

    $item = $pdo->prepare(
        "INSERT INTO order_items (order_id, sku, quantity)
         VALUES (:order_id, :sku, :quantity)",
    );
    $item->execute([
        "order_id" => $orderId,
        "sku" => $sku,
        "quantity" => $quantity,
    ]);

    $pdo->commit();
} catch (Throwable $error) {
    $pdo->rollBack();
    throw $error;
}

The database should not be left with an order but no order item because PHP hit an exception halfway through. Read SQL transactions and ACID before you build anything that moves money, inventory, permissions, or account state.

Payment flows are only the obvious transaction example. Use the same pattern anywhere one user action has to leave the database in one coherent state: creating an account and profile, recording a moderation decision and audit row, or moving inventory between two locations. If the second write failing would make the first write misleading, wrap the pair.

Do not let the transaction block become a hiding place for unrelated work. Validate the request before beginTransaction(), keep slow remote calls outside the transaction when you can, and commit as soon as the related database writes have succeeded.

Also keep schema changes out of normal transaction examples. The PHP manual calls out that databases such as MySQL can implicitly commit around DDL statements like CREATE TABLE or DROP TABLE. That is a deployment and migration concern, not the same thing as wrapping an order insert and its order items. Application transactions should mostly cover data changes that belong to one user action.

Common Pitfalls & Debugging

Emulated and Native Prepares Behave Differently

Symptom: invalid SQL is not reported until execute(), or placeholder parsing changes after deployment. Cause: PDO MySQL uses emulated prepares by default, so PDO parses the statement instead of asking MySQL to prepare it. Fix: set PDO::ATTR_EMULATE_PREPARES deliberately, test the chosen mode, and use the placeholder style supported by the driver.

The Error Mode Hides the Failure

Symptom: a failed query returns false and later code reports a misleading secondary error. Cause: the connection uses silent error handling on an older deployment or overrides the PHP 8 default. Fix: set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION in the connection options and catch exceptions only where the application can respond meaningfully.

Validation Still Happens First

Prepared statements protect the SQL value boundary. They do not decide whether an email is valid, whether a price is allowed to be negative, or whether the current user may update the row. Validate shape and authorization before the query, use prepared statements at the database boundary, and escape output when the row comes back into HTML.

For form-heavy pages, pair this guide with form validation in PHP and PHP security fundamentals. PDO is one layer in the stack, not the whole defense.

Frequently Asked Questions

What is the difference between PDO exec and query?

exec runs a statement and returns the number of rows it affected, which suits INSERT, UPDATE, and DELETE. query runs a statement that returns rows and hands back a PDOStatement to iterate. Neither takes bound parameters, so both need trusted input.

Do prepared statements make PHP queries faster?

Prepared statements can reduce repeated parsing when the same SQL template runs many times, but they are not a guaranteed speed improvement for one query. Their main benefit is keeping values separate from SQL. Measure repeated workloads instead of choosing them as a blanket optimization.

What does FETCH_ASSOC change compared with the default?

PDO's default is FETCH_BOTH, which returns every column twice, once keyed by name and once by position. FETCH_ASSOC returns each row keyed by column name only, roughly halving the memory a fetched row occupies.

Does closing a connection roll back an open transaction?

Yes. If a script ends or the PDO object is destroyed with a transaction still open, the connection closes and uncommitted work is rolled back, so an unhandled early exit does not leave a partial write applied.

Can you bind an array of values to a single PDO placeholder for an IN clause?

No. PDO has no native array binding for one placeholder, so an IN clause needs one placeholder generated per value in the array, built dynamically to match the array's count before the statement is prepared.

Does PDO automatically retry a query if the database connection drops mid-request?

No. A dropped connection surfaces as a PDOException on the next statement PDO runs. The application has to catch it and decide whether to reconnect and retry, and only for a read that is genuinely safe to repeat.

Is PDO quote a safe substitute for a prepared statement?

No. quote escapes a value for direct inclusion in an SQL string, but skipping it once or using it inconsistently reintroduces the exact injection risk prepared statements remove. It has a narrow legitimate use for values, never for identifiers.

Self-Check

1. Which value can a PDO parameter represent: an email address, a column name, or the keyword DESC?
2. What column does the allowlist example select when $_GET["sort"] contains an unknown key?
3. What can a MySQL rowCount() result of zero mean after an idempotent update?
4. What response does the single-user lookup produce when fetch() returns false?
5. Where should request validation happen: before or after beginTransaction()?

Answers

1. An email address. Parameters represent complete data values, while identifiers and keywords need an application allowlist.
2. created_at. The second fallback selects the known default when the requested key is absent from the map.
3. The row was unchanged or absent. Default MySQL changed-row counts cannot distinguish those outcomes.
4. HTTP 404 with User not found. The branch sets the status before stopping the script.
5. Before the transaction. Validation should finish before the database begins holding transactional resources.

Next Steps

PDO does not make poor SQL correct. It keeps the dispatch boundary clear while the schema, indexes, joins, validation, and transaction rules do their own jobs.

Continue with SQL joins for multi-table reads and SQL transactions and ACID for related writes. The PHP and MySQL dynamic-site guide puts the connection, query, and HTML output into one small application.