PHP PDO Basics

Introduction to PDO

PHP Data Objects (PDO) is a database abstraction layer that provides a consistent interface for accessing databases in PHP. Unlike older MySQL-specific extensions like mysql_* and mysqli_*, PDO works with multiple database systems including MySQL, PostgreSQL, SQLite, and many others.

PDO represents a significant advancement in PHP database programming, offering prepared statements, object-oriented interface, and robust error handling out of the box. It's the recommended approach for database interactions in modern PHP applications.

Why PDO is Superior

Database Independence: Write code once and run it on different database systems with minimal changes. PDO abstracts database-specific differences behind a common interface.

Security: Built-in prepared statement support makes SQL injection attacks much harder to execute. Parameters are automatically escaped and validated.

Performance: Prepared statements can be executed multiple times with different parameters, reducing parsing overhead for repeated queries.

Object-Oriented Interface: Clean, modern API that integrates well with object-oriented PHP code.

Error Handling: Comprehensive error reporting through exceptions, making debugging and error handling more robust.

Feature Rich: Supports transactions, stored procedures, and advanced database features across different systems.

PDO vs. Other Database Extensions

mysqli vs PDO: While mysqli is MySQL-specific and offers some advanced MySQL features, PDO provides broader database support and is generally easier to use.

Legacy mysql_ vs PDO*: The old mysql extension was deprecated in PHP 5.5 and removed in PHP 7.0. PDO is the modern replacement with superior security and functionality.

Database-Specific Extensions: While direct database extensions might offer database-specific optimizations, PDO provides the best balance of features, security, and portability.

Database Connection

Basic Connection Syntax

<?php
/**
 * Basic PDO database connection examples
 * 
 * PDO connections are established using a Data Source Name (DSN)
 * that specifies the database type, location, and connection parameters.
 */

// MySQL connection
try {
    $mysqlDsn = 'mysql:host=localhost;dbname=myapp;charset=utf8mb4';
    $username = 'db_user';
    $password = 'secure_password';
    
    $pdo = new PDO($mysqlDsn, $username, $password);
    echo "Connected to MySQL successfully\n";
} catch (PDOException $e) {
    die("MySQL connection failed: " . $e->getMessage());
}

// PostgreSQL connection
try {
    $pgsqlDsn = 'pgsql:host=localhost;dbname=myapp;port=5432';
    $pdo = new PDO($pgsqlDsn, $username, $password);
    echo "Connected to PostgreSQL successfully\n";
} catch (PDOException $e) {
    die("PostgreSQL connection failed: " . $e->getMessage());
}

// SQLite connection (file-based database)
try {
    $sqliteDsn = 'sqlite:/path/to/database.db';
    $pdo = new PDO($sqliteDsn);
    echo "Connected to SQLite successfully\n";
} catch (PDOException $e) {
    die("SQLite connection failed: " . $e->getMessage());
}

// SQLite in-memory database (for testing)
try {
    $pdo = new PDO('sqlite::memory:');
    echo "Created in-memory SQLite database\n";
} catch (PDOException $e) {
    die("In-memory SQLite creation failed: " . $e->getMessage());
}
?>

Connection Options and Configuration

<?php
/**
 * Advanced PDO connection configuration
 * 
 * PDO supports various options for customizing connection behavior,
 * error handling, and performance characteristics.
 */

class DatabaseConnection
{
    private PDO $pdo;
    
    public function __construct(string $dsn, string $username = '', string $password = '', array $options = [])
    {
        // Default PDO options for security and performance
        $defaultOptions = [
            // Error handling mode
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            
            // Default fetch mode
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            
            // Disable emulated prepared statements for better security
            PDO::ATTR_EMULATE_PREPARES => false,
            
            // Enable persistent connections for better performance
            PDO::ATTR_PERSISTENT => false,
            
            // Set connection timeout
            PDO::ATTR_TIMEOUT => 30,
            
            // Force column names to lowercase (for consistency)
            PDO::ATTR_CASE => PDO::CASE_LOWER,
            
            // Don't convert numeric values to strings
            PDO::ATTR_STRINGIFY_FETCHES => false,
        ];
        
        // Merge custom options with defaults
        $options = array_merge($defaultOptions, $options);
        
        try {
            $this->pdo = new PDO($dsn, $username, $password, $options);
            
            // Set additional connection-specific settings
            $this->configureConnection();
            
        } catch (PDOException $e) {
            throw new DatabaseException("Database connection failed: " . $e->getMessage(), 0, $e);
        }
    }
    
