Lesson 41 of 50 – PHP PDO Connection

82%

PHP PDO Connection

PDO stands for PHP Data Objects. It provides a consistent interface for connecting PHP applications to supported databases, including MySQL. PDO supports prepared statements, parameter binding, and exception-based error handling.

1. What Is PDO in PHP?

PDO is a database access interface in PHP. It allows you to connect to a database and execute SQL queries using a common set of methods.

Advantages of PDO

Supports multiple database drivers.
Supports prepared statements.
Provides consistent database access methods.
Supports transactions when the database driver supports them.
Can report database errors using exceptions.

PDO example:

<?php
$pdo = new PDO(
    "mysql:host=localhost;dbname=school;charset=utf8mb4",
    "root",
    ""
);

echo "Database connection successful.";
?>

This example assumes the MySQL-compatible server is running, the database named school exists, and the supplied credentials are correct.

2. Requirements for PDO MySQL

Before connecting PHP to MySQL, make sure the required software and database are available.

Install PHP or XAMPP.
Start Apache and the database server.
Create a database named school.
Enable the PDO MySQL extension.
Use the correct hostname, username, password, and database name.

You can check whether PDO MySQL is available by running:

<?php
if (extension_loaded("pdo_mysql")) {
    echo "PDO MySQL extension is enabled.";
} else {
    echo "PDO MySQL extension is not enabled.";
}
?>

If the extension is missing, enable or install it according to your PHP environment and restart the relevant service.

3. Understanding the PDO Connection String

PDO uses a Data Source Name (DSN) to describe the database connection details.

mysql:host=localhost;dbname=school;charset=utf8mb4

Explanation:

mysql identifies the PDO MySQL driver.
host=localhost specifies the database server host.
dbname=school selects the database.
charset=utf8mb4 configures the connection character set.

Example with a custom port:

<?php
$dsn = "mysql:host=127.0.0.1;port=3306;dbname=school;charset=utf8mb4";

echo $dsn;
?>

Port 3306 is a common MySQL port, but your database server may use a different port.

4. Creating a PDO Connection

The PDO constructor creates a database connection using the DSN and credentials.

<?php
$host = "localhost";
$dbname = "school";
$username = "root";
$password = "";

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

$pdo = new PDO($dsn, $username, $password);

echo "Connected to the database.";
?>

Expected output: Connected to the database.

This is a basic local-development example. The default credentials vary by installation. Use the actual credentials configured for your database.

In production, avoid hardcoding credentials in files committed to source control. Use protected configuration or environment variables.

5. Handling Connection Errors with try and catch

A connection can fail if the database is unavailable, the database name is incorrect, or the credentials are invalid. Configure PDO to throw exceptions so errors can be handled.

<?php
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=school;charset=utf8mb4",
        "root",
        "",
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        ]
    );

    echo "Database connected successfully.";
} catch (PDOException $e) {
    error_log($e->getMessage());
    echo "Unable to connect to the database.";
}
?>

PDO::ERRMODE_EXCEPTION makes database errors throw exceptions that can be handled with catch.

The example logs technical details on the server but displays a generic message to the visitor. Avoid showing database passwords, server details, or raw exception messages on public pages.

6. Setting PDO Connection Options

PDO accepts an options array to configure connection behavior.

<?php
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=school;charset=utf8mb4",
        "root",
        "",
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false
        ]
    );

    echo "PDO configured successfully.";
} catch (PDOException $e) {
    error_log($e->getMessage());
    echo "Connection failed.";
}
?>

Options explained:

PDO::ATTR_ERRMODE controls error reporting.
PDO::FETCH_ASSOC makes fetched rows associative arrays by default.
PDO::ATTR_EMULATE_PREPARES set to false requests native prepared statements where supported.

Supported behavior can vary by PDO driver. These options are commonly useful for MySQL applications.

7. Creating a Reusable Database Connection File

