How to Connect to a Database Using PHP PDO

PHP Data Objects (PDO) provides a secure, lightweight, and consistent interface for accessing various relational databases in PHP. This article covers how to establish a database connection using PDO, configure essential connection options, and properly handle connection errors using try-catch blocks.

The PDO Connection String (DSN)

To connect to a database, PDO requires a Data Source Name (DSN), which contains the database driver name, host, database name, and character set. You also need the database username and password.

Here is the basic structure of a MySQL DSN:

$dsn = "mysql:host=localhost;dbname=my_database;charset=utf8mb4";

Establishing the Connection

To establish the connection, you instantiate a new PDO object by passing the DSN, username, password, and an optional array of driver options.

It is best practice to wrap this connection process inside a try-catch block. This ensures that if the database is offline or the credentials are wrong, the script handles the error gracefully instead of exposing sensitive connection details to the user.

<?php
$host = '127.0.0.1';
$db   = 'my_database';
$user = 'db_user';
$pass = 'db_password';
$charset = 'utf8mb4';

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

// Set default PDO options
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
     $pdo = new PDO($dsn, $user, $pass, $options);
     echo "Database connection established successfully.";
} catch (\PDOException $e) {
     // Handle the connection error safely
     error_log($e->getMessage());
     exit("Database connection failed. Please try again later.");
}

Understanding Connection Options

The $options array configures how PDO behaves once the connection is active:

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION: This tells PDO to throw exceptions when a database error occurs, making it easier to debug issues.
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC: This sets the default fetch style to return rows as associative arrays, where the keys match the column names.
PDO::ATTR_EMULATE_PREPARES => false: This disables emulation of prepared statements, forcing PDO to use real prepared statements provided by the database driver. This improves security against SQL injection attacks.

Closing the Connection

PHP automatically closes database connections when your script finishes executing. However, if you need to close the connection manually during runtime, you can do so by destroying the PDO object:

$pdo = null;