PHP PDO Introduction

PDO is like a universal remote that works with many different databases. You learn one set of buttons and can use it with MySQL and others.

In this page:

What is PDO?
PDO Error Modes
Executing Queries (query)
Executing Statements (exec)
Closing PDO Connections

Syntax

php

$pdo = new PDO("mysql:host=hostname;dbname=database_name", $user, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$statement = $pdo->query("SELECT * FROM table_name");

What is PDO?

PDO (PHP Data Objects) provides a single, consistent object-oriented API for talking to many different database systems — MySQL, PostgreSQL, SQLite, and more — so switching databases later doesn't mean rewriting all your query code.

Example: What is PDO?

php

<?php
$pdo = new PDO('sqlite::memory:');
echo get_class($pdo);
// The same PDO API works for MySQL, PostgreSQL, SQLite, and more
?>

PDO Error Modes

You create a PDO connection with new PDO($dsn, $user, $password), where the DSN (Data Source Name) string specifies the database driver and connection details, like 'mysql:host=localhost;dbname=myapp'.

Example: PDO Error Modes

php

<?php
// On a real server: new PDO('mysql:host=localhost;dbname=myapp', $user, $password);
$pdo = new PDO('sqlite::memory:');
echo "Connected via DSN";
?>

Executing Queries (query)

PDO supports named placeholders (:name) and positional placeholders (?) in prepared statements, giving you flexibility in how you bind values compared to mysqli's more rigid parameter binding.

Example: Executing Queries (query)

php

<?php
// Create a new `PDO` instance with 'sqlite::memory:', stored in `$pdo`
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER, name TEXT)");
// Declare `$stmt`, set to `$pdo->prepare("INSERT INTO users (id, name) VALUES (:id, :name)")`
$stmt = $pdo->prepare("INSERT INTO users (id, name) VALUES (:id, :name)");
$stmt->execute(['id' => 1, 'name' => 'Alice']);
// Print "Inserted using named placeholder" to the output
echo "Inserted using named placeholder";
?>

Executing Statements (exec)

Setting PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION makes PDO throw real exceptions on database errors instead of silently returning false, which is strongly recommended since it makes failures impossible to accidentally ignore.

Example: Executing Statements (exec)

php

<?php
// Create a new `PDO` instance with 'sqlite::memory:', stored in `$pdo`
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Try running this block; jump to `catch` if it throws
try {
    $pdo->exec("SELECT * FROM nonexistent_table");
// Catch PDOException $e
} catch (PDOException $e) {
    // Print "Caught exception instead of a silent failure" to the output
    echo "Caught exception instead of a silent failure";
}
?>

Closing PDO Connections

Because PDO abstracts over multiple database engines, many teams choose it over mysqli by default even for MySQL-only projects, simply for its more consistent and modern API and easier future portability.

Example: Closing PDO Connections

php

<?php
$pdo = new PDO('sqlite::memory:');
echo "PDO's consistent API is why many teams pick it even for MySQL-only projects";
$pdo = null; // closes the connection
?>

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQLi Connection Next Lesson → PHP CRUD Operations