PHP PDO (PHP Data Objects): Database-Agnostic Mastery & Security

While MySQLi is dedicated strictly to MySQL, modern enterprise PHP applications (such as Laravel and Symfony) standardize exclusively on PDO (PHP Data Objects). PDO provides a clean, unified, database-agnostic interface supporting 12 different relational database engines—including MySQL, PostgreSQL, SQLite, Oracle, and Microsoft SQL Server. In this masterclass, you will learn how to write bulletproof, SQL-injection proof database applications using PDO.

Figure 6: Complete 4-step PDO workflow: DSN Connection, SQL Preparation, Parameter Binding, and Result Fetching.

Direct Comparison: PDO vs MySQLi

Connecting via PDO Data Source Name (DSN)

To connect to a database with PDO, you construct a Data Source Name (DSN) string and pass optimal configuration options:

<?php
    $host = "127.0.0.1";
    $db   = "veewom_academy";
    $user = "root";
    $pass = "";
    $charset = "utf8mb4";

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

    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions on errors
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Return associative arrays
        PDO::ATTR_EMULATE_PREPARES   => false,                  // True native prepared statements
    ];

    try {
        $pdo = new PDO($dsn, $user, $pass, $options);
        echo "Connected successfully to database via PDO!";
    } catch (PDOException $e) {
        die("Database Connection Error: " . $e->getMessage());
    }
?>

CRUD with PDO: Prepared Statements & Named Parameters

1. Inserting Records (INSERT)

<?php
    $sql = "INSERT INTO users (name, email, role) VALUES (:name, :email, :role)";
    $stmt = $pdo->prepare($sql);

    // Clean execution with associative array
    $stmt->execute([
        ':name'  => 'Deepak Verma',
        ':email' => 'deepak@veewom.com',
        ':role'  => 'BCA Student'
    ]);

    echo "User created with ID: " . $pdo->lastInsertId();
?>

2. Fetching Records (SELECT)

<?php
    $stmt = $pdo->prepare("SELECT id, name, email FROM users WHERE role = :role");
    $stmt->execute([':role' => 'BCA Student']);

    // Fetch all matching records in one call
    $students = $stmt->fetchAll();

    foreach ($students as $s) {
        echo "ID: " . $s['id'] . " | Name: " . htmlspecialchars($s['name']) . "<br>";
    }
?>

Atomic Database Transactions in PDO

When performing multi-step financial or registration operations (such as transferring college fees between accounts), transactions ensure that all queries succeed together, or all fail together:

<?php
    try {
        $pdo->beginTransaction();

        // Step 1: Deduct from Student Wallet
        $stmt1 = $pdo->prepare("UPDATE wallets SET balance = balance - :amt WHERE user_id = :id");
        $stmt1->execute([':amt' => 5000, ':id' => 101]);

        // Step 2: Credit to College Fee Account
        $stmt2 = $pdo->prepare("UPDATE college_accounts SET balance = balance + :amt WHERE account_id = 1");
        $stmt2->execute([':amt' => 5000]);

        // If both queries succeed without exception, commit changes permanently
        $pdo->commit();
        echo "Fee payment processed successfully!";

    } catch (Exception $e) {
        // If anything fails, rollback database to original state!
        $pdo->rollBack();
        echo "Payment failed and rolled back: " . $e->getMessage();
    }
?>

BCA / CS University Exam Corner

Interactive Student Practice Session