PHP PDO and MySQL

Introduction to PDO and MySQL

PHP Data Objects (PDO) is a database abstraction layer providing a uniform interface for accessing databases in PHP. When combined with MySQL, one of the world's most popular relational database systems, PDO offers a powerful, secure, and flexible solution for database operations in web applications.

Understanding PDO with MySQL is essential for building secure, efficient database-driven applications that protect against SQL injection and provide reliable data management.

Why PDO and MySQL Matter

Security: PDO's prepared statements provide excellent protection against SQL injection attacks through proper parameter binding.

Database Abstraction: PDO allows switching between different database systems with minimal code changes.

Performance: Prepared statements can be reused, improving performance for repeated queries.

Error Handling: Comprehensive error reporting and exception handling for debugging and error management.

Advanced Features: Support for transactions, stored procedures, and complex data types.

MySQL Compatibility: Excellent integration with MySQL's features including stored procedures, views, and advanced data types.

Key PDO Features

Prepared Statements: Pre-compiled SQL statements that separate data from code for security and performance.

Parameter Binding: Safe way to include user data in SQL queries without concatenation.

Transaction Support: ACID-compliant transaction handling for data integrity.

Error Modes: Configurable error reporting including exceptions for robust error handling.

Fetch Modes: Multiple ways to retrieve data including arrays, objects, and custom classes.

PDO Connection and Configuration

Establishing MySQL Connections

<?php
/**
 * PDO MySQL Connection and Configuration
 * 
 * Demonstrates secure database connections, configuration options,
 * and connection management best practices.
 */

/**
 * Database connection manager with connection pooling
 */
class DatabaseManager
{
    private static ?PDO $connection = null;
    private static array $config = [];
    private static array $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_STRINGIFY_FETCHES => false,
        PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
    ];
    
    /**
     * Initialize database configuration
     */
    public static function configure(array $config): void
    {
        self::$config = array_merge([
            'host' => 'localhost',
            'port' => 3306,
            'database' => '',
            'username' => '',
            'password' => '',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'timezone' => '+00:00',
            'strict_mode' => true,
            'engine' => 'InnoDB'
        ], $config);
    }
    
    /**
     * Get database connection
     */
    public static function getConnection(): PDO
    {
        if (self::$connection === null) {
            self::connect();
        }
        
        return self::$connection;
    }
    
    /**
     * Establish database connection
     */
    private static function connect(): void
    {
        if (empty(self::$config)) {
            throw new RuntimeException('Database configuration not set');
        }
        
        $dsn = self::buildDSN();
        
        try {
            self::$connection = new PDO(
                $dsn,
                self::$config['username'],
                self::$config['password'],
                self::$options
            );
            
            // Set MySQL session variables
            self::configureMySQLSession();
            
        } catch (PDOException $e) {
            throw new RuntimeException('Database connection failed: ' . $e->getMessage());
        }
    }
    
    /**
     * Build MySQL DSN string
     */
    private static function buildDSN(): string
    {
        $dsn = "mysql:host={self::$config['host']}";
        $dsn .= ";port={self::$config['port']}";
        $dsn .= ";dbname={self::$config['database']}";
        $dsn .= ";charset={self::$config['charset']}";
        
        return $dsn;
    }
    
    /**
     * Configure MySQL session settings
     */
    private static function configureMySQLSession(): void
    {
        $statements = [
            "SET time_zone = '{self::$config['timezone']}'",
            "SET NAMES {self::$config['charset']} COLLATE {self::$config['collation']}"
        ];
        
        if (self::$config['strict_mode']) {
            $statements[] = "SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'";
        }
        
        foreach ($statements as $sql) {
            self::$connection->exec($sql);
        }
    }
    
    /**
     * Test database connection
     */
    public static function testConnection(): bool
    {
        try {
            $pdo = self::getConnection();
            $stmt = $pdo->query('SELECT 1');
            return $stmt !== false;
        } catch (Exception $e) {
            return false;
        }
    }
    
    /**
     * Get connection information
     */
    public static function getConnectionInfo(): array
    {
        $pdo = self::getConnection();
        
        return [
            'server_version' => $pdo->getAttribute(PDO::ATTR_SERVER_VERSION),
            'client_version' => $pdo->getAttribute(PDO::ATTR_CLIENT_VERSION),
            'connection_status' => $pdo->getAttribute(PDO::ATTR_CONNECTION_STATUS),
            'server_info' => $pdo->getAttribute(PDO::ATTR_SERVER_INFO),
            'autocommit' => $pdo->getAttribute(PDO::ATTR_AUTOCOMMIT)
        ];
    }
    
    /**
     * Close database connection
     */
    public static function disconnect(): void
    {
        self::$connection = null;
    }
    
    /**
     * Reconnect to database
     */
    public static function reconnect(): void
    {
        self::disconnect();
        self::connect();
    }
}

