Learn PHP PDO: A Comprehensive Guide with Examples

Oct 8, 2021

Get up to 65% Off Hosting with FREE SSL & FREE Domains!

* Web 2.0 University is supported by it's audience. If you purchase through links on our site, we may earn an affiliate commision.

PHP Data Objects (PDO) is a database access layer providing a uniform method of access to multiple databases. PDO is widely recommended for database interactions in PHP because it provides a secure and flexible way to handle databases. In this blog post, we’ll explore PDO, its benefits, and provide detailed examples to help you learn how to use it effectively.

What is PDO?

PDO stands for PHP Data Objects. It is a consistent interface for accessing databases in PHP. PDO supports multiple databases, including MySQL, PostgreSQL, SQLite, and many others. The main features of PDO include:

Database Independence: Write your code once and switch databases easily.
Prepared Statements: Protect against SQL injection attacks.
Error Handling: Provides robust error handling mechanisms.

Why Use PDO?

Security: PDO’s use of prepared statements helps prevent SQL injection.
Flexibility: Easily switch between different databases.
Simplified Code: PDO provides a clean, object-oriented API.

Setting Up PDO

To use PDO, ensure you have PHP installed along with a database like MySQL. Most web servers, such as XAMPP or WAMP, come with these pre-installed.

Basic PDO Operations

Let’s start with the basics of using PDO. We’ll cover connecting to a database, performing CRUD (Create, Read, Update, Delete) operations, and handling errors.

Step 1: Connecting to a Database

<?php
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'root';
$password = '';

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();
}
?>

Step 2: Creating a Table

<?php
$sql = "CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT NOT NULL
)";

try {
    $pdo->exec($sql);
    echo "Table users created successfully";
} catch (PDOException $e) {
    echo "Error creating table: " . $e->getMessage();
}
?>

Step 3: Inserting Data

<?php
$name = 'John Doe';
$email = '[email protected]';
$age = 30;

$sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
$stmt = $pdo->prepare($sql);

$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':age', $age);

try {
    $stmt->execute();
    echo "New record created successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Step 4: Reading Data

<?php
$sql = "SELECT * FROM users";
$stmt = $pdo->prepare($sql);

try {
    $stmt->execute();
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($users as $user) {
        echo "ID: " . $user['id'] . "<br>";
        echo "Name: " . $user['name'] . "<br>";
        echo "Email: " . $user['email'] . "<br>";
        echo "Age: " . $user['age'] . "<br><br>";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Step 5: Updating Data

<?php
$id = 1;
$newEmail = '[email protected]';

$sql = "UPDATE users SET email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);

$stmt->bindParam(':email', $newEmail);
$stmt->bindParam(':id', $id);

try {
    $stmt->execute();
    echo "Record updated successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Step 6: Deleting Data

<?php
$id = 1;

$sql = "DELETE FROM users WHERE id = :id";
$stmt = $pdo->prepare($sql);

$stmt->bindParam(':id', $id);

try {
    $stmt->execute();
    echo "Record deleted successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Advanced PDO Features

Using Transactions

Transactions allow you to execute multiple queries as a single unit. This ensures that either all queries are executed successfully, or none are, maintaining data integrity.

<?php
try {
    $pdo->beginTransaction();

    $stmt1 = $pdo->prepare("INSERT INTO users (name, email, age) VALUES ('Alice', '[email protected]', 25)");
    $stmt2 = $pdo->prepare("INSERT INTO users (name, email, age) VALUES ('Bob', '[email protected]', 28)");

    $stmt1->execute();
    $stmt2->execute();

    $pdo->commit();
    echo "Transaction completed successfully";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}
?>

Fetching Data in Different Formats

PDO provides flexibility in fetching data. You can fetch data as an associative array, a numeric array, or an object.

<?php
$sql = "SELECT * FROM users";
$stmt = $pdo->prepare($sql);
$stmt->execute();

// Fetch as associative array
$usersAssoc = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($usersAssoc);

// Fetch as numeric array
$usersNum = $stmt->fetchAll(PDO::FETCH_NUM);
print_r($usersNum);

// Fetch as object
$usersObj = $stmt->fetchAll(PDO::FETCH_OBJ);
print_r($usersObj);
?>

Error Handling

PDO offers robust error handling mechanisms. You can set the error mode to silent, warning, or exception.

<?php
// Setting error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    // Intentionally causing an error by using a non-existent table
    $stmt = $pdo->prepare("SELECT * FROM non_existent_table");
    $stmt->execute();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Conclusion

In this guide, we’ve covered the basics and some advanced features of using PDO in PHP. We’ve seen how to connect to a database, perform CRUD operations, handle transactions, and manage errors using PDO. By mastering PDO, you can write secure, efficient, and maintainable database interaction code in your PHP applications.