    /**
     * Configure database-specific settings after connection
     */
    private function configureConnection(): void
    {
        $driver = $this->pdo->getAttribute(PDO::ATTR_DRIVER_NAME);
        
        switch ($driver) {
            case 'mysql':
                // Set MySQL-specific settings
                $this->pdo->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
                $this->pdo->exec("SET time_zone = '+00:00'");
                $this->pdo->exec("SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'");
                break;
                
            case 'pgsql':
                // Set PostgreSQL-specific settings
                $this->pdo->exec("SET NAMES 'UTF8'");
                $this->pdo->exec("SET timezone = 'UTC'");
                break;
                
            case 'sqlite':
                // Enable foreign key constraints in SQLite
                $this->pdo->exec("PRAGMA foreign_keys = ON");
                $this->pdo->exec("PRAGMA journal_mode = WAL");
                break;
        }
    }
    
    public function getPdo(): PDO
    {
        return $this->pdo;
    }
    
    /**
     * Test database connection
     */
    public function testConnection(): bool
    {
        try {
            $this->pdo->query('SELECT 1');
            return true;
        } catch (PDOException $e) {
            return false;
        }
    }
    
    /**
     * Get database version information
     */
    public function getVersion(): string
    {
        return $this->pdo->getAttribute(PDO::ATTR_SERVER_VERSION);
    }
}

// Custom exception for database errors
class DatabaseException extends Exception {}

// Usage example
try {
    $config = [
        'dsn' => 'mysql:host=localhost;dbname=myapp;charset=utf8mb4',
        'username' => 'db_user',
        'password' => 'secure_password',
        'options' => [
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_TIMEOUT => 10
        ]
    ];
    
    $db = new DatabaseConnection($config['dsn'], $config['username'], $config['password'], $config['options']);
    
    echo "Database connected successfully\n";
    echo "Version: " . $db->getVersion() . "\n";
    
} catch (DatabaseException $e) {
    echo "Database error: " . $e->getMessage() . "\n";
}
?>

Basic Queries

Simple SELECT Queries

<?php
/**
 * Basic PDO query examples
 * 
 * Demonstrates different ways to execute and fetch results
 * from SELECT queries using PDO.
 */

// Assuming $pdo is a valid PDO connection
$pdo = new PDO('mysql:host=localhost;dbname=myapp', $username, $password, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]);

// Simple query without parameters
try {
    $stmt = $pdo->query('SELECT * FROM users ORDER BY created_at DESC LIMIT 10');
    
    // Fetch all results at once
    $users = $stmt->fetchAll();
    
    foreach ($users as $user) {
        echo "User: {$user['name']} ({$user['email']})\n";
    }
    
} catch (PDOException $e) {
    echo "Query failed: " . $e->getMessage() . "\n";
}

// Query with fetch modes
$stmt = $pdo->query('SELECT id, name, email FROM users LIMIT 5');

// Fetch as associative array (default)
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "ID: {$row['id']}, Name: {$row['name']}\n";
}

// Fetch as object
$stmt = $pdo->query('SELECT id, name, email FROM users LIMIT 5');
while ($user = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo "ID: {$user->id}, Name: {$user->name}\n";
}

// Fetch into custom class
class User
{
    public int $id;
    public string $name;
    public string $email;
    public DateTime $created_at;
    
    public function getDisplayName(): string
    {
        return $this->name . ' (' . $this->email . ')';
    }
}

$stmt = $pdo->query('SELECT id, name, email, created_at FROM users LIMIT 5');
$stmt->setFetchMode(PDO::FETCH_CLASS, User::class);

while ($user = $stmt->fetch()) {
    echo $user->getDisplayName() . "\n";
}
?>

Prepared Statements

Parameterized Queries for Security

