PDO Setup - Connecting to MySQL
PDO (PHP Data Objects) is the recommended database abstraction layer. Unlike raw mysqli_*, PDO supports multiple databases with one API, provides named parameters in prepared statements, and offers clean exception-based error handling. The connection setup is one of the most critical pieces of infrastructure in any PHP application.
PDO Connection - Production-Grade Setup
<?php
declare(strict_types=1);
// ── PDO connection factory ─────────────────────────────────────
function createPdo(): PDO {
$dsn = sprintf(
"mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4",
$_ENV["DB_HOST"] ?? "localhost",
(int) ($_ENV["DB_PORT"] ?? 3306),
$_ENV["DB_NAME"] ?? "blog"
);
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // Throw PDOException on errors
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // Fetch as associative arrays
PDO::ATTR_EMULATE_PREPARES => false, // Use real prepared statements
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4", // Ensure UTF-8
];
try {
return new PDO(
$dsn,
$_ENV["DB_USER"] ?? "root",
$_ENV["DB_PASSWORD"] ?? "",
$options
);
} catch (PDOException $e) {
// Log the real error - NEVER expose to user
error_log("Database connection failed: " . $e->getMessage());
throw new RuntimeException("Service temporarily unavailable.");
}
}
// ── Why PDO over mysqli? ───────────────────────────────────────
// 1. Database agnostic - switch from MySQL to PostgreSQL by changing DSN
// 2. Named parameters - :id vs ? (order-independent, self-documenting)
// 3. PDO::FETCH_ASSOC, FETCH_CLASS, FETCH_INTO - flexible result mapping
// 4. Objects-only interface - consistent error handling via exceptions
// 5. Better security - true prepared statements (ATTR_EMULATE_PREPARES = false)
// ── DSN formats for different databases ───────────────────────
// MySQL: "mysql:host=localhost;dbname=blog;charset=utf8mb4"
// PostgreSQL: "pgsql:host=localhost;dbname=blog"
// SQLite: "sqlite:/path/to/database.db"
// SQL Server: "sqlsrv:Server=localhost;Database=blog"
// ── Singleton PDO in a simple application ─────────────────────
class Database {
private static ?PDO $instance = null;
private function __construct() {}
public static function getConnection(): PDO {
if (self::$instance === null) {
self::$instance = createPdo();
}
return self::$instance;
}
}
$pdo = Database::getConnection();Quick Quiz
Tip
Tip
Practice PDO Setup Connecting to MySQL in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Visual architecture and concepts for PDO Setup - Connecting to MySQL
Practice Task
Note
Practice Task - (1) Write a working example of PDO Setup Connecting to MySQL from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Common Mistake
Warning
A common mistake with PDO Setup Connecting to MySQL is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready php code.