How to Use PDO in PHP: Simple Guide with Examples

Use PDO in PHP by creating a new PDO object with your database connection details, then prepare and execute SQL statements securely. PDO supports multiple databases and helps prevent SQL injection by using prepared statements.

Syntax

The basic syntax to use PDO involves creating a new PDO instance with a data source name (DSN), username, and password. Then you can prepare SQL statements and execute them safely.

• DSN: Defines the database type, host, and database name.
• Username & Password: Credentials for database access.
• prepare(): Prepares an SQL statement for execution.
• execute(): Runs the prepared statement with optional parameters.

php

<?php
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$username = 'dbuser';
$password = 'dbpass';

try {
    $pdo = new PDO($dsn, $username, $password);
    $stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
    $stmt->execute(['id' => 1]);
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
?>

Example

This example connects to a MySQL database, inserts a new user securely using prepared statements, and fetches all users to display their names.

php

<?php
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$username = 'dbuser';
$password = 'dbpass';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Insert a new user
    $stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
    $stmt->execute(['name' => 'Alice', 'email' => '[e-mail]']);

    // Fetch all users
    $stmt = $pdo->query('SELECT name, email FROM users');
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($users as $user) {
        echo $user['name'] . ' - ' . $user['email'] . "\n";
    }
} catch (PDOException $e) {
    echo 'Error: ' . $e->getMessage();
}
?>

Output

Alice - [e-mail]

Common Pitfalls

Common mistakes when using PDO include not setting the error mode, which hides errors, and not using prepared statements, which risks SQL injection. Also, forgetting to specify the charset in the DSN can cause encoding issues.

Always set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION to catch errors properly.

php

<?php
// Wrong way: No error mode, vulnerable to SQL injection
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass');
$id = $_GET['id'];
$result = $pdo->query("SELECT * FROM users WHERE id = $id");

// Right way: Use error mode and prepared statements
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => $id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
?>

Quick Reference

Feature	Description
PDO Object	Create with DSN, username, password
prepare()	Prepare SQL with placeholders
execute()	Run prepared statement with values
fetch()	Get one row from result
fetchAll()	Get all rows from result
Error Mode	Set with ATTR_ERRMODE to ERRMODE_EXCEPTION
Transactions	Use beginTransaction(), commit(), rollBack()

Key Takeaways

Create a PDO instance with DSN, username, and password to connect to your database.

Always use prepared statements with placeholders to prevent SQL injection.

Set PDO error mode to ERRMODE_EXCEPTION to catch and handle errors effectively.

Specify charset in DSN to avoid encoding problems.

Use fetch() or fetchAll() to retrieve query results safely.