PDO connection setup in PHP

Introduction

PDO helps you connect to databases safely and easily. It makes talking to databases simple and secure.

When you want to connect your PHP code to a MySQL database.

When you need to run database queries safely without risking security problems.

When you want to switch between different database types without changing much code.

When you want to handle errors in database connections clearly.

When you want to prepare database commands to avoid mistakes or attacks.

Syntax

PHP

<?php
$dsn = 'mysql:host=your_host;dbname=your_dbname;charset=utf8';
$username = 'your_username';
$password = 'your_password';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully.";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

The $dsn string tells PDO where the database is and which one to use.

Setting PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION helps catch errors clearly.

Examples

Basic connection to a MySQL database on your local computer.

PHP

<?php
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8';
$username = 'root';
$password = '';
$pdo = new PDO($dsn, $username, $password);
?>

Connecting to a PostgreSQL database using PDO.

PHP

<?php
$dsn = 'pgsql:host=localhost;port=5432;dbname=testdb';
$username = 'postgres';
$password = 'secret';
$pdo = new PDO($dsn, $username, $password);
?>

Connecting to a SQLite database file without username or password.

PHP

<?php
$dsn = 'sqlite:/path/to/database.db';
$pdo = new PDO($dsn);
?>

Sample Program

This program tries to connect to a MySQL database named mydatabase on the local machine. It prints a success message if it connects or an error message if it fails.

PHP

<?php
$dsn = 'mysql:host=localhost;dbname=mydatabase;charset=utf8';
$username = 'user123';
$password = 'pass123';

try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully.";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

Important Notes

Always use try-catch to handle connection errors gracefully.

Use UTF-8 charset in DSN to avoid character problems.

Keep your database username and password safe and never share them publicly.

Summary

PDO lets you connect to databases in a safe and easy way.

Use DSN, username, and password to set up the connection.

Handle errors with try-catch and set error mode to exceptions.