Connect to MySQL

Connecting to MySQL: PDO vs MySQLi

PHP offers two database extension drivers to connect to MySQL: PDO (PHP Data Objects) and MySQLi (MySQL Improved). PDO is the industry standard due to its database-agnostic interface supporting 12+ database engines.

PDO vs MySQLi Comparison

Connection Architecture

flowchart TD
    A["PHP Script"] --> B["DSN String: 'mysql:host=localhost;dbname=test;charset=utf8mb4'"]
    B --> C["new PDO($dsn, $user, $pass, $options)"]
    C --> D{"Connection Success?"}
    D -- Failure --> E["Catch PDOException; Log Error"]
    D -- Success --> F["Return Ready PDO Handle"]

Practical Code Example (Industry Standard PDO Connection)

<?php
declare(strict_types=1);

function getDatabaseConnection(): PDO
{
    $host = '127.0.0.1';
    $db   = 'app_db';
    $user = 'root';
    $pass = 'secret_password';
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$host;dbname=$db;charset=$charset";

    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions on errors
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Fetch associative arrays by default
        PDO::ATTR_EMULATE_PREPARES   => false,                  // Native prepared statements
    ];

    try {
        return new PDO($dsn, $user, $pass, $options);
    } catch (PDOException $e) {
        // Log real connection error to log file, never reveal credentials to user!
        error_log("Database Connection Error: " . $e->getMessage());
        throw new RuntimeException("Database connection failure. Please try again later.");
    }
}

// Testing PDO Connection
try {
    $pdo = getDatabaseConnection();
    echo "PDO MySQL Connection Established Successfully!
";
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "
";
}

Best Practices

• Always Prefer PDO over MySQLi: PDO enables easy migration to PostgreSQL or SQLite without rewriting queries.
• Enable PDO::ERRMODE_EXCEPTION: Configures PDO to throw catchable PDOException objects on syntax or connection errors.
• Disable Emulated Prepares (PDO::ATTR_EMULATE_PREPARES => false): Enforces real native SQL server-side prepared statement execution.

Self-Check Challenge

Write a PDO Data Source Name (DSN) string connecting to host 127.0.0.1, database store_db, with utf8mb4 charset! (mysql:host=127.0.0.1;dbname=store_db;charset=utf8mb4)