Connecting to MySQL with PHP

Duration: 75 minutes

Lesson 1 of 8

Learning Objectives

Understand MySQL connection fundamentals
Configure database connections properly
Implement connection pooling strategies
Set up character encoding correctly
Configure SSL/TLS secure connections
Handle connection errors gracefully
Manage persistent vs non-persistent connections
Apply connection best practices

Understanding Database Connections

Establishing a reliable connection to MySQL is the foundation of any PHP database application. Let's master every aspect! 🔌

💡

Connection Fundamentals

Connection String:Specifies server, database, credentials
Character Encoding:Must match database encoding (utf8mb4 recommended)
Timeout Settings:Prevent hanging connections
Resource Management:Connections consume server resources
Security:Never hardcode credentials, use environment variables
Error Handling:Always handle connection failures gracefully

Basic Connection Methods

graph TB APP[PHP Application] --> METHOD{Connection Method} METHOD --> PDO[PDO Connection] METHOD --> MYSQLI[MySQLi Connection] METHOD --> DEPRECATED[mysql_* Deprecated] PDO --> CONFIG1[DSN Configuration] MYSQLI --> CONFIG2[Host Configuration] CONFIG1 --> CONNECT1[Establish Connection] CONFIG2 --> CONNECT2[Establish Connection] CONNECT1 --> DB[(MySQL Database)] CONNECT2 --> DB style APP fill:#dbeafe style PDO fill:#dcfce7 style MYSQLI fill:#fef3c7 style DEPRECATED fill:#fee2e2 style DB fill:#e9d5ff

Connection Implementation Examples

<?php
// ========================================
// METHOD 1: PDO CONNECTION
// ========================================

/**
 * PDO Connection with comprehensive configuration
 */
class PDOConnection {
    private $pdo;
    private $config;
    
    public function __construct() {
        $this->loadConfiguration();
        $this->connect();
    }
    
    /**
     * Load configuration from environment or config file
     */
    private function loadConfiguration() {
        // Best practice: Use environment variables
        $this->config = [
            'driver'   => $_ENV['DB_DRIVER'] ?? 'mysql',
            'host'     => $_ENV['DB_HOST'] ?? 'localhost',
            'port'     => $_ENV['DB_PORT'] ?? 3306,
            'database' => $_ENV['DB_DATABASE'] ?? 'testdb',
            'username' => $_ENV['DB_USERNAME'] ?? 'root',
            'password' => $_ENV['DB_PASSWORD'] ?? '',
            'charset'  => $_ENV['DB_CHARSET'] ?? 'utf8mb4',
            'collation' => $_ENV['DB_COLLATION'] ?? 'utf8mb4_unicode_ci',
            'options'  => [
                // Error handling
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                
                // Fetch mode
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                
                // Prepare emulation (false for real prepared statements)
                PDO::ATTR_EMULATE_PREPARES => false,
                
                // Connection timeout (seconds)
                PDO::ATTR_TIMEOUT => 5,
                
                // Persistent connection
                PDO::ATTR_PERSISTENT => false,
                
                // MySQL specific: Use buffered queries
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
                
                // MySQL specific: Enable compression
                PDO::MYSQL_ATTR_COMPRESS => true,
                
                // MySQL specific: Init command
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
            ]
        ];
    }
    
    /**
     * Establish database connection
     */
    private function connect() {
        try {
            // Build DSN (Data Source Name)
            $dsn = $this->buildDSN();
            
            // Create PDO instance
            $this->pdo = new PDO(
                $dsn,
                $this->config['username'],
                $this->config['password'],
                $this->config['options']
            );
            
            // Additional configuration after connection
            $this->postConnectionSetup();
            
            echo "✅ PDO Connection established successfully\n";
            
        } catch (PDOException $e) {
            $this->handleConnectionError($e);
        }
    }
    
    /**
     * Build DSN string
     */
    private function buildDSN() {
        $dsn = "{$this->config['driver']}:";
        
        // Add connection parameters
        $params = [];
        
        // Unix socket or TCP/IP
        if (isset($this->config['unix_socket'])) {
            $params[] = "unix_socket={$this->config['unix_socket']}";
        } else {
            $params[] = "host={$this->config['host']}";
            $params[] = "port={$this->config['port']}";
        }
        
        $params[] = "dbname={$this->config['database']}";
        $params[] = "charset={$this->config['charset']}";
        
        $dsn .= implode(';', $params);
        
        return $dsn;
    }
    
