PHP Introduction to PDO

PDO (PHP Data Objects) is a database access layer built into PHP that gives you a single, consistent interface for talking to many different database systems — MySQL, PostgreSQL, SQLite, SQL Server, and more. Instead of learning a different set of functions for each database driver, you learn PDO once and can swap the underlying database with minimal code changes. It also makes it easy to write secure, injection-proof queries using prepared statements. This lesson covers everything you need to start using PDO confidently: connecting, querying, binding parameters, handling errors, and using transactions.

Overview / How PDO Works

PDO is not a database driver itself — it is an abstraction layer. Underneath it sits a specific PDO driver, such as pdo_mysql, pdo_pgsql, or pdo_sqlite, which must be enabled in your php.ini. When you create a new PDO(...) instance, the Zend engine loads the appropriate driver based on the connection string (called a DSN, or Data Source Name) and opens a connection to the database server. From that point on, every method you call on the PDO object — query(), prepare(), exec() — is translated by the driver into the database’s native protocol.

PDO gives you two closely related classes to work with:

PDO — represents the connection itself. You use it to open a connection, run simple queries, start transactions, and prepare statements.
PDOStatement — represents a prepared or executed SQL statement. You use it to bind parameters, execute the query, and fetch the resulting rows.

The biggest practical benefit of PDO over the older mysqli or long-removed mysql_* functions is prepared statements with bound parameters. Instead of building a SQL string by concatenating user input directly into it (which opens the door to SQL injection), you write a query template with placeholders, then hand PDO the actual values separately. PDO (or the database driver itself) sends the query and the values as distinct pieces, so user input can never be interpreted as SQL syntax.

Enabling PDO

Most PHP installations ship with PDO and the MySQL driver enabled by default. You can confirm this by checking phpinfo() or running php -m from the command line and looking for PDO and pdo_mysql in the list of loaded extensions.

Syntax

A PDO connection is created by instantiating the PDO class with a DSN, credentials, and optional driver options:

new PDO(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null): PDO

Part	Description
`$dsn`	The Data Source Name — identifies the driver, host, port, database name, and character set, e.g. `"mysql:host=localhost;dbname=school;charset=utf8mb4"`.
`$username`	The database user to authenticate as.
`$password`	The password for that user.
`$options`	An associative array of driver attributes, such as the error-reporting mode or the default fetch mode.

Once connected, the two most common attributes to set are:

Constant	Purpose
`PDO::ATTR_ERRMODE`	Controls how PDO reports errors. Set it to `PDO::ERRMODE_EXCEPTION` so failures throw a `PDOException` instead of failing silently.
`PDO::ATTR_DEFAULT_FETCH_MODE`	Controls the default shape of fetched rows, e.g. `PDO::FETCH_ASSOC` for associative arrays.

Examples

Example 1: Opening a connection

<?php
$dsn = "mysql:host=localhost;dbname=school;charset=utf8mb4";
$username = "root";
$password = "secret";

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

Output:

Connected successfully!

This connects to a MySQL database named school and explicitly switches on exception-based error reporting. Wrapping the connection in a try/catch block is important: if the credentials are wrong or the server is unreachable, PDO throws a PDOException that you can catch and handle gracefully instead of letting a raw fatal error (potentially containing your DSN and password) leak to the browser.

Example 2: Selecting rows with a prepared statement

