When developing web applications with PHP, one of the most important tasks is working with databases. While older extensions like mysql_ or mysqli_ still exist, the modern, flexible, and secure way to interact with databases in PHP is by using PDO (PHP Data Objects).

PDO is not tied to a specific database; it provides a consistent interface for working with multiple databases such as MySQL, PostgreSQL, SQLite, and many others. This makes your code more portable and easier to maintain.

Why Use PDO?

• Database Independence — You can switch from MySQL to PostgreSQL or SQLite with minimal code changes.
• Security — PDO supports prepared statements, which help prevent SQL injection.
• Flexibility — PDO allows different fetching styles (arrays, objects, etc.) and has useful features like transactions.

Connecting to a Database with PDO

Before you can run queries, you need to establish a connection:

<?php
$dsn = "mysql:host=localhost;dbname=testdb;charset=utf8mb4";
$username = "root";
$password = "";

try {
    $pdo = new PDO($dsn, $username, $password);
    // Set error mode to exception for better error handling
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connection successful!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

DSN (Data Source Name) tells PDO which database driver to use and how to connect.
setAttribute() lets you configure how PDO behaves (here we enable exceptions on errors).

Executing Simple Queries

Once connected, you can run queries using query() for simple, non-parameterized SQL:

<?php
$sql = "SELECT * FROM users";
foreach ($pdo->query($sql) as $row) {
    echo $row['id'] . " - " . $row['name'] . "<br>";
}
?>

This is fine for quick scripts, but not secure if you accept user input.

Prepared Statements (Safe and Recommended)

Prepared statements are the right way to handle dynamic input. They separate SQL logic from user data, preventing SQL injection.

Example: Selecting with Parameters

<?php
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute(['email' => 'john@example.com']);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

if ($user) {
    echo "User found: " . $user['name'];
} else {
    echo "No user found.";
}
?>

Notice the :email placeholder – it’s replaced safely by PDO, so even malicious input won’t break your query.

Inserting Data

<?php
$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
$stmt->execute([
    'name' => 'Alice',
    'email' => 'alice@example.com'
]);

echo "New user inserted with ID: " . $pdo->lastInsertId();
?>

Updating Data

<?php
$stmt = $pdo->prepare("UPDATE users SET name = :name WHERE id = :id");
$stmt->execute([
    'name' => 'Updated Name',
    'id' => 1
]);
echo "Record updated!";
?>

Deleting Data

<?php
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute(['id' => 2]);
echo "Record deleted!";
?>

Fetching Results

PDO offers several fetch modes:

• PDO::FETCH_ASSOC → associative array
• PDO::FETCH_NUM → numeric array
• PDO::FETCH_OBJ → object
• PDO::FETCH_BOTH → default (both numeric and associative)

Example:

<?php
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_OBJ);

foreach ($users as $user) {
    echo $user->id . " - " . $user->name . "<br>";
}
?>

Transactions with PDO

Transactions allow you to group multiple queries together, ensuring either all succeed or none do (rollback).

<?php
try {
    $pdo->beginTransaction();
    $pdo->exec("INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')");
    $pdo->exec("INSERT INTO orders (user_id, amount) VALUES (LAST_INSERT_ID(), 100)");
    $pdo->commit();
    echo "Transaction successful!";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}
?>

Conclusion

PDO is the standard way to work with databases in PHP today. It’s secure, flexible, and portable. By mastering PDO, you’ll not only write safer code but also prepare your applications for future growth and possible database migrations.

Whether you’re just starting with PHP or looking to modernize your code, PDO should always be your first choice for database interaction.