    /**
     * Post-connection configuration
     */
    private function postConnectionSetup() {
        // Set timezone
        $this->pdo->exec("SET time_zone = '+00:00'");
        
        // Set SQL mode for strict data handling
        $this->pdo->exec("SET sql_mode = 'STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'");
        
        // Set transaction isolation level
        $this->pdo->exec("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED");
    }
    
    /**
     * Handle connection errors
     */
    private function handleConnectionError(PDOException $e) {
        $errorInfo = [
            'message' => $e->getMessage(),
            'code' => $e->getCode(),
            'timestamp' => date('Y-m-d H:i:s'),
            'host' => $this->config['host'],
            'database' => $this->config['database']
        ];
        
        // Log error (don't expose passwords)
        error_log("PDO Connection Error: " . json_encode($errorInfo));
        
        // User-friendly error message
        if ($_ENV['APP_ENV'] === 'production') {
            die("Database connection error. Please try again later.");
        } else {
            die("Connection failed: " . $e->getMessage());
        }
    }
    
    /**
     * Get PDO instance
     */
    public function getConnection() {
        return $this->pdo;
    }
    
    /**
     * Test connection
     */
    public function testConnection() {
        try {
            $stmt = $this->pdo->query('SELECT 1');
            return $stmt !== false;
        } catch (PDOException $e) {
            return false;
        }
    }
    
    /**
     * Get connection info
     */
    public function getConnectionInfo() {
        return [
            'server_info' => $this->pdo->getAttribute(PDO::ATTR_SERVER_INFO),
            'server_version' => $this->pdo->getAttribute(PDO::ATTR_SERVER_VERSION),
            'client_version' => $this->pdo->getAttribute(PDO::ATTR_CLIENT_VERSION),
            'connection_status' => $this->pdo->getAttribute(PDO::ATTR_CONNECTION_STATUS),
            'driver_name' => $this->pdo->getAttribute(PDO::ATTR_DRIVER_NAME),
            'persistent' => $this->pdo->getAttribute(PDO::ATTR_PERSISTENT)
        ];
    }
}

// ========================================
// METHOD 2: MYSQLI CONNECTION
// ========================================

/**
 * MySQLi Connection with comprehensive configuration
 */
class MySQLiConnection {
    private $mysqli;
    private $config;
    
    public function __construct() {
        $this->loadConfiguration();
        $this->connect();
    }
    
    /**
     * Load configuration
     */
    private function loadConfiguration() {
        $this->config = [
            'host' => $_ENV['DB_HOST'] ?? 'localhost',
            'username' => $_ENV['DB_USERNAME'] ?? 'root',
            'password' => $_ENV['DB_PASSWORD'] ?? '',
            'database' => $_ENV['DB_DATABASE'] ?? 'testdb',
            'port' => $_ENV['DB_PORT'] ?? 3306,
            'socket' => $_ENV['DB_SOCKET'] ?? null,
            'charset' => $_ENV['DB_CHARSET'] ?? 'utf8mb4'
        ];
    }
    
