Alaa Amer

Working with Databases in PHP using PDO

Working with Databases in PHP using PDO

Specialized Guide by Alaa Amer – Professional Web & App Developer

PDO (PHP Data Objects) is the modern and secure way to work with databases in PHP. It provides a unified interface for working with different types of databases.

2️⃣ Setting Up Secure PDO Connection

<?php
class Database {
    private $host = 'localhost';
    private $db_name = 'my_database';
    private $username = 'root';
    private $password = '';
    private $conn;

    public function connect() {
        $this->conn = null;

        try {
            $dsn = "mysql:host=" . $this->host . ";dbname=" . $this->db_name . ";charset=utf8mb4";

            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false
            ];

            $this->conn = new PDO($dsn, $this->username, $this->password, $options);

        } catch(PDOException $e) {
            echo "Connection failed: " . $e->getMessage();
        }

        return $this->conn;
    }
}
?>

4️⃣ Working with Transactions

function transferMoney($fromAccount, $toAccount, $amount) {
    $database = new Database();
    $db = $database->connect();

    try {
        // Start transaction
        $db->beginTransaction();

        // Deduct from first account
        $query1 = "UPDATE accounts SET balance = balance - :amount WHERE id = :id";
        $stmt1 = $db->prepare($query1);
        $stmt1->execute([':amount' => $amount, ':id' => $fromAccount]);

        // Add to second account
        $query2 = "UPDATE accounts SET balance = balance + :amount WHERE id = :id";
        $stmt2 = $db->prepare($query2);
        $stmt2->execute([':amount' => $amount, ':id' => $toAccount]);

        // Commit transaction
        $db->commit();
        return "Transfer successful";

    } catch (Exception $e) {
        // Rollback transaction on error
        $db->rollback();
        return "Transfer failed: " . $e->getMessage();
    }
}

6️⃣ Professional Error Handling

try {
    $db = new PDO($dsn, $user, $pass, $options);
    $stmt = $db->prepare($query);
    $stmt->execute();

} catch (PDOException $e) {
    // Log the error
    error_log($e->getMessage());

    // Show user message
    die("A system error occurred. Please try again later.");
}

💡 Summary

PDO provides a secure and efficient way to work with databases. Investing in learning it saves you many security issues.

📩 Need help with databases?