PDO Configuration

What Is PDO?

PDO (PHP Data Objects) is a database abstraction layer built into PHP that provides a consistent interface for accessing different databases. Instead of using database-specific functions like mysql_query() or mysqli_connect(), PDO lets you write code that works across MySQL, PostgreSQL, SQLite, and other database systems with minimal changes.

Why PDO Over mysqli?

• Database portability — switch between MySQL, PostgreSQL, SQLite by changing the connection string
• Named parameters — more readable prepared statements with :name placeholders
• Better error handling — exception-based errors are cleaner than checking return values
• Object-oriented only — consistent, clean API without a procedural alternative

Enabling PDO Extensions

PDO is included in PHP by default, but database-specific drivers must be enabled in php.ini:

# Check which PDO drivers are installed
php -m | grep -i pdo

# In php.ini, uncomment/add the driver you need:
extension=pdo_mysql
extension=pdo_pgsql
extension=pdo_sqlite

# Restart Apache/Nginx after changes
sudo systemctl restart apache2

To check PDO availability from your browser, create a test file:

<?php
// test_pdo.php
phpinfo();
// Search for "PDO" in the output
// Or more specifically:
echo "PDO drivers: " . implode(", ", PDO::getAvailableDrivers());
?>

Basic PDO Connections

MySQL

<?php
$host = 'localhost';
$dbname = 'my_database';
$username = 'db_user';
$password = 'db_password';

try {
    $pdo = new PDO(
        "mysql:host=$host;dbname=$dbname;charset=utf8mb4",
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]
    );
    echo "Connected successfully!";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

PostgreSQL

<?php
$pdo = new PDO(
    "pgsql:host=localhost;port=5432;dbname=my_database",
    "db_user",
    "db_password"
);
?>

SQLite

<?php
// SQLite uses a file path instead of host/credentials
$pdo = new PDO("sqlite:/path/to/database.db");
?>

Prepared Statements

Prepared statements prevent SQL injection by separating the query structure from the data:

<?php
// SAFE: Using prepared statement with named parameters
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND status = :status");
$stmt->execute([
    ':email' => $userEmail,
    ':status' => 'active'
]);
$user = $stmt->fetch();

// SAFE: Using positional placeholders
$stmt = $pdo->prepare("INSERT INTO posts (title, body, author_id) VALUES (?, ?, ?)");
$stmt->execute([$title, $body, $authorId]);

// Get the last inserted ID
$newId = $pdo->lastInsertId();

// DANGEROUS: Never do this!
// $pdo->query("SELECT * FROM users WHERE email = '$userInput'");
?>

Error Handling

PDO supports three error modes. Always use ERRMODE_EXCEPTION for development and debugging:

<?php
// ERRMODE_EXCEPTION (recommended) — throws exceptions
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// ERRMODE_WARNING — issues PHP warnings
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

// ERRMODE_SILENT (default) — fails silently
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);

// Production error handling pattern
try {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
    $stmt->execute([':id' => $userId]);
    $result = $stmt->fetchAll();
} catch (PDOException $e) {
    // Log the error, show user-friendly message
    error_log("Database error: " . $e->getMessage());
    echo "An error occurred. Please try again later.";
}
?>

Common PDO Errors & Solutions

• could not find driver — The PDO driver for your database isn't installed. Install it: sudo apt install php-mysql (or php-pgsql, php-sqlite3)
• Access denied for user — Wrong username, password, or the user doesn't have privileges on the database. Verify credentials in your database manager.
• Connection refused — The database server isn't running or isn't accepting connections on the specified host/port. Check systemctl status mysql.
• Unknown database — The database name in your connection string doesn't exist. Create it first or check for typos.
• SQLSTATE[HY000] [2002] No such file or directory — MySQL socket file not found. Try using 127.0.0.1 instead of localhost in the connection string.