<?php
/**
 * Prepared statements with PDO
 * 
 * Prepared statements are the cornerstone of secure database
 * programming. They prevent SQL injection and improve performance
 * for repeated queries.
 */

class UserRepository
{
    private PDO $pdo;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    /**
     * Find user by ID using positional parameters
     */
    public function findById(int $id): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE id = ?');
        $stmt->execute([$id]);
        
        $user = $stmt->fetch();
        return $user ?: null;
    }
    
    /**
     * Find user by email using named parameters
     */
    public function findByEmail(string $email): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE email = :email');
        $stmt->bindParam(':email', $email, PDO::PARAM_STR);
        $stmt->execute();
        
        $user = $stmt->fetch();
        return $user ?: null;
    }
    
    /**
     * Alternative syntax for named parameters
     */
    public function findByEmailAlternative(string $email): ?array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM users WHERE email = :email');
        $stmt->execute(['email' => $email]);
        
        $user = $stmt->fetch();
        return $user ?: null;
    }
    
    /**
     * Search users with multiple parameters
     */
    public function searchUsers(string $searchTerm, bool $activeOnly = true, int $limit = 10): array
    {
        $sql = 'SELECT * FROM users WHERE (name LIKE :search OR email LIKE :search)';
        $params = ['search' => "%{$searchTerm}%"];
        
        if ($activeOnly) {
            $sql .= ' AND active = :active';
            $params['active'] = 1;
        }
        
        $sql .= ' ORDER BY name LIMIT :limit';
        
        $stmt = $this->pdo->prepare($sql);
        
        // Bind parameters with explicit types
        $stmt->bindParam(':search', $params['search'], PDO::PARAM_STR);
        
        if ($activeOnly) {
            $stmt->bindParam(':active', $params['active'], PDO::PARAM_INT);
        }
        
        $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll();
    }
    
    /**
     * Create new user with proper parameter binding
     */
    public function createUser(array $userData): int
    {
        $sql = 'INSERT INTO users (name, email, password_hash, active, created_at) 
                VALUES (:name, :email, :password_hash, :active, :created_at)';
        
        $stmt = $this->pdo->prepare($sql);
        
        $params = [
            'name' => $userData['name'],
            'email' => $userData['email'],
            'password_hash' => password_hash($userData['password'], PASSWORD_DEFAULT),
            'active' => $userData['active'] ?? true,
            'created_at' => date('Y-m-d H:i:s')
        ];
        
        $stmt->execute($params);
        
        // Return the ID of the newly created user
        return (int) $this->pdo->lastInsertId();
    }
    
    /**
     * Update user with selective field updates
     */
    public function updateUser(int $id, array $updateData): bool
    {
        // Build dynamic update query
        $fields = [];
        $params = ['id' => $id];
        
        foreach ($updateData as $field => $value) {
            if (in_array($field, ['name', 'email', 'active'])) {
                $fields[] = "$field = :$field";
                $params[$field] = $value;
            }
        }
        
        if (empty($fields)) {
            return false; // No valid fields to update
        }
        
        $sql = 'UPDATE users SET ' . implode(', ', $fields) . ', updated_at = :updated_at WHERE id = :id';
        $params['updated_at'] = date('Y-m-d H:i:s');
        
        $stmt = $this->pdo->prepare($sql);
        $result = $stmt->execute($params);
        
        return $stmt->rowCount() > 0;
    }
    
    /**
     * Delete user by ID
     */
    public function deleteUser(int $id): bool
    {
        $stmt = $this->pdo->prepare('DELETE FROM users WHERE id = :id');
        $stmt->execute(['id' => $id]);
        
        return $stmt->rowCount() > 0;
    }
    
    /**
     * Batch insert users for better performance
     */
    public function batchInsertUsers(array $users): array
    {
        $sql = 'INSERT INTO users (name, email, password_hash, created_at) VALUES (?, ?, ?, ?)';
        $stmt = $this->pdo->prepare($sql);
        
        $insertedIds = [];
        $createdAt = date('Y-m-d H:i:s');
        
        foreach ($users as $user) {
            $passwordHash = password_hash($user['password'], PASSWORD_DEFAULT);
            
            $stmt->execute([
                $user['name'],
                $user['email'],
                $passwordHash,
                $createdAt
            ]);
            
            $insertedIds[] = (int) $this->pdo->lastInsertId();
        }
        
        return $insertedIds;
    }
}

