Learn PHP Programming

<?php
// Database configuration
$host = 'localhost';
$dbname = 'my_application';
$username = 'db_user';
$password = 'db_password';
$charset = 'utf8mb4';

// DSN (Data Source Name)
$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";

// PDO options for security and performance
$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,                     // Use real prepared statements
    PDO::ATTR_STRINGIFY_FETCHES  => false,                     // Keep native data types
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
    echo "Connected successfully!";
} catch (PDOException $e) {
    // Log the error, don't expose details to users
    error_log("Database connection failed: " . $e->getMessage());
    die("A database error occurred. Please try again later.");
}
?>

<?php
// Named placeholders (recommended for readability)
$sql = "INSERT INTO users (name, email, age) VALUES (:name, :email, :age)";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':name'  => 'Alice Johnson',
    ':email' => 'alice@example.com',
    ':age'   => 28
]);
$lastId = $pdo->lastInsertId();
echo "Inserted user with ID: $lastId";

// Positional placeholders (shorter syntax)
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute(['Bob Smith', 'bob@example.com', 35]);

// Insert multiple rows efficiently
$users = [
    ['Charlie', 'charlie@example.com', 22],
    ['Diana', 'diana@example.com', 30],
];
$sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
$stmt = $pdo->prepare($sql);
foreach ($users as $user) {
    $stmt->execute($user);
}
?>

<?php
// FETCH_ASSOC - Returns associative array
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > ?");
$stmt->execute([25]);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
    echo $user['name'] . " - " . $user['email'] . "\n";
}

// FETCH_OBJ - Returns anonymous objects
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
$stmt->execute([':email' => 'alice@example.com']);
$user = $stmt->fetch(PDO::FETCH_OBJ);
echo $user->name;  // Access properties with arrow notation

// FETCH_CLASS - Maps results to a class
class User {
    public $id;
    public $name;
    public $email;

    public function getDisplayName() {
        return strtoupper($this->name);
    }
}
$stmt = $pdo->prepare("SELECT id, name, email FROM users");
$stmt->execute();
$users = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
foreach ($users as $user) {
    echo $user->getDisplayName() . "\n";
}
?>

<?php
// UPDATE with named placeholders
$sql = "UPDATE users SET email = :email, age = :age WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([
    ':email' => 'newalice@example.com',
    ':age'   => 29,
    ':id'    => 1
]);
echo "Updated " . $stmt->rowCount() . " row(s)";

// DELETE with confirmation check
$sql = "DELETE FROM users WHERE id = ? AND email = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([42, 'old@example.com']);
if ($stmt->rowCount() === 0) {
    echo "No matching record found to delete.";
} else {
    echo "Deleted successfully.";
}
?>

<?php
// Transfer money between accounts (must be atomic)
try {
    $pdo->beginTransaction();

    // Debit from sender
    $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - :amount WHERE id = :id AND balance >= :amount");
    $stmt->execute([':amount' => 100.00, ':id' => 1]);
    if ($stmt->rowCount() === 0) {
        throw new Exception("Insufficient funds or account not found.");
    }

    // Credit to receiver
    $stmt = $pdo->prepare("UPDATE accounts SET balance = balance + :amount WHERE id = :id");
    $stmt->execute([':amount' => 100.00, ':id' => 2]);
    if ($stmt->rowCount() === 0) {
        throw new Exception("Receiver account not found.");
    }

    // Log the transaction
    $stmt = $pdo->prepare("INSERT INTO transfers (from_id, to_id, amount, created_at) VALUES (?, ?, ?, NOW())");
    $stmt->execute([1, 2, 100.00]);

    $pdo->commit();
    echo "Transfer completed successfully!";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transfer failed: " . $e->getMessage();
    error_log("Transaction error: " . $e->getMessage());
}
?>