<?php
$dsn = "mysql:host=localhost;dbname=school;charset=utf8mb4";
$pdo = new PDO($dsn, "root", "secret", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$stmt = $pdo->prepare("SELECT id, name, grade FROM students WHERE grade > :grade");
$stmt->execute(['grade' => 80]);

foreach ($stmt as $row) {
    echo "{$row['name']} scored {$row['grade']}" . PHP_EOL;
}

Output:

Alice scored 92
Diana scored 88

Here :grade is a named placeholder. Calling prepare() sends the query template to the database (or has the driver parse it) before any data is attached. execute() then supplies the actual value through an associative array whose keys match the placeholder names. Because the value is never woven into the SQL string, it cannot alter the query’s structure — this is what makes prepared statements immune to SQL injection. Iterating over a PDOStatement directly with foreach works because it implements Traversable, fetching one row per iteration.

Example 3: Inserting data inside a transaction

<?php
$pdo = new PDO("mysql:host=localhost;dbname=school;charset=utf8mb4", "root", "secret", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

try {
    $pdo->beginTransaction();

    $insert = $pdo->prepare("INSERT INTO students (name, grade) VALUES (:name, :grade)");
    $insert->execute(['name' => 'Charlie', 'grade' => 78]);
    $newId = $pdo->lastInsertId();

    $update = $pdo->prepare("UPDATE classes SET student_count = student_count + 1 WHERE id = :id");
    $update->execute(['id' => 3]);

    $pdo->commit();
    echo "Student added with ID: $newId";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

Output:

Student added with ID: 42

This example groups two related writes — inserting a new student and updating a class count — into a single transaction. beginTransaction() tells the database to hold off making changes permanent, both statements run, and commit() finalizes them together. If anything fails in between, the catch block calls rollBack() so neither write is applied, keeping the database consistent. lastInsertId() returns the auto-increment ID generated by the most recent INSERT.

How It Works Step by Step

1. Parse the DSN. PDO reads the driver name from the DSN prefix (e.g. mysql:) and loads the matching extension, such as pdo_mysql.
2. Open the connection. The driver opens a TCP or socket connection to the database server and authenticates using the supplied username and password.
3. Apply attributes. Any options passed in the constructor’s fourth argument (or set afterward with setAttribute()) configure behavior like error mode and fetch mode.
4. Prepare the statement. Calling prepare() sends the SQL template (with placeholders) to be parsed and compiled, separately from any data.
5. Bind and execute. Values passed to execute() (or bound earlier with bindValue()/bindParam()) are sent to the database, which substitutes them safely and runs the query.
6. Fetch results. For SELECT queries, you pull rows out of the resulting PDOStatement with methods like fetch(), fetchAll(), or by iterating with foreach.

Common Mistakes

Mistake 1: Concatenating user input into SQL

Building a query by inserting a variable straight into the SQL string reopens the exact vulnerability PDO exists to prevent.

<?php
$username = $_GET['username'];
$pdo = new PDO("mysql:host=localhost;dbname=school", "root", "secret");
$result = $pdo->query("SELECT * FROM users WHERE username = '$username'");
foreach ($result as $row) {
    echo $row['username'];
}

An attacker could submit a username like ' OR '1'='1 and change the meaning of the query entirely. Always use a prepared statement with a placeholder instead:

<?php
$username = $_GET['username'];
$pdo = new PDO("mysql:host=localhost;dbname=school", "root", "secret", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
$stmt->execute(['username' => $username]);
foreach ($stmt as $row) {
    echo $row['username'];
}

Mistake 2: Mixing named and positional placeholders

PDO does not allow a single prepared statement to mix ? placeholders with named :placeholder ones. This throws an error at execution time.

<?php
$pdo = new PDO("mysql:host=localhost;dbname=school", "root", "secret", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->prepare("SELECT * FROM students WHERE grade > ? AND class_id = :class_id");
$stmt->execute([80, 'class_id' => 3]);

foreach ($stmt as $row) {
    echo $row['name'];
}

Stick to one style consistently within a query:

<?php
$pdo = new PDO("mysql:host=localhost;dbname=school", "root", "secret", [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);
$stmt = $pdo->prepare("SELECT * FROM students WHERE grade > :grade AND class_id = :class_id");
$stmt->execute(['grade' => 80, 'class_id' => 3]);

foreach ($stmt as $row) {
    echo $row['name'];
}

Best Practices

Always set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION so database errors surface as catchable exceptions instead of failing silently.
Use prepared statements with bound parameters for every query that includes any variable data — never build SQL with string interpolation or concatenation.
Specify charset=utf8mb4 in the DSN to correctly store the full range of Unicode characters, including emoji.
Wrap multi-step writes in a transaction (beginTransaction(), commit(), rollBack()) so related changes succeed or fail together.
Reuse a single PDO connection object across a request rather than opening multiple connections.
Never expose raw exception messages (which can include the DSN or query) directly to end users in production; log them instead and show a generic error.
Set a sensible default fetch mode, such as PDO::FETCH_ASSOC, so you don’t have to specify it on every call.

Practice Exercises

Exercise 1: Write a script that connects to a MySQL database called shop with exception-mode error reporting enabled, then prints a success message.
Exercise 2: Write a prepared statement that selects all products from a products table where price is less than a value supplied by the user, using a named placeholder, and print each product’s name.
Exercise 3: Write a script that inserts a new order into an orders table and a matching row into an order_items table inside a single transaction, rolling back if either insert fails.

Summary

PDO is a database abstraction layer that provides one consistent API across many database systems.
A connection is created with new PDO($dsn, $username, $password, $options).
Prepared statements (prepare() + execute()) separate SQL structure from data, preventing SQL injection.
Named (:name) and positional (?) placeholders exist, but cannot be mixed in the same statement.
Setting PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION is essential for catching and handling database errors properly.
Transactions (beginTransaction(), commit(), rollBack()) keep multi-step writes consistent.

PHP Introduction to PDO

Overview / How PDO Works

Enabling PDO

Syntax

Examples

Example 1: Opening a connection

Example 2: Selecting rows with a prepared statement

Example 3: Inserting data inside a transaction

How It Works Step by Step

Common Mistakes

Mistake 1: Concatenating user input into SQL

Mistake 2: Mixing named and positional placeholders

Best Practices

Practice Exercises

Summary

Table of Contents