/**
 * Connection factory for multiple databases
 */
class ConnectionFactory
{
    private static array $connections = [];
    private static array $configs = [];
    
    /**
     * Register database configuration
     */
    public static function register(string $name, array $config): void
    {
        self::$configs[$name] = $config;
    }
    
    /**
     * Get database connection by name
     */
    public static function get(string $name = 'default'): PDO
    {
        if (!isset(self::$connections[$name])) {
            if (!isset(self::$configs[$name])) {
                throw new InvalidArgumentException("Database configuration '$name' not found");
            }
            
            self::$connections[$name] = self::createConnection(self::$configs[$name]);
        }
        
        return self::$connections[$name];
    }
    
    /**
     * Create new database connection
     */
    private static function createConnection(array $config): PDO
    {
        $dsn = "mysql:host={$config['host']};dbname={$config['database']};charset=utf8mb4";
        
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
        ];
        
        return new PDO($dsn, $config['username'], $config['password'], $options);
    }
}

// Usage examples
try {
    // Configure primary database
    DatabaseManager::configure([
        'host' => 'localhost',
        'database' => 'my_app',
        'username' => 'app_user',
        'password' => 'secure_password',
        'timezone' => '+00:00'
    ]);
    
    // Test connection
    if (DatabaseManager::testConnection()) {
        echo "Database connection successful!\n";
        
        // Get connection info
        $info = DatabaseManager::getConnectionInfo();
        echo "MySQL Version: " . $info['server_version'] . "\n";
        echo "Client Version: " . $info['client_version'] . "\n";
    }
    
    // Multiple database connections
    ConnectionFactory::register('main', [
        'host' => 'localhost',
        'database' => 'main_db',
        'username' => 'main_user',
        'password' => 'main_pass'
    ]);
    
    ConnectionFactory::register('analytics', [
        'host' => 'analytics-server',
        'database' => 'analytics_db',
        'username' => 'analytics_user',
        'password' => 'analytics_pass'
    ]);
    
    $mainDB = ConnectionFactory::get('main');
    $analyticsDB = ConnectionFactory::get('analytics');
    
} catch (Exception $e) {
    echo "Database error: " . $e->getMessage() . "\n";
}
?>

Prepared Statements and Parameter Binding

Secure Query Execution

<?php
/**
 * PDO Prepared Statements and Parameter Binding
 * 
 * Demonstrates secure query execution, parameter binding,
 * and various query patterns with MySQL.
 */

/**
 * Database query builder with prepared statements
 */
