How to Connect PHP to a Database with PDO

PDO is an object-oriented interface for accessing databases from PHP through a common API.

In the past (and in many old tutorials), mysql_connect functions (now removed) or the mysqli extension were used. However, in modern professional development, PDO (PHP Data Objects) is king.

Why PDO and not mysqli?

Imagine that mysqli is a charger that only works for iPhones. If tomorrow you want to switch to Android, you have to throw away the charger and buy a new one. PDO is like a universal charger: it works with almost any database.

The Connection String (DSN)

To connect, we need to define the DSN (Data Source Name). It’s simply a string that tells PDO: “What type of database it is, where it is, and what it’s called”.

Typical format: mysql:host=localhost;dbname=my_store;charset=utf8mb4

• host: The server address (usually localhost or an IP).
• dbname: Your database name.
• charset: Very important. Always use utf8mb4 (not just utf8). This ensures you can save emojis (😊) and special characters without breaking anything.

Secure and Robust Connection Code

Let’s create a connection that, if it fails, notifies us properly (using try-catch from Module 5) and configures itself automatically to be secure.

<?php
// Configuration (Ideally this would come from $_ENV, as seen in Module 6)
$host = 'localhost';
$db   = 'php_course';
$user = 'root';
$pass = ''; // In XAMPP it's usually empty, in MAMP it's 'root'
$charset = 'utf8mb4';

// Build the DSN
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

// PDO configuration options (Vital for security and convenience)
$options = [
    // On error, throw an Exception (instead of failing silently)
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    // Data will always come as Associative Arrays (easier to read)
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    // Request native prepared statements from the MySQL driver
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    // Attempt to connect
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Database connection successful!";
    
} catch (PDOException $e) {
    // If it fails, catch the specific PDO error
    // IMPORTANT: In production, never 'echo' the error directly to the user,
    // log it to an internal log instead.
    die("Connection error: " . $e->getMessage());
}

Breaking Down the Options ($options)

This options array is what separates a beginner from a pro:

1. ERRMODE_EXCEPTION: Makes PDO failures throw exceptions. This is the default mode since PHP 8, but declaring it makes the intention explicit and helps if the code runs in another environment.
2. FETCH_ASSOC: When we request data, it will be given like this: ['name' => 'John']. Without this, by default it duplicates them: [0 => 'John', 'name' => 'John'], using twice the memory.
3. EMULATE_PREPARES => false: Asks the MySQL driver to use native prepared statements. Both native and emulated modes separate parameters and SQL when used correctly; choose based on the driver’s capabilities and limitations.