// Usage examples
try {
    $userRepo = new UserRepository($pdo);
    
    // Find user by ID
    $user = $userRepo->findById(123);
    if ($user) {
        echo "Found user: " . $user['name'] . "\n";
    }
    
    // Search users
    $results = $userRepo->searchUsers('john', true, 5);
    echo "Found " . count($results) . " users matching 'john'\n";
    
    // Create new user
    $newUserId = $userRepo->createUser([
        'name' => 'Jane Doe',
        'email' => '[email protected]',
        'password' => 'secure_password123',
        'active' => true
    ]);
    echo "Created user with ID: $newUserId\n";
    
    // Update user
    $updated = $userRepo->updateUser($newUserId, [
        'name' => 'Jane Smith',
        'active' => false
    ]);
    echo $updated ? "User updated successfully\n" : "Update failed\n";
    
} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage() . "\n";
}
?>

Error Handling

Exception-Based Error Management

<?php
/**
 * Comprehensive PDO error handling
 * 
 * Proper error handling is crucial for robust database applications.
 * PDO provides excellent exception-based error reporting.
 */

class DatabaseManager
{
    private PDO $pdo;
    private bool $debugMode;
    
    public function __construct(PDO $pdo, bool $debugMode = false)
    {
        $this->pdo = $pdo;
        $this->debugMode = $debugMode;
        
        // Ensure PDO is configured for exceptions
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    
    /**
     * Execute query with comprehensive error handling
     */
    public function executeQuery(string $sql, array $params = []): PDOStatement
    {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt;
            
        } catch (PDOException $e) {
            $this->handleDatabaseError($e, $sql, $params);
            throw $e; // Re-throw after logging
        }
    }
    
    /**
     * Handle database errors with appropriate logging and user feedback
     */
    private function handleDatabaseError(PDOException $e, string $sql, array $params): void
    {
        $errorInfo = [
            'message' => $e->getMessage(),
            'code' => $e->getCode(),
            'sql' => $sql,
            'params' => $this->debugMode ? $params : '[HIDDEN]',
            'trace' => $this->debugMode ? $e->getTraceAsString() : '[HIDDEN]',
            'timestamp' => date('Y-m-d H:i:s')
        ];
        
        // Log detailed error information
        error_log('Database Error: ' . json_encode($errorInfo));
        
        // Handle specific error types
        switch ($e->getCode()) {
            case '23000': // Integrity constraint violation
                $this->handleConstraintViolation($e);
                break;
                
            case '42S02': // Table or view not found
                $this->handleTableNotFound($e);
                break;
                
            case '42000': // Syntax error
                $this->handleSyntaxError($e);
                break;
                
            case 'HY000': // General error
                $this->handleGeneralError($e);
                break;
                
            default:
                $this->handleUnknownError($e);
        }
    }
    
    private function handleConstraintViolation(PDOException $e): void
    {
        if (strpos($e->getMessage(), 'Duplicate entry') !== false) {
            throw new DatabaseException('A record with this information already exists.', 'DUPLICATE_ENTRY', $e);
        } elseif (strpos($e->getMessage(), 'foreign key constraint') !== false) {
            throw new DatabaseException('Cannot complete operation due to related data constraints.', 'FOREIGN_KEY_VIOLATION', $e);
        } else {
            throw new DatabaseException('Data integrity constraint violated.', 'CONSTRAINT_VIOLATION', $e);
        }
    }
    
    private function handleTableNotFound(PDOException $e): void
    {
        throw new DatabaseException('Required database table not found. Please check database setup.', 'TABLE_NOT_FOUND', $e);
    }
    
    private function handleSyntaxError(PDOException $e): void
    {
        if ($this->debugMode) {
            throw new DatabaseException('SQL syntax error: ' . $e->getMessage(), 'SYNTAX_ERROR', $e);
        } else {
            throw new DatabaseException('Database query error occurred.', 'SYNTAX_ERROR', $e);
        }
    }
    