class QueryBuilder
{
    private PDO $pdo;
    private string $table = '';
    private array $conditions = [];
    private array $bindings = [];
    private string $orderBy = '';
    private string $groupBy = '';
    private ?int $limit = null;
    private ?int $offset = null;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }
    
    /**
     * Set table name
     */
    public function table(string $table): self
    {
        $this->table = $table;
        return $this;
    }
    
    /**
     * Add WHERE condition
     */
    public function where(string $column, string $operator, $value): self
    {
        $placeholder = ':' . $column . '_' . count($this->bindings);
        $this->conditions[] = "$column $operator $placeholder";
        $this->bindings[$placeholder] = $value;
        
        return $this;
    }
    
    /**
     * Add WHERE IN condition
     */
    public function whereIn(string $column, array $values): self
    {
        $placeholders = [];
        foreach ($values as $i => $value) {
            $placeholder = ":{$column}_in_{$i}";
            $placeholders[] = $placeholder;
            $this->bindings[$placeholder] = $value;
        }
        
        $this->conditions[] = "$column IN (" . implode(',', $placeholders) . ")";
        return $this;
    }
    
    /**
     * Add WHERE BETWEEN condition
     */
    public function whereBetween(string $column, $start, $end): self
    {
        $startPlaceholder = ":{$column}_start";
        $endPlaceholder = ":{$column}_end";
        
        $this->conditions[] = "$column BETWEEN $startPlaceholder AND $endPlaceholder";
        $this->bindings[$startPlaceholder] = $start;
        $this->bindings[$endPlaceholder] = $end;
        
        return $this;
    }
    
    /**
     * Add WHERE LIKE condition
     */
    public function whereLike(string $column, string $pattern): self
    {
        $placeholder = ":{$column}_like";
        $this->conditions[] = "$column LIKE $placeholder";
        $this->bindings[$placeholder] = $pattern;
        
        return $this;
    }
    
    /**
     * Add ORDER BY clause
     */
    public function orderBy(string $column, string $direction = 'ASC'): self
    {
        $this->orderBy = "ORDER BY $column $direction";
        return $this;
    }
    
    /**
     * Add GROUP BY clause
     */
    public function groupBy(string $column): self
    {
        $this->groupBy = "GROUP BY $column";
        return $this;
    }
    
    /**
     * Add LIMIT clause
     */
    public function limit(int $limit, ?int $offset = null): self
    {
        $this->limit = $limit;
        $this->offset = $offset;
        return $this;
    }
    
    /**
     * Execute SELECT query
     */
    public function get(array $columns = ['*']): array
    {
        $columnList = implode(', ', $columns);
        $sql = "SELECT $columnList FROM {$this->table}";
        
        if (!empty($this->conditions)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->conditions);
        }
        
        if ($this->groupBy) {
            $sql .= ' ' . $this->groupBy;
        }
        
        if ($this->orderBy) {
            $sql .= ' ' . $this->orderBy;
        }
        
        if ($this->limit) {
            $sql .= " LIMIT {$this->limit}";
            if ($this->offset) {
                $sql .= " OFFSET {$this->offset}";
            }
        }
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->bindings);
        
        return $stmt->fetchAll();
    }
    
    /**
     * Execute SELECT query and return first result
     */
    public function first(array $columns = ['*']): ?array
    {
        $this->limit(1);
        $results = $this->get($columns);
        return $results[0] ?? null;
    }
    
    /**
     * Get count of records
     */
    public function count(): int
    {
        $sql = "SELECT COUNT(*) FROM {$this->table}";
        
        if (!empty($this->conditions)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->conditions);
        }
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->bindings);
        
        return (int) $stmt->fetchColumn();
    }
    
    /**
     * Insert record
     */
    public function insert(array $data): int
    {
        $columns = array_keys($data);
        $placeholders = array_map(fn($col) => ":$col", $columns);
        
        $sql = "INSERT INTO {$this->table} (" . implode(', ', $columns) . ") VALUES (" . implode(', ', $placeholders) . ")";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);
        
        return (int) $this->pdo->lastInsertId();
    }
    
    /**
     * Update records
     */
    public function update(array $data): int
    {
        $setParts = [];
        foreach ($data as $column => $value) {
            $setParts[] = "$column = :update_$column";
            $this->bindings[":update_$column"] = $value;
        }
        
        $sql = "UPDATE {$this->table} SET " . implode(', ', $setParts);
        
        if (!empty($this->conditions)) {
            $sql .= ' WHERE ' . implode(' AND ', $this->conditions);
        }
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->bindings);
        
        return $stmt->rowCount();
    }
    
    /**
     * Delete records
     */
    public function delete(): int
    {
        if (empty($this->conditions)) {
            throw new RuntimeException('DELETE queries must have WHERE conditions');
        }
        
        $sql = "DELETE FROM {$this->table} WHERE " . implode(' AND ', $this->conditions);
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->bindings);
        
        return $stmt->rowCount();
    }
    
    /**
     * Reset query builder
     */
    public function reset(): self
    {
        $this->conditions = [];
        $this->bindings = [];
        $this->orderBy = '';
        $this->groupBy = '';
        $this->limit = null;
        $this->offset = null;
        
        return $this;
    }
}

