PDO (PHP Data Objects)

Introduction

MySQLi works well, but it is built specifically for MySQL. If your application ever needed to switch databases, or you simply wanted one consistent, modern API for database work, you would reach for PDO instead.

This lesson introduces PDO (PHP Data Objects) — how to connect, query, fetch results, and use prepared statements — and closes with a direct comparison to MySQLi so you know when to reach for each.

What is PDO?

PDO (PHP Data Objects) is a database abstraction layer built into PHP. Rather than being tied to one database system like MySQLi is to MySQL, PDO provides a single, consistent, object-oriented API that works across MySQL, PostgreSQL, SQLite, SQL Server, and several other database systems.

This matters because it means the same PDO code you write for MySQL today could, with only a change to the connection string, work against a different database tomorrow — the queries, fetching, and prepared statement syntax stay the same.

Connecting with PDO

A PDO connection is created with `new PDO(...)`, passing a Data Source Name (DSN) string that describes the database type, host, and database name, along with a username and password. Because PDO throws an exception if the connection fails, the connection should be wrapped in a try/catch block.

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

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

Connected successfully!

Running a Query & Fetching Results

For queries without any external input, `query()` runs the SQL directly, and `fetchAll()` with the `PDO::FETCH_ASSOC` mode returns every row as an associative array.

<?php
$stmt = $pdo->query("SELECT id, name, course FROM students");
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($students as $student) {
    echo $student["name"] . " - " . $student["course"] . "\n";
}
?>

Amit Verma - PHP Development
Sara Khan - Web Design

You can also fetch one row at a time with `fetch(PDO::FETCH_ASSOC)`, which is useful when you only expect a single result.

<?php
$stmt = $pdo->query("SELECT * FROM students WHERE id = 1");
$student = $stmt->fetch(PDO::FETCH_ASSOC);

echo $student["name"];
?>

Amit Verma

Prepared Statements in PDO

PDO prepared statements follow the same principle you learned with MySQLi — the query structure and its data are sent separately — but with a slightly different syntax. Placeholders can be positional (`?`) or named (`:name`), and values are passed as an array directly to `execute()` instead of a separate bind_param() call.

<?php
$username = $_POST["username"];
$password = $_POST["password"];

$stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->execute([$username, $password]);

$user = $stmt->fetch(PDO::FETCH_ASSOC);
?>

Named placeholders can make longer queries easier to read, since each value is matched by name instead of position.

<?php
$stmt = $pdo->prepare("INSERT INTO students (name, age, course) VALUES (:name, :age, :course)");

$stmt->execute([
    "name" => "Rahul Singh",
    "age" => 20,
    "course" => "Database Systems",
]);

echo "Rows inserted: " . $stmt->rowCount();
?>

Rows inserted: 1

Insert, Update & Delete with PDO

The same prepare-then-execute pattern applies to UPDATE and DELETE, with `rowCount()` telling you how many rows were affected.

<?php
// UPDATE
$stmt = $pdo->prepare("UPDATE students SET course = :course WHERE id = :id");
$stmt->execute(["course" => "Advanced PHP", "id" => 1]);

// DELETE
$stmt = $pdo->prepare("DELETE FROM students WHERE id = :id");
$stmt->execute(["id" => 3]);
echo "Rows deleted: " . $stmt->rowCount();
?>

Rows deleted: 1

PDO vs MySQLi

Both PDO and MySQLi connect PHP to MySQL safely when used with prepared statements. The real difference is scope and consistency, not security.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

PDO gives you one consistent, exception-driven, object-oriented way to talk to MySQL and many other databases, with prepared statements built in from the start.

In this lesson, you connected with PDO inside a try/catch, queried and fetched data with PDO::FETCH_ASSOC, used prepared statements with named and positional placeholders, and compared PDO directly to MySQLi. Next, you will use these database skills to build a real authentication and login system.