Mastering Database Operations in PHP Using PDO

When developing PHP applications, interacting with a database is a fundamental task. While there are multiple ways to connect and perform database operations in PHP, the PDO (PHP Data Objects) extension stands out as the most flexible and secure method. PDO allows developers to work with multiple database systems through a consistent API and offers advanced features such as prepared statements, error handling, and transaction support.

In this blog post, we'll explore how to use PDO for database operations, showcasing modern PHP syntax and best practices. Additionally, we’ll demonstrate why you should always adopt the latest version of PHP and its return type declarations for cleaner, safer, and more maintainable code.

Why Use PDO Over Other Methods?

In the past, PHP offered several ways to connect to databases, such as the mysql_* functions, which were later deprecated, and the mysqli_* extension. However, PDO offers significant advantages over these methods:

Database Independence: PDO supports multiple databases like MySQL, PostgreSQL, SQLite, and more. This allows your code to be database-agnostic, making it easier to switch between databases without major changes in the application.
Prepared Statements: PDO supports prepared statements, which help protect against SQL injection attacks by separating SQL logic from user data.
Advanced Features: With PDO, you can use advanced features such as transaction management, error handling, and different fetch modes, making your database operations more robust.
Consistent API: PDO offers a consistent API across different databases, so your code looks the same, regardless of the database type.

Connecting to a Database with PDO in PHP 8+

To get started with PDO, you first need to establish a connection to your database. Here's an example of how to connect to a MySQL database using the latest PHP syntax.

<?php

declare(strict_types=1);

class DatabaseConnection
{
    private PDO $pdo;

    public function __construct(string $host, string $dbName, string $username, string $password)
    {
        $dsn = "mysql:host=$host;dbname=$dbName;charset=utf8mb4";
        $this->pdo = new PDO($dsn, $username, $password, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }

    public function getConnection(): PDO
    {
        return $this->pdo;
    }
}

// Example of usage:
$db = new DatabaseConnection('localhost', 'my_database', 'root', 'password');
$pdo = $db->getConnection();

Explanation of the Code:

Strict Typing (declare(strict_types=1);): By enforcing strict typing, PHP ensures that the expected types are followed, which reduces bugs and improves code safety.
Typed Properties (private PDO $pdo;): PHP 7.4 introduced typed properties, and using them ensures that $pdo will always be a PDO object.
Constructor Initialization: The __construct() method accepts database credentials as arguments, sets up the DSN (Data Source Name), and establishes a connection using the new PDO() constructor.
Error Handling (PDO::ATTR_ERRMODE): PDO throws exceptions for database errors by setting PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION. This is crucial for catching and handling errors properly.
Fetch Mode (PDO::ATTR_DEFAULT_FETCH_MODE): By default, PDO fetches data as an associative array. This makes it easy to work with query results.

Performing CRUD Operations with PDO

Inserting Data into the Database

Using prepared statements is one of the key security features of PDO. Here's how to perform an INSERT operation:

<?php

class UserRepository
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function addUser(string $name, string $email): bool
    {
        $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute(['name' => $name, 'email' => $email]);
    }
}

// Usage:
$userRepo = new UserRepository($pdo);
$userRepo->addUser('John Doe', 'john.doe@example.com');

In this example:

We use prepare() to create a prepared statement with placeholders (:name, :email).
We bind values using the execute() method, passing an array of data.
The addUser() method returns true if the insertion is successful.

Fetching Data from the Database

Fetching data from the database can be done using SELECT queries. Here's how to retrieve a user by ID:

<?php

class UserRepository
{
    // Other methods...

    public function getUserById(int $id): ?array
    {
        $sql = "SELECT * FROM users WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['id' => $id]);
        $result = $stmt->fetch();
        return $result !== false ? $result : null;
    }
}

// Usage:
$user = $userRepo->getUserById(1);
if ($user !== null) {
    echo "User found: " . $user['name'];
} else {
    echo "User not found.";
}

In this example:

The fetch() method retrieves a single row. If no result is found, it returns false.
The return type ?array means the function can return an array or null if no user is found.

Updating Data in the Database

Updating records is similar to inserting them. Here's how you can update a user's email:

<?php

class UserRepository
{
    // Other methods...

    public function updateUserEmail(int $id, string $email): bool
    {
        $sql = "UPDATE users SET email = :email WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute(['email' => $email, 'id' => $id]);
    }
}

// Usage:
$userRepo->updateUserEmail(1, 'new.email@example.com');

Deleting Data from the Database

To delete a record, use the following:

<?php

class UserRepository
{
    // Other methods...

    public function deleteUser(int $id): bool
    {
        $sql = "DELETE FROM users WHERE id = :id";
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute(['id' => $id]);
    }
}

// Usage:
$userRepo->deleteUser(1);

Advanced PDO Features

Transactions

PDO supports transactions, which are useful when you need to ensure that a series of database operations either all succeed or all fail.

<?php

$pdo->beginTransaction();

try {
    $pdo->exec("INSERT INTO users (name) VALUES ('User1')");
    $pdo->exec("INSERT INTO users (name) VALUES ('User2')");
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Failed: " . $e->getMessage();
}

Fetching Multiple Rows

To fetch multiple rows, use the fetchAll() method:

<?php

$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);
$users = $stmt->fetchAll();

Conclusion

Using PDO for database operations in PHP is a secure, flexible, and modern approach to interacting with databases. By following best practices such as using prepared statements, handling errors properly, and leveraging modern PHP features like strict types and return types, you ensure that your code is maintainable, scalable, and secure.

Always keep your PHP version up to date to take advantage of new features, performance improvements, and security patches. With PDO and PHP's latest syntax, you can build robust, secure, and future-proof applications.