/**
 * User repository with prepared statements
 */
class UserRepository
{
    private PDO $pdo;
    private QueryBuilder $query;
    
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
        $this->query = new QueryBuilder($pdo);
    }
    
    /**
     * Find user by ID
     */
    public function findById(int $id): ?array
    {
        return $this->query->table('users')
            ->where('id', '=', $id)
            ->first();
    }
    
    /**
     * Find user by email
     */
    public function findByEmail(string $email): ?array
    {
        return $this->query->reset()
            ->table('users')
            ->where('email', '=', $email)
            ->first();
    }
    
    /**
     * Find users by role
     */
    public function findByRole(string $role): array
    {
        return $this->query->reset()
            ->table('users')
            ->where('role', '=', $role)
            ->where('active', '=', 1)
            ->orderBy('name')
            ->get();
    }
    
    /**
     * Search users by name
     */
    public function searchByName(string $search): array
    {
        return $this->query->reset()
            ->table('users')
            ->whereLike('name', "%$search%")
            ->orderBy('name')
            ->get();
    }
    
    /**
     * Get paginated users
     */
    public function getPaginated(int $page, int $perPage): array
    {
        $offset = ($page - 1) * $perPage;
        
        $users = $this->query->reset()
            ->table('users')
            ->orderBy('created_at', 'DESC')
            ->limit($perPage, $offset)
            ->get();
        
        $total = $this->query->reset()
            ->table('users')
            ->count();
        
        return [
            'data' => $users,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'pages' => ceil($total / $perPage)
        ];
    }
    
    /**
     * Create new user
     */
    public function create(array $userData): int
    {
        $userData['created_at'] = date('Y-m-d H:i:s');
        $userData['updated_at'] = date('Y-m-d H:i:s');
        
        return $this->query->reset()
            ->table('users')
            ->insert($userData);
    }
    
    /**
     * Update user
     */
    public function update(int $id, array $userData): bool
    {
        $userData['updated_at'] = date('Y-m-d H:i:s');
        
        $affected = $this->query->reset()
            ->table('users')
            ->where('id', '=', $id)
            ->update($userData);
        
        return $affected > 0;
    }
    
    /**
     * Delete user
     */
    public function delete(int $id): bool
    {
        $affected = $this->query->reset()
            ->table('users')
            ->where('id', '=', $id)
            ->delete();
        
        return $affected > 0;
    }
    
    /**
     * Soft delete user
     */
    public function softDelete(int $id): bool
    {
        return $this->update($id, [
            'deleted_at' => date('Y-m-d H:i:s'),
            'active' => 0
        ]);
    }
    
    /**
     * Batch insert users
     */
    public function batchInsert(array $users): bool
    {
        if (empty($users)) {
            return false;
        }
        
        $columns = array_keys($users[0]);
        $placeholders = '(' . implode(',', array_map(fn($col) => ":$col", $columns)) . ')';
        
        $sql = "INSERT INTO users (" . implode(', ', $columns) . ") VALUES ";
        $values = [];
        $bindings = [];
        
        foreach ($users as $index => $user) {
            $rowPlaceholders = [];
            foreach ($columns as $column) {
                $placeholder = ":{$column}_{$index}";
                $rowPlaceholders[] = $placeholder;
                $bindings[$placeholder] = $user[$column];
            }
            $values[] = '(' . implode(',', $rowPlaceholders) . ')';
        }
        
        $sql .= implode(',', $values);
        
        $stmt = $this->pdo->prepare($sql);
        return $stmt->execute($bindings);
    }
    
    /**
     * Get user statistics
     */
    public function getStatistics(): array
    {
        $sql = "
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN active = 1 THEN 1 ELSE 0 END) as active,
                SUM(CASE WHEN role = 'admin' THEN 1 ELSE 0 END) as admins,
                AVG(TIMESTAMPDIFF(YEAR, created_at, NOW())) as avg_age_years
            FROM users
        ";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        
        return $stmt->fetch();
    }
}