In larger projects, create a separate file for the database connection and include it wherever needed.

File: db.php

<?php
$host = "localhost";
$dbname = "school";
$username = "root";
$password = "";

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

try {
    $pdo = new PDO(
        $dsn,
        $username,
        $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]
    );
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit("Database connection failed.");
}
?>

Use the connection in another file:

<?php
require_once "db.php";

echo "Connection is ready to use.";
?>

If your files are in different directories, adjust the include path accordingly. Keep connection files and credentials outside publicly downloadable locations whenever possible.

8. Testing the Connection with a SQL Query

After connecting, you can execute a query to check whether PHP can access the database.

<?php
require_once "db.php";

try {
    $stmt = $pdo->query("SELECT DATABASE()");
    $databaseName = $stmt->fetchColumn();

    echo "Connected database: "
        . htmlspecialchars(
            (string) $databaseName,
            ENT_QUOTES | ENT_SUBSTITUTE,
            "UTF-8"
        );
} catch (PDOException $e) {
    error_log($e->getMessage());
    echo "Unable to retrieve database information.";
}
?>

Expected output: Connected database: school

The output assumes the connection uses the school database. The SELECT DATABASE() query returns the currently selected database name.

Use query() for a fixed SQL query that does not require user-supplied parameters. For user input, use prepared statements with bound parameters.

9. Practical Example: Fetch Students Using PDO

This example connects to the database and displays student records from the students table. It assumes the table created in the previous lesson already exists.

File: students.php

<?php
require_once "db.php";

try {
    $stmt = $pdo->query(
        "SELECT id, name, email, course
         FROM students
         ORDER BY id ASC"
    );

    $students = $stmt->fetchAll();
} catch (PDOException $e) {
    error_log($e->getMessage());
    $students = [];
    $message = "Unable to load student records.";
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Student Records</title>
</head>
<body>

    <h2>Student Records</h2>

    <?php if (!empty($message)): ?>
        <p>
            <?= htmlspecialchars(
                $message,
                ENT_QUOTES | ENT_SUBSTITUTE,
                "UTF-8"
            ) ?>
        </p>
    <?php elseif (count($students) === 0): ?>
        <p>No student records found.</p>
    <?php else: ?>

        <table border="1" cellpadding="8">
            <tr>
                <th>ID</th>
                <th>Name</th>
                <th>Email</th>
                <th>Course</th>
            </tr>

            <?php foreach ($students as $student): ?>
                <tr>
                    <td><?= (int) $student["id"] ?></td>

                    <td>
                        <?= htmlspecialchars(
                            $student["name"],
                            ENT_QUOTES | ENT_SUBSTITUTE,
                            "UTF-8"
                        ) ?>
                    </td>

                    <td>
                        <?= htmlspecialchars(
                            $student["email"],
                            ENT_QUOTES | ENT_SUBSTITUTE,
                            "UTF-8"
                        ) ?>
                    </td>

                    <td>
                        <?= htmlspecialchars(
                            $student["course"],
                            ENT_QUOTES | ENT_SUBSTITUTE,
                            "UTF-8"
                        ) ?>
                    </td>
                </tr>
            <?php endforeach; ?>
        </table>

    <?php endif; ?>

</body>
</html>

This example demonstrates a reusable connection file, query execution, fetching rows, and safely displaying database values in HTML.

For a large student database, use pagination rather than loading every record with fetchAll().

🧠 Quick Quiz

1. What does PDO stand for?

PHP Data Objects
Personal Database Operations
PHP Document Output

2. Which PHP class creates a PDO database connection?

MySQLConnection
PDO
Database

3. Which PDO option enables exception-based error handling?

PDO::FETCH_ASSOC
PDO::ATTR_DEFAULT_FETCH_MODE
PDO::ERRMODE_EXCEPTION

← Previous Next →

PHP Tutorial

PHP PDO Connection

Advantages of PDO

🧠 Quick Quiz