DEV Community

ライフポータル
ライフポータル

Posted on • Originally published at code-izumi.com

PHP Database Connection: A Beginner’s Guide to PDO (MySQL & PostgreSQL)

#php #programming

In web development, connecting to a database to manage user info or product data is an essential task. For those new to PHP, this first step can often feel like a hurdle.

Common questions include:

  • "What exactly is PDO, and should I use it over mysqli?"
  • "Why do I keep getting error screens when trying to connect?"
  • "How do I connect to PostgreSQL as well as MySQL?"

In this guide, we’ll dive deep into PDO (PHP Data Objects)—the industry-standard way to handle database connections in PHP. We’ll cover everything from basic connection scripts to essential security options and troubleshooting.

1. Why Choose PDO Over mysqli?

There are two main ways to connect to a database in PHP: mysqli and PDO.

Modern PHP development strongly recommends PDO. The biggest advantage is that PDO acts as a "Database Abstraction Layer." This means you can use the same code structure regardless of which database you are using.

If you decide to switch from MySQL to PostgreSQL later, you only need to change your connection string (DSN) slightly. mysqli, on the other hand, is for MySQL only. Mastering PDO makes you a more versatile and future-proof developer.

2. Connecting to MySQL with PDO

To connect, you need four pieces of information: Hostname, Database name, Username, and Password.

The Basic Connection Script 

<?php
// Connection Settings
$dsn = 'mysql:dbname=my_database;host=localhost;charset=utf8mb4';
$user = 'db_user';
$password = 'password123';

try {
    // Establishing the connection
    $dbh = new PDO($dsn, $user, $password);

    echo "Successfully connected to the database!\n";

    // Disconnect (Optional: setting it to null or waiting for script to end)
    $dbh = null;

} catch (PDOException $e) {
    // Handle failures
    echo "A connection error occurred.";

    // Debugging only (Avoid showing this in production!)
    // echo "Detail: " . $e->getMessage();

    exit();
}
?>
Enter fullscreen mode Exit fullscreen mode

Key Concepts

  • DSN (Data Source Name): A string containing your database info. Always specify charset=utf8mb4 here to prevent security vulnerabilities like SQL injection.
  • try-catch Block: Database connections can fail for many reasons (wrong password, server down). Always wrap your connection in a try-catch to handle the PDOException gracefully.
  • Security Tip: Never display $e->getMessage() in a production environment. It can leak sensitive internal info like your database username or host.

3. Essential "Option Settings" for Professionals

You can customize PDO's behavior by passing an "options array" as the fourth argument to new PDO. Here is the standard setup used in professional environments:

<?php
$options = [
    // Throw exceptions on SQL errors (Essential for debugging)
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    // Return results as an associative array by default
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    // Disable prepared statement emulation for better security
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $dbh = new PDO($dsn, $user, $password, $options);
    echo "Connected with optimized settings!";
} catch (PDOException $e) {
    error_log($e->getMessage()); // Record details in the server log instead of the screen
}
?>
Enter fullscreen mode Exit fullscreen mode
  • ATTR_ERRMODE: Ensures that any SQL error triggers an exception that you can catch.
  • ATTR_DEFAULT_FETCH_MODE: Setting this to FETCH_ASSOC ensures you get clean associative arrays (column names as keys) without duplicated numeric indices, saving memory.
  • ATTR_EMULATE_PREPARES: Setting this to false forces PDO to use the database's native prepared statements, which is safer.

4. Connecting to PostgreSQL

The beauty of PDO is that the code structure remains the same. You only change the DSN prefix.

// PostgreSQL DSN uses 'pgsql:' instead of 'mysql:'
$dsn = 'pgsql:dbname=my_database;host=localhost;port=5432';
$user = 'postgres_user';
$password = 'password123';
Enter fullscreen mode Exit fullscreen mode

5. Troubleshooting Checklist

If you see an error like SQLSTATE[...], check these points:

  1. Credentials: Double-check for typos in your DB name, username, or password.
  2. Host and Port: If your DB is in a Docker container or on a remote server, localhost won't work. Also, check if your port (MySQL: 3306, Postgres: 5432) is custom.
  3. Drivers: Ensure the PDO driver is enabled in your php.ini. Check phpinfo() for pdo_mysql or pdo_pgsql.
  4. Localhost vs. 127.0.0.1: On Linux/Mac, localhost attempts a socket connection. If that fails, using 127.0.0.1 switches to a TCP/IP connection, which often solves the issue.

Conclusion

PDO is the gold standard for database interaction in PHP. By mastering the DSN structure and using proper exception handling, you can write secure, portable, and professional-grade code that works across multiple database systems.

Originally published at: [https://code-izumi.com/php/database-connection/]

Top comments (0)