    /**
     * Establish MySQLi connection
     */
    private function connect() {
        // Enable error reporting
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        
        try {
            // Initialize MySQLi
            $this->mysqli = new mysqli();
            
            // Set connection timeout
            $this->mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);
            
            // Enable compression
            $this->mysqli->options(MYSQLI_CLIENT_COMPRESS, true);
            
            // SSL Configuration (if needed)
            if (isset($this->config['ssl'])) {
                $this->configureSLL();
            }
            
            // Establish connection
            $this->mysqli->real_connect(
                $this->config['host'],
                $this->config['username'],
                $this->config['password'],
                $this->config['database'],
                $this->config['port'],
                $this->config['socket']
            );
            
            // Set charset
            $this->mysqli->set_charset($this->config['charset']);
            
            // Post-connection setup
            $this->postConnectionSetup();
            
            echo "✅ MySQLi Connection established successfully\n";
            
        } catch (mysqli_sql_exception $e) {
            $this->handleConnectionError($e);
        }
    }
    
    /**
     * Configure SSL
     */
    private function configureSLL() {
        $this->mysqli->ssl_set(
            $this->config['ssl']['key'] ?? null,
            $this->config['ssl']['cert'] ?? null,
            $this->config['ssl']['ca'] ?? null,
            $this->config['ssl']['capath'] ?? null,
            $this->config['ssl']['cipher'] ?? null
        );
    }
    
    /**
     * Post-connection setup
     */
    private function postConnectionSetup() {
        // Set timezone
        $this->mysqli->query("SET time_zone = '+00:00'");
        
        // Set SQL mode
        $this->mysqli->query("SET sql_mode = 'STRICT_ALL_TABLES'");
        
        // Set names (backup for charset)
        $this->mysqli->query("SET NAMES '{$this->config['charset']}'");
    }
    
    /**
     * Handle connection errors
     */
    private function handleConnectionError($e) {
        $errorInfo = [
            'message' => $e->getMessage(),
            'code' => $e->getCode(),
            'timestamp' => date('Y-m-d H:i:s')
        ];
        
        error_log("MySQLi Connection Error: " . json_encode($errorInfo));
        
        if ($_ENV['APP_ENV'] === 'production') {
            die("Database connection error. Please try again later.");
        } else {
            die("Connection failed: " . $e->getMessage());
        }
    }
    
    /**
     * Get MySQLi instance
     */
    public function getConnection() {
        return $this->mysqli;
    }
    
    /**
     * Get connection stats
     */
    public function getStats() {
        return [
            'host_info' => $this->mysqli->host_info,
            'protocol_version' => $this->mysqli->protocol_version,
            'server_info' => $this->mysqli->server_info,
            'server_version' => $this->mysqli->server_version,
            'client_info' => $this->mysqli->client_info,
            'client_version' => $this->mysqli->client_version,
            'thread_id' => $this->mysqli->thread_id,
            'warning_count' => $this->mysqli->warning_count,
            'stat' => $this->mysqli->stat()
        ];
    }
}

// ========================================
// CONNECTION MANAGER (Singleton Pattern)
// ========================================

class ConnectionManager {
    private static $instances = [];
    private $connections = [];
    
    private function __construct() {}
    
    /**
     * Get singleton instance
     */
    public static function getInstance() {
        $class = static::class;
        if (!isset(self::$instances[$class])) {
            self::$instances[$class] = new static();
        }
        return self::$instances[$class];
    }
    
    /**
     * Get or create connection
     */
    public function getConnection($name = 'default', $type = 'pdo') {
        $key = "{$name}_{$type}";
        
        if (!isset($this->connections[$key])) {
            $this->connections[$key] = $this->createConnection($type);
        }
        
        return $this->connections[$key];
    }
    
    /**
     * Create new connection
     */
    private function createConnection($type) {
        switch ($type) {
            case 'pdo':
                $conn = new PDOConnection();
                return $conn->getConnection();
                
            case 'mysqli':
                $conn = new MySQLiConnection();
                return $conn->getConnection();
                
            default:
                throw new Exception("Unknown connection type: {$type}");
        }
    }
    
    /**
     * Close all connections
     */
    public function closeAll() {
        foreach ($this->connections as $key => $connection) {
            if ($connection instanceof PDO) {
                $connection = null;
            } elseif ($connection instanceof mysqli) {
                $connection->close();
            }
            unset($this->connections[$key]);
        }
    }
    
    // Prevent cloning
    private function __clone() {}
    
    // Prevent unserialization
    public function __wakeup() {
        throw new Exception("Cannot unserialize singleton");
    }
}

// ========================================
// USAGE EXAMPLES
// ========================================

// Example 1: Direct PDO connection
$pdoConn = new PDOConnection();
$pdo = $pdoConn->getConnection();
$stmt = $pdo->query("SELECT VERSION()");
echo "MySQL Version: " . $stmt->fetchColumn() . "\n";

// Example 2: Direct MySQLi connection
$mysqliConn = new MySQLiConnection();
$mysqli = $mysqliConn->getConnection();
$result = $mysqli->query("SELECT VERSION()");
$row = $result->fetch_row();
echo "MySQL Version: " . $row[0] . "\n";