// Usage examples
try {
    $pdo = DatabaseManager::getConnection();
    $userRepo = new UserRepository($pdo);
    
    // Find user by ID
    $user = $userRepo->findById(1);
    if ($user) {
        echo "Found user: " . $user['name'] . "\n";
    }
    
    // Search users
    $searchResults = $userRepo->searchByName('john');
    echo "Found " . count($searchResults) . " users matching 'john'\n";
    
    // Get paginated results
    $page = $userRepo->getPaginated(1, 10);
    echo "Page 1 of {$page['pages']} ({$page['total']} total users)\n";
    
    // Create new user
    $newUserId = $userRepo->create([
        'name' => 'Jane Doe',
        'email' => '[email protected]',
        'role' => 'user',
        'active' => 1
    ]);
    echo "Created user with ID: $newUserId\n";
    
    // Update user
    $updated = $userRepo->update($newUserId, [
        'name' => 'Jane Smith',
        'role' => 'admin'
    ]);
    echo "User updated: " . ($updated ? 'Yes' : 'No') . "\n";
    
    // Get statistics
    $stats = $userRepo->getStatistics();
    echo "User Statistics:\n";
    echo "- Total: {$stats['total']}\n";
    echo "- Active: {$stats['active']}\n";
    echo "- Admins: {$stats['admins']}\n";
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>

Error Handling and Debugging

Comprehensive Error Management

<?php
/**
 * PDO Error Handling and Debugging
 * 
 * Demonstrates error handling, debugging techniques,
 * and logging for PDO MySQL operations.
 */

/**
 * Database error handler with logging
 */
class DatabaseErrorHandler
{
    private string $logFile;
    private bool $debugMode;
    
    public function __construct(string $logFile = 'database.log', bool $debugMode = false)
    {
        $this->logFile = $logFile;
        $this->debugMode = $debugMode;
    }
    
    /**
     * Handle PDO exceptions
     */
    public function handlePDOException(PDOException $e, string $sql = '', array $bindings = []): void
    {
        $error = [
            'timestamp' => date('Y-m-d H:i:s'),
            'error_code' => $e->getCode(),
            'error_message' => $e->getMessage(),
            'sql_state' => $e->errorInfo[0] ?? 'Unknown',
            'driver_code' => $e->errorInfo[1] ?? 'Unknown',
            'driver_message' => $e->errorInfo[2] ?? 'Unknown',
            'file' => $e->getFile(),
            'line' => $e->getLine(),
            'sql' => $sql,
            'bindings' => $bindings
        ];
        
        $this->logError($error);
        
        if ($this->debugMode) {
            $this->displayError($error);
        }
    }
    
    /**
     * Log error to file
     */
    private function logError(array $error): void
    {
        $logEntry = sprintf(
            "[%s] PDO Error %s: %s | SQL: %s | Bindings: %s | File: %s:%d\n",
            $error['timestamp'],
            $error['error_code'],
            $error['error_message'],
            $error['sql'],
            json_encode($error['bindings']),
            $error['file'],
            $error['line']
        );
        
        file_put_contents($this->logFile, $logEntry, FILE_APPEND | LOCK_EX);
    }
    
    /**
     * Display error in debug mode
     */
    private function displayError(array $error): void
    {
        echo "<div style='background: #ffebee; border: 1px solid #f44336; padding: 15px; margin: 10px; border-radius: 4px;'>";
        echo "<h3 style='color: #d32f2f; margin: 0 0 10px 0;'>Database Error</h3>";
        echo "<p><strong>Error:</strong> {$error['error_message']}</p>";
        echo "<p><strong>Code:</strong> {$error['error_code']}</p>";
        echo "<p><strong>SQL State:</strong> {$error['sql_state']}</p>";
        
        if ($error['sql']) {
            echo "<p><strong>SQL:</strong> <code>{$error['sql']}</code></p>";
        }
        
        if (!empty($error['bindings'])) {
            echo "<p><strong>Bindings:</strong> <code>" . json_encode($error['bindings']) . "</code></p>";
        }
        
        echo "<p><strong>Location:</strong> {$error['file']}:{$error['line']}</p>";
        echo "</div>";
    }
    
    /**
     * Check MySQL connection health
     */
    public function checkConnectionHealth(PDO $pdo): array
    {
        $health = [
            'connected' => false,
            'server_version' => null,
            'uptime' => null,
            'threads_connected' => null,
            'queries_per_second' => null,
            'errors' => []
        ];
        
        try {
            // Test basic connectivity
            $stmt = $pdo->query('SELECT 1');
            $health['connected'] = true;
            
            // Get server version
            $health['server_version'] = $pdo->getAttribute(PDO::ATTR_SERVER_VERSION);
            
            // Get server status
            $stmt = $pdo->query('SHOW STATUS WHERE Variable_name IN ("Uptime", "Threads_connected", "Queries")');
            $status = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
            
            $health['uptime'] = $status['Uptime'] ?? null;
            $health['threads_connected'] = $status['Threads_connected'] ?? null;
            
            if (isset($status['Queries']) && $health['uptime']) {
                $health['queries_per_second'] = round($status['Queries'] / $health['uptime'], 2);
            }
            
        } catch (PDOException $e) {
            $health['errors'][] = $e->getMessage();
        }
        
        return $health;
    }
}

/**
 * Query profiler for performance monitoring
 */
class QueryProfiler
{
    private array $queries = [];
    private bool $enabled = true;
    
    /**
     * Start query profiling
     */
    public function startQuery(string $sql, array $bindings = []): string
    {
        if (!$this->enabled) {
            return '';
        }
        
        $queryId = uniqid();
        $this->queries[$queryId] = [
            'sql' => $sql,
            'bindings' => $bindings,
            'start_time' => microtime(true),
            'start_memory' => memory_get_usage(true),
            'end_time' => null,
            'end_memory' => null,
            'duration' => null,
            'memory_delta' => null
        ];
        
        return $queryId;
    }
    
    /**
     * End query profiling
     */
    public function endQuery(string $queryId): void
    {
        if (!$this->enabled || !isset($this->queries[$queryId])) {
            return;
        }
        
        $endTime = microtime(true);
        $endMemory = memory_get_usage(true);
        
        $this->queries[$queryId]['end_time'] = $endTime;
        $this->queries[$queryId]['end_memory'] = $endMemory;
        $this->queries[$queryId]['duration'] = $endTime - $this->queries[$queryId]['start_time'];
        $this->queries[$queryId]['memory_delta'] = $endMemory - $this->queries[$queryId]['start_memory'];
    }
    
    /**
     * Get profiling results
     */
    public function getResults(): array
    {
        $totalQueries = count($this->queries);
        $totalTime = array_sum(array_column($this->queries, 'duration'));
        $avgTime = $totalQueries > 0 ? $totalTime / $totalQueries : 0;
        
        $slowQueries = array_filter($this->queries, fn($q) => $q['duration'] > 0.1);
        
        return [
            'total_queries' => $totalQueries,
            'total_time' => round($totalTime, 4),
            'average_time' => round($avgTime, 4),
            'slow_queries' => count($slowQueries),
            'queries' => $this->queries
        ];
    }
    
    /**
     * Reset profiler
     */
    public function reset(): void
    {
        $this->queries = [];
    }
    
    /**
     * Enable/disable profiling
     */
    public function setEnabled(bool $enabled): void
    {
        $this->enabled = $enabled;
    }
}

/**
 * Enhanced PDO wrapper with error handling and profiling
 */
class SafePDO
{
    private PDO $pdo;
    private DatabaseErrorHandler $errorHandler;
    private QueryProfiler $profiler;
    
    public function __construct(PDO $pdo, bool $debugMode = false)
    {
        $this->pdo = $pdo;
        $this->errorHandler = new DatabaseErrorHandler('database.log', $debugMode);
        $this->profiler = new QueryProfiler();
    }
    
    /**
     * Prepare and execute query safely
     */
    public function execute(string $sql, array $bindings = []): PDOStatement
    {
        $queryId = $this->profiler->startQuery($sql, $bindings);
        
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($bindings);
            
            $this->profiler->endQuery($queryId);
            return $stmt;
            
        } catch (PDOException $e) {
            $this->profiler->endQuery($queryId);
            $this->errorHandler->handlePDOException($e, $sql, $bindings);
            throw $e;
        }
    }
    
    /**
     * Execute query and fetch all results
     */
    public function fetchAll(string $sql, array $bindings = []): array
    {
        $stmt = $this->execute($sql, $bindings);
        return $stmt->fetchAll();
    }
    
    /**
     * Execute query and fetch single row
     */
    public function fetchOne(string $sql, array $bindings = []): ?array
    {
        $stmt = $this->execute($sql, $bindings);
        $result = $stmt->fetch();
        return $result ?: null;
    }
    
    /**
     * Execute query and fetch single column
     */
    public function fetchColumn(string $sql, array $bindings = []): mixed
    {
        $stmt = $this->execute($sql, $bindings);
        return $stmt->fetchColumn();
    }
    
    /**
     * Begin transaction
     */
    public function beginTransaction(): bool
    {
        try {
            return $this->pdo->beginTransaction();
        } catch (PDOException $e) {
            $this->errorHandler->handlePDOException($e);
            throw $e;
        }
    }
    
    /**
     * Commit transaction
     */
    public function commit(): bool
    {
        try {
            return $this->pdo->commit();
        } catch (PDOException $e) {
            $this->errorHandler->handlePDOException($e);
            throw $e;
        }
    }
    
    /**
     * Rollback transaction
     */
    public function rollback(): bool
    {
        try {
            return $this->pdo->rollback();
        } catch (PDOException $e) {
            $this->errorHandler->handlePDOException($e);
            throw $e;
        }
    }
    
    /**
     * Get profiling results
     */
    public function getProfilingResults(): array
    {
        return $this->profiler->getResults();
    }
    
    /**
     * Check database health
     */
    public function getHealthStatus(): array
    {
        return $this->errorHandler->checkConnectionHealth($this->pdo);
    }
    
    /**
     * Get underlying PDO instance
     */
    public function getPDO(): PDO
    {
        return $this->pdo;
    }
}

