PHP PDO Guide: MySQL Queries and Prepared Statements

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.

PDO uses a database-specific driver, so MySQL requires pdo_mysql. The examples assume an existing app database, the shown tables and columns, and credentials loaded into $_ENV. PDO shares an API across databases; it does not make MySQL-specific SQL portable.

How Do You Connect PHP to MySQL with PDO?

Create a PDO connection with a MySQL DSN, credentials, and connection options. Then prepare the SQL and pass the input value to execute(). Here, $email is an already validated email address.

<?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 options control errors, returned rows, and how statements are prepared. 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.

PDO parameters represent complete values, 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",
];

$requestedSort = $_GET["sort"] ?? "created";
$sort = is_string($requestedSort)
    ? ($allowedSorts[$requestedSort] ?? "created_at")
    : "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.

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.

When the SQL needs an integer, bind it as an integer. MySQL may convert numeric strings, but explicit integer binding makes parameters such as LIMIT unambiguous.

<?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.

Reuse the PDO Connection

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;
}

Laravel and Symfony wrap this work in their own layers, and 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.

How Do You Fetch Rows with PDO?

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");
}

Iteration avoids building another array with fetchAll(), but PDO MySQL buffers results by default. For large exports, use pagination or an unbuffered query. An unbuffered result must be consumed or closed before another query uses that connection.

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 turns a missing row into an intentional 404 instead of a notice-filled page that leaks implementation detail.

Select Only the Columns You Need

SELECT * returns every column, including data the page may not need. Naming columns reduces unnecessary transfer and makes the expected result easier to see.

Prefer naming the columns the page 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.

When Should PDO Use a Transaction?

PDO should use a transaction when related writes must succeed or fail together. With MySQL, use transactional tables such as InnoDB.

<?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) {
    if ($pdo->inTransaction()) {
        $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, separate from 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.

Do PDO Prepared Statements Replace Validation?

PDO prepared statements do not replace validation or permission checks. Check the email format, allowed price range, and the user’s permission before running the query. Escape returned values when displaying them in HTML.

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

Frequently Asked Questions

What is the difference between PDO exec and query?

PDO exec() returns an affected-row count, while PDO query() returns a PDOStatement from which you can fetch results. Neither method accepts bound parameters. Use prepare() and execute() when the query contains input values.

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 PDO FETCH_ASSOC change compared with FETCH_BOTH?

PDO FETCH_ASSOC returns each row with column names as keys. The default FETCH_BOTH also includes numeric keys. FETCH_ASSOC avoids those extra array entries, but it does not guarantee a specific memory saving.

Does closing a PDO connection roll back an open transaction?

PDO automatically rolls back an outstanding transaction started with beginTransaction() when the script ends or the connection closes. This requires transactional tables and cannot undo changes already committed, including MySQL implicit commits around some schema statements.

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

PDO cannot bind an array to one IN-clause placeholder. Generate one placeholder per value and pass the values separately to execute(). Handle an empty array before building the query.

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

PDO does not automatically retry a failed query. With exception mode enabled, the application must catch the connection error and decide whether retrying is safe. A lost response does not prove that a write failed, so retrying it can create duplicate records.

Is PDO quote a safe substitute for a prepared statement?

PDO quote() can quote a string value for a supported driver, but prepared statements are easier to use consistently. quote() cannot safely substitute table names, column names, or arbitrary SQL fragments.

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.

Build a Small Database-Backed Page

PDO handles connections and queries. You still need appropriate tables, indexes, validation, and transaction rules.

The PHP and MySQL dynamic-site guide puts the connection, query, and HTML output into one small application.