// Example 3: Using Connection Manager
$manager = ConnectionManager::getInstance();
$pdo = $manager->getConnection('default', 'pdo');
$mysqli = $manager->getConnection('default', 'mysqli');

// Example 4: Testing connections
$pdoConn = new PDOConnection();
if ($pdoConn->testConnection()) {
    echo "PDO connection is active\n";
    print_r($pdoConn->getConnectionInfo());
}

$mysqliConn = new MySQLiConnection();
echo "MySQLi connection stats:\n";
print_r($mysqliConn->getStats());

Connection Configuration Best Practices

Environment-Based Configuration

<?php
// ========================================
// CONFIGURATION MANAGEMENT
// ========================================

/**
 * Database configuration class
 */
class DatabaseConfig {
    private $environments = [];
    private $currentEnv;
    
    public function __construct() {
        $this->currentEnv = $_ENV['APP_ENV'] ?? 'development';
        $this->loadConfigurations();
    }
    
    /**
     * Load environment-specific configurations
     */
    private function loadConfigurations() {
        // Development configuration
        $this->environments['development'] = [
            'driver' => 'mysql',
            'host' => 'localhost',
            'port' => 3306,
            'database' => 'dev_database',
            'username' => 'dev_user',
            'password' => 'dev_password',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => 'dev_',
            'strict' => true,
            'engine' => 'InnoDB',
            'options' => [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_PERSISTENT => false
            ]
        ];
        
        // Testing configuration
        $this->environments['testing'] = [
            'driver' => 'mysql',
            'host' => 'localhost',
            'port' => 3307,
            'database' => 'test_database',
            'username' => 'test_user',
            'password' => 'test_password',
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => 'test_',
            'strict' => true,
            'engine' => 'InnoDB',
            'options' => [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
            ]
        ];
        
        // Production configuration
        $this->environments['production'] = [
            'driver' => 'mysql',
            'host' => $_ENV['DB_HOST'],
            'port' => $_ENV['DB_PORT'] ?? 3306,
            'database' => $_ENV['DB_DATABASE'],
            'username' => $_ENV['DB_USERNAME'],
            'password' => $_ENV['DB_PASSWORD'],
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => 'InnoDB',
            'options' => [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_PERSISTENT => true,
                PDO::ATTR_TIMEOUT => 10,
                PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
                PDO::MYSQL_ATTR_COMPRESS => true
            ],
            'ssl' => [
                'ca' => '/path/to/ca-cert.pem',
                'verify' => true
            ]
        ];
    }
    
    /**
     * Get current environment configuration
     */
    public function getConfig() {
        if (!isset($this->environments[$this->currentEnv])) {
            throw new Exception("Unknown environment: {$this->currentEnv}");
        }
        
        return $this->environments[$this->currentEnv];
    }
    
    /**
     * Get specific configuration value
     */
    public function get($key, $default = null) {
        $config = $this->getConfig();
        return $config[$key] ?? $default;
    }
}

// ========================================
// CONNECTION POOLING
// ========================================

/**
 * Connection pool implementation
 */
class ConnectionPool {
    private $pool = [];
    private $maxConnections = 10;
    private $minConnections = 2;
    private $currentConnections = 0;
    private $config;
    
    public function __construct(DatabaseConfig $config) {
        $this->config = $config->getConfig();
        $this->initializePool();
    }
    
    /**
     * Initialize connection pool
     */
    private function initializePool() {
        // Create minimum connections
        for ($i = 0; $i < $this->minConnections; $i++) {
            $this->createConnection();
        }
    }
    
    /**
     * Create new connection
     */
    private function createConnection() {
        if ($this->currentConnections >= $this->maxConnections) {
            throw new Exception("Maximum connection limit reached");
        }
        
        $dsn = "mysql:host={$this->config['host']};port={$this->config['port']};dbname={$this->config['database']};charset={$this->config['charset']}";
        
        $pdo = new PDO(
            $dsn,
            $this->config['username'],
            $this->config['password'],
            $this->config['options']
        );
        
        $connectionId = uniqid('conn_');
        
        $this->pool[$connectionId] = [
            'connection' => $pdo,
            'in_use' => false,
            'created_at' => time(),
            'last_used' => time()
        ];
        
        $this->currentConnections++;
        
        return $connectionId;
    }
    
