Database Operations with PDO

50 min

PDO (PHP Data Objects) is PHP's modern database abstraction layer that provides a consistent interface for accessing databases. Unlike the older mysql_* functions (now deprecated), PDO works with multiple database systems (MySQL, PostgreSQL, SQLite, etc.) using the same API. PDO provides prepared statements, transactions, error handling, and fetch modes that make database operations safer and more efficient. Understanding PDO is essential for modern PHP database development.

Prepared statements are PDO's most important security feature. They separate SQL structure from data, preventing SQL injection attacks. Prepared statements are compiled once and executed multiple times with different data, improving performance. You create prepared statements with `prepare()`, bind parameters with `bindParam()` or `bindValue()`, and execute with `execute()`. Placeholders (`?` or named `:name`) represent values to be bound. Understanding prepared statements is crucial for secure database operations.

PDO connection requires a Data Source Name (DSN) string that specifies the database driver, host, database name, and optionally charset. Connection options include error mode (PDO::ERRMODE_EXCEPTION for exceptions), fetch mode (how rows are returned), and other settings. PDO connections should be configured with proper error handling and charset settings. Understanding PDO connection helps you set up secure, efficient database access.

PDO provides several fetch modes for retrieving data: `PDO::FETCH_ASSOC` (associative array), `PDO::FETCH_NUM` (numeric array), `PDO::FETCH_OBJ` (object), `PDO::FETCH_CLASS` (populate class instances), and `PDO::FETCH_BOTH` (both associative and numeric). The default fetch mode can be set on the connection or per query. Understanding fetch modes helps you retrieve data in the format that best suits your needs.

Transactions enable you to group multiple database operations into atomic units. Either all operations succeed (commit) or all fail (rollback). Transactions are essential for maintaining data integrity when multiple related operations must succeed together. PDO supports transactions with `beginTransaction()`, `commit()`, and `rollBack()`. Understanding transactions helps you maintain data consistency in complex operations.

Best practices include always using prepared statements for user input, setting PDO::ERRMODE_EXCEPTION for better error handling, using transactions for related operations, closing connections properly, and using appropriate fetch modes. PDO should be used instead of deprecated mysql_* functions. Understanding PDO enables you to build secure, efficient database-driven PHP applications.

Key Concepts

PDO provides a consistent interface for multiple database systems.
Prepared statements prevent SQL injection and improve performance.
PDO supports transactions for atomic database operations.
Fetch modes control how database rows are returned.
Always use prepared statements when working with user input.

Learning Objectives

Master

Connecting to databases using PDO
Using prepared statements for secure database operations
Working with transactions for data integrity
Understanding PDO fetch modes and error handling

Develop

Database security thinking and SQL injection prevention
Understanding transaction management and data integrity
Designing secure, efficient database access layers

Tips

Always use prepared statements for queries with user input.
Set PDO::ERRMODE_EXCEPTION for better error handling.
Use transactions for related database operations.
Use appropriate fetch modes for your data needs.

Common Pitfalls

Not using prepared statements, creating SQL injection vulnerabilities.
Not handling PDO exceptions, causing application crashes.
Not using transactions for related operations, causing data inconsistency.
Using deprecated mysql_* functions instead of PDO.

Summary

PDO provides a consistent, secure interface for database access.
Prepared statements prevent SQL injection and improve performance.
Transactions ensure data integrity for related operations.
Understanding PDO is essential for modern PHP database development.
Always use prepared statements when working with user input.

Exercise

Create a simple CRUD application using PDO for database operations.

<?php
// Database configuration
$host = "localhost";
$dbname = "test_db";
$username = "root";
$password = "";

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Create table if not exists
    $sql = "CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";
    $pdo->exec($sql);
    
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
    exit();
}

// Handle form submission
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $name = trim($_POST["name"] ?? "");
    $email = trim($_POST["email"] ?? "");
    
    if (!empty($name) && !empty($email)) {
        try {
            $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
            $stmt->execute([$name, $email]);
            $message = "User added successfully!";
        } catch(PDOException $e) {
            $message = "Error: " . $e->getMessage();
        }
    }
}

// Fetch all users
try {
    $stmt = $pdo->query("SELECT * FROM users ORDER BY created_at DESC");
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e) {
    $users = [];
    $message = "Error fetching users: " . $e->getMessage();
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>User Management</title>
</head>
<body>
    <h1>User Management</h1>
    
    <?php if (isset($message)): ?>
        <p style="color: <?php echo strpos($message, 'successfully') !== false ? 'green' : 'red'; ?>">
            <?php echo htmlspecialchars($message); ?>
        </p>
    <?php endif; ?>
    
    <h2>Add New User</h2>
    <form method="POST">
        <div>
            <label for="name">Name:</label>
            <input type="text" id="name" name="name" required>
        </div>
        <br>
        <div>
            <label for="email">Email:</label>
            <input type="email" id="email" name="email" required>
        </div>
        <br>
        <button type="submit">Add User</button>
    </form>
    
    <h2>Users List</h2>
    <?php if (empty($users)): ?>
        <p>No users found.</p>
    <?php else: ?>
        <table border="1">
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Created At</th>
            </tr>
            <?php foreach ($users as $user): ?>
                <tr>
                    <td><?php echo htmlspecialchars($user["id"]); ?></td>
                    <td><?php echo htmlspecialchars($user["name"]); ?></td>
                    <td><?php echo htmlspecialchars($user["name"]); ?></td>
                    <td><?php echo htmlspecialchars($user["created_at"]); ?></td>
                </tr>
            <?php endforeach; ?>
        </table>
    <?php endif; ?>
</body>
</html>

Database Operations with PDO