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

Source: Dev.to

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 is limited to MySQL only.
• Mastering PDO makes you a more versatile and future‑proof developer.

Connecting to MySQL with PDO

The Basic Connection Script

getMessage();

    exit();
}
?>

Key Concepts

• DSN (Data Source Name) – a string containing your database info. Always specify charset=utf8mb4 to avoid security issues.
• try‑catch Block – wrap the connection in try‑catch to handle PDOException gracefully.
• Security Tip – never display $e->getMessage() in production; it can leak sensitive information.

Essential “Option Settings” for Professionals

You can customize PDO’s behavior by passing an options array as the fourth argument to new PDO.

 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()); // Log details instead of displaying them
}
?>

• ATTR_ERRMODE – ensures any SQL error triggers an exception you can catch.
• ATTR_DEFAULT_FETCH_MODE – FETCH_ASSOC returns clean associative arrays, saving memory.
• ATTR_EMULATE_PREPARES – setting this to false forces PDO to use the database’s native prepared statements, which is safer.

Connecting to PostgreSQL

The code structure remains the same; only the DSN prefix changes.

Troubleshooting Checklist

If you encounter an error like SQLSTATE[...], verify the following:

• Credentials – ensure there are no typos in the database name, username, or password.
• Host and Port – for Docker containers or remote servers, localhost may not work. Confirm the correct port (MySQL 3306, PostgreSQL 5432) and any custom settings.
• Drivers – make sure the appropriate PDO driver (pdo_mysql or pdo_pgsql) is enabled in php.ini. Use phpinfo() to check.
• Localhost vs. 127.0.0.1 – on Linux/macOS, localhost attempts a socket connection. Switching to 127.0.0.1 forces a TCP/IP connection, which can resolve connection issues.

Conclusion

PDO is the gold standard for database interaction in PHP. By mastering the DSN structure and employing 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/