    /**
     * Get available connection
     */
    public function getConnection() {
        // Find available connection
        foreach ($this->pool as $id => &$conn) {
            if (!$conn['in_use']) {
                $conn['in_use'] = true;
                $conn['last_used'] = time();
                return $conn['connection'];
            }
        }
        
        // Create new connection if under limit
        if ($this->currentConnections < $this->maxConnections) {
            $id = $this->createConnection();
            $this->pool[$id]['in_use'] = true;
            return $this->pool[$id]['connection'];
        }
        
        // Wait for available connection
        throw new Exception("No available connections in pool");
    }
    
    /**
     * Release connection back to pool
     */
    public function releaseConnection(PDO $connection) {
        foreach ($this->pool as &$conn) {
            if ($conn['connection'] === $connection) {
                $conn['in_use'] = false;
                $conn['last_used'] = time();
                return true;
            }
        }
        
        return false;
    }
    
    /**
     * Clean up idle connections
     */
    public function cleanup($maxIdleTime = 300) {
        $now = time();
        
        foreach ($this->pool as $id => $conn) {
            if (!$conn['in_use'] && 
                ($now - $conn['last_used']) > $maxIdleTime &&
                $this->currentConnections > $this->minConnections) {
                
                unset($this->pool[$id]);
                $this->currentConnections--;
            }
        }
    }
    
    /**
     * Get pool statistics
     */
    public function getStats() {
        $inUse = 0;
        $idle = 0;
        
        foreach ($this->pool as $conn) {
            if ($conn['in_use']) {
                $inUse++;
            } else {
                $idle++;
            }
        }
        
        return [
            'total' => $this->currentConnections,
            'in_use' => $inUse,
            'idle' => $idle,
            'max' => $this->maxConnections,
            'min' => $this->minConnections
        ];
    }
}

// ========================================
// SECURE CONNECTION WITH SSL
// ========================================

/**
 * Secure SSL/TLS connection
 */
class SecureConnection {
    private $pdo;
    
    public function __construct() {
        $this->connectWithSSL();
    }
    
    /**
     * Establish SSL connection
     */
    private function connectWithSSL() {
        $config = [
            'host' => $_ENV['DB_HOST'],
            'dbname' => $_ENV['DB_DATABASE'],
            'username' => $_ENV['DB_USERNAME'],
            'password' => $_ENV['DB_PASSWORD'],
            'charset' => 'utf8mb4'
        ];
        
        $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset={$config['charset']}";
        
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca-cert.pem',
            PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
            PDO::MYSQL_ATTR_SSL_KEY => '/path/to/client-key.pem',
            PDO::MYSQL_ATTR_SSL_CERT => '/path/to/client-cert.pem'
        ];
        
        try {
            $this->pdo = new PDO($dsn, $config['username'], $config['password'], $options);
            
            // Verify SSL is active
            $stmt = $this->pdo->query("SHOW STATUS LIKE 'Ssl_cipher'");
            $result = $stmt->fetch();
            
            if (empty($result['Value'])) {
                throw new Exception("SSL connection not established");
            }
            
            echo "✅ Secure SSL connection established\n";
            echo "SSL Cipher: " . $result['Value'] . "\n";
            
        } catch (PDOException $e) {
            die("SSL Connection failed: " . $e->getMessage());
        }
    }
    
    public function getConnection() {
        return $this->pdo;
    }
}

// ========================================
// USAGE EXAMPLES
// ========================================

// Example: Using configuration manager
$config = new DatabaseConfig();
$dbConfig = $config->getConfig();

// Example: Connection pooling
$pool = new ConnectionPool($config);
$conn = $pool->getConnection();
// Use connection...
$pool->releaseConnection($conn);
echo "Pool stats: ";
print_r($pool->getStats());

// Example: SSL connection
$secureConn = new SecureConnection();
$pdo = $secureConn->getConnection();

Practice Exercise

💻

Connection Challenges

Complete these connection tasks:

Create a connection using environment variables
Implement connection retry logic with exponential backoff
Set up a connection pool with 5 connections
Configure SSL/TLS secure connection
Create a multi-database connection manager
Implement connection health checks
Set up read/write connection splitting
Build connection monitoring system

PHP WordPress Development

Course Progress