    private function handleGeneralError(PDOException $e): void
    {
        if (strpos($e->getMessage(), 'server has gone away') !== false) {
            throw new DatabaseException('Database connection lost. Please try again.', 'CONNECTION_LOST', $e);
        } else {
            throw new DatabaseException('Database operation failed.', 'GENERAL_ERROR', $e);
        }
    }
    
    private function handleUnknownError(PDOException $e): void
    {
        throw new DatabaseException('An unexpected database error occurred.', 'UNKNOWN_ERROR', $e);
    }
    
    /**
     * Execute transaction with proper error handling
     */
    public function executeTransaction(callable $callback)
    {
        try {
            $this->pdo->beginTransaction();
            
            $result = $callback($this);
            
            $this->pdo->commit();
            return $result;
            
        } catch (Exception $e) {
            $this->pdo->rollback();
            
            if ($e instanceof PDOException) {
                $this->handleDatabaseError($e, 'TRANSACTION', []);
            }
            
            throw $e;
        }
    }
}

/**
 * Custom database exception with additional context
 */
class DatabaseException extends Exception
{
    private string $errorType;
    private ?PDOException $originalException;
    
    public function __construct(string $message, string $errorType = 'UNKNOWN', ?PDOException $originalException = null)
    {
        parent::__construct($message);
        $this->errorType = $errorType;
        $this->originalException = $originalException;
    }
    
    public function getErrorType(): string
    {
        return $this->errorType;
    }
    
    public function getOriginalException(): ?PDOException
    {
        return $this->originalException;
    }
}

// Usage examples with error handling
try {
    $dbManager = new DatabaseManager($pdo, true); // Debug mode on
    
    // Example 1: Handle duplicate entry
    try {
        $dbManager->executeQuery(
            'INSERT INTO users (email, name) VALUES (?, ?)',
            ['[email protected]', 'John Doe'] // Assuming email already exists
        );
    } catch (DatabaseException $e) {
        if ($e->getErrorType() === 'DUPLICATE_ENTRY') {
            echo "User with this email already exists\n";
        } else {
            echo "Error creating user: " . $e->getMessage() . "\n";
        }
    }
    
    // Example 2: Transaction with error handling
    $result = $dbManager->executeTransaction(function($db) {
        // Create user
        $userStmt = $db->executeQuery(
            'INSERT INTO users (name, email) VALUES (?, ?)',
            ['Jane Doe', '[email protected]']
        );
        $userId = $db->getPdo()->lastInsertId();
        
        // Create user profile
        $db->executeQuery(
            'INSERT INTO user_profiles (user_id, bio) VALUES (?, ?)',
            [$userId, 'Software developer']
        );
        
        return $userId;
    });
    
    echo "User and profile created successfully with ID: $result\n";
    
} catch (DatabaseException $e) {
    echo "Application error: " . $e->getMessage() . "\n";
    echo "Error type: " . $e->getErrorType() . "\n";
} catch (Exception $e) {
    echo "Unexpected error: " . $e->getMessage() . "\n";
}
?>

Related Topics

For more database-related PHP topics:

• PHP Database Transactions - Transaction management and ACID properties
• PHP Query Builder - Building dynamic queries safely
• PHP Database Migrations - Schema versioning and updates
• PHP ORM Patterns - Object-relational mapping concepts
• PHP SQL Injection Prevention - Advanced security practices

Summary

PDO provides a robust, secure foundation for database programming in PHP:

Security: Built-in prepared statement support prevents SQL injection attacks effectively.

Portability: Database-agnostic interface allows code to work across different database systems.

Modern Interface: Object-oriented API with exception-based error handling.

Performance: Prepared statements reduce parsing overhead for repeated queries.

Feature Rich: Supports transactions, custom fetch modes, and advanced database features.

Best Practices: Always use prepared statements, implement proper error handling, and configure PDO securely.

PDO is the recommended approach for all new PHP database applications. Its combination of security, performance, and flexibility makes it ideal for both simple applications and complex enterprise systems.