// Usage examples
try {
    $pdo = DatabaseManager::getConnection();
    $safePDO = new SafePDO($pdo, true);
    
    // Execute queries with profiling and error handling
    $users = $safePDO->fetchAll('SELECT * FROM users WHERE active = :active', ['active' => 1]);
    echo "Found " . count($users) . " active users\n";
    
    // Test error handling
    try {
        $safePDO->fetchAll('SELECT * FROM non_existent_table');
    } catch (PDOException $e) {
        echo "Caught and handled database error\n";
    }
    
    // Check database health
    $health = $safePDO->getHealthStatus();
    echo "Database connected: " . ($health['connected'] ? 'Yes' : 'No') . "\n";
    echo "MySQL version: " . ($health['server_version'] ?? 'Unknown') . "\n";
    
    // Get profiling results
    $profile = $safePDO->getProfilingResults();
    echo "Executed {$profile['total_queries']} queries in {$profile['total_time']}s\n";
    echo "Average query time: {$profile['average_time']}s\n";
    
} catch (Exception $e) {
    echo "Application error: " . $e->getMessage() . "\n";
}
?>

Related Topics

For more PHP database topics:

• PHP CRUD Operations - Create, Read, Update, Delete operations
• PHP Prepared Statements - Advanced prepared statement usage
• PHP Transactions - Database transaction management
• PHP Query Builder - Advanced query building
• PHP Database Migrations - Schema management

Summary

PDO with MySQL provides a robust foundation for PHP database operations:

Secure Connections: Proper configuration and connection management prevent security vulnerabilities and ensure reliable database access.

Prepared Statements: Parameter binding protects against SQL injection while improving performance through statement reuse.

Error Handling: Comprehensive error management with logging and debugging capabilities ensures robust application behavior.

Query Building: Structured query builders provide clean, maintainable database operations with proper parameter binding.

Performance Monitoring: Query profiling and health monitoring enable optimization and troubleshooting of database operations.

Transaction Support: ACID-compliant transaction handling ensures data integrity in complex operations.

Best Practices: Following established patterns for connection management, error handling, and query execution creates maintainable, secure applications.

Mastering PDO with MySQL enables you to build secure, efficient database-driven PHP applications with professional error handling and performance monitoring capabilities.