Database Integration (MySQL) 🗄️

<?php
// PDO is PHP's recommended database layer. It speaks 12+ databases with one
// API and supports prepared statements (your shield against SQL injection).

// The DSN ("Data Source Name") is a single string describing WHAT to connect to:
//   driver : host : database name : charset
$dsn = 'mysql:host=localhost;dbname=my_app;charset=utf8mb4';

// new PDO(dsn, username, password, options) opens the connection.
$pdo = new PDO($dsn, 'app_user', 'secret', [
    // Throw an exception the moment anything goes wrong (see below — do this always).
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    // Return each row as an associative array: ['name' => 'Alice'] not [0 => 'Alice'].
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    // Use the database's REAL prepared statements, not PHP-side emulation.
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

echo "Connected to MySQL.\n";   // we only reach this line if the connection worked
?>

Connected to MySQL.

<?php
// Two ways to run SQL. The rule is simple:
//   - NO user input?  -> query() is fine (one trip, less typing).
//   - ANY user input? -> prepare() + execute() with placeholders.

// query() runs a fixed string and returns a result set straight away.
// fetchAll() pulls EVERY matching row into one array.
$rows = $pdo->query('SELECT id, name, role FROM users ORDER BY id')->fetchAll();
foreach ($rows as $u) {
    // FETCH_ASSOC means each row is an array keyed by column name.
    echo "  #{$u['id']} {$u['name']} [{$u['role']}]\n";
}

// prepare() + execute() is for queries that contain VALUES from outside.
// The ? is a positional placeholder — a slot the value drops into safely.
$id = 2;                                              // pretend this came from a URL
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = ?');
$stmt->execute([$id]);                               // value supplied here, separately
$one = $stmt->fetch();                               // fetch() returns ONE row (or false)
echo "User {$id} is {$one['name']}\n";
?>

  #1 Alice [admin]
  #2 Bob [user]
  #3 Carol [user]
User 2 is Bob

<?php
// Placeholders come in two flavours. Pick one style per query (don't mix).

// 1) POSITIONAL placeholders ( ? ) — bind by ORDER. Pass a plain array.
$stmt = $pdo->prepare('INSERT INTO users (name, email, role) VALUES (?, ?, ?)');
$stmt->execute(['Diana Prince', 'diana@example.com', 'user']);

// lastInsertId() gives you the auto-increment id the database just generated.
$newId = (int) $pdo->lastInsertId();
echo "Inserted user #{$newId}\n";

// 2) NAMED placeholders ( :name ) — bind by NAME. Pass a keyed array.
// Clearer for long queries because the value can't end up in the wrong slot.
$stmt = $pdo->prepare(
    'SELECT id, name FROM users WHERE role = :role AND name LIKE :search'
);
$stmt->execute([
    'role'   => 'user',
    'search' => 'D%',                 // names starting with D
]);
foreach ($stmt->fetchAll() as $row) {
    echo "  match: #{$row['id']} {$row['name']}\n";
}
?>

Inserted user #4
  match: #4 Diana Prince

<?php
// UPDATE and DELETE work exactly like SELECT/INSERT: prepare, then execute
// with the values bound as parameters. rowCount() tells you how many rows
// were actually changed — useful for "did that record exist?" checks.

// UPDATE — change Bob's role.
$stmt = $pdo->prepare('UPDATE users SET role = :role WHERE id = :id');
$stmt->execute(['role' => 'moderator', 'id' => 2]);
echo "Updated {$stmt->rowCount()} row(s)\n";   // 1

// DELETE — remove the user we inserted earlier.
$stmt = $pdo->prepare('DELETE FROM users WHERE id = ?');
$stmt->execute([4]);
echo "Deleted {$stmt->rowCount()} row(s)\n";    // 1

// rowCount() is 0 when nothing matched — no error, just no rows touched.
$stmt = $pdo->prepare('DELETE FROM users WHERE id = ?');
$stmt->execute([999]);
echo "Deleted {$stmt->rowCount()} row(s)\n";    // 0
?>

Updated 1 row(s)
Deleted 1 row(s)
Deleted 0 row(s)

<?php
// === Why string interpolation is dangerous (SQL injection) ===

// BAD — never build SQL by gluing user input into the string:
//   $sql = "SELECT * FROM users WHERE name = '$name'";   // DO NOT DO THIS
//
// If an attacker sends $name as:   ' OR 1=1 --
// the query becomes:
//   SELECT * FROM users WHERE name = '' OR 1=1 -- '
// ...which matches EVERY row. The input changed the query's logic.

// GOOD — bind the input as a parameter. PDO sends the SQL and the data on
// SEPARATE channels, so user input is always treated as a value, never as code.
$name = "' OR 1=1 --";                 // a hostile value...
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE name = :name');
$stmt->execute(['name' => $name]);     // ...is searched for literally, and matches nothing
echo "Rows found: " . count($stmt->fetchAll()) . "\n";   // 0 — attack defused

// Bonus: a legitimate apostrophe is handled for free, no escaping needed.
$stmt->execute(['name' => "O'Brien"]);
echo "Looked up O'Brien safely.\n";
?>

Rows found: 0
Looked up O'Brien safely.

<?php
// 🎯 YOUR TURN — read ONE user safely by id, then print their name.
// The connection $pdo already exists. Fill in each ___ using the 👉 hints.

$id = 3;   // imagine this arrived from a form or URL

// 1) prepare a SELECT that finds the row WHERE id matches a placeholder
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = ___');   // 👉 use a  ?  placeholder

// 2) run it, passing $id as the value (positional → plain array)
$stmt->___([$id]);                 // 👉 the method that runs a prepared statement

// 3) grab the single matching row
$row = $stmt->___();               // 👉 the method that returns ONE row

echo "Name: {$row['name']}\n";

// ✅ Expected output (when id 3 is "Carol"):
//    Name: Carol
?>

Name: Carol

<?php
// 🎯 YOUR TURN — insert a user with NAMED placeholders, then echo the new id.
// Fill in each ___ using the 👉 hints.

$stmt = $pdo->prepare(
    'INSERT INTO users (name, email) VALUES (___, ___)'   // 👉 use named placeholders :name and :email
);

// Run it, binding each name to its value (named → keyed array)
$stmt->execute([
    '___' => 'Eve Adams',          // 👉 the key must match the :name placeholder (no colon here)
    '___' => 'eve@example.com',    // 👉 the key must match the :email placeholder
]);

// Ask the database for the id it just generated
$id = (int) $pdo->___();           // 👉 the method that returns the last inserted id
echo "Created user #{$id}\n";

// ✅ Expected output (example):
//    Created user #5
?>

Created user #5

<?php
// === Transactions: all-or-nothing ===
// A transaction groups several writes so they either ALL succeed or ALL undo.
// The classic case is moving money: you must never debit one account without
// crediting the other.

try {
    $pdo->beginTransaction();          // open the transaction

    // Take 100 from account 1...
    $debit = $pdo->prepare('UPDATE accounts SET balance = balance - :amt WHERE id = :id');
    $debit->execute(['amt' => 100, 'id' => 1]);

    // ...and give 100 to account 2.
    $credit = $pdo->prepare('UPDATE accounts SET balance = balance + :amt WHERE id = :id');
    $credit->execute(['amt' => 100, 'id' => 2]);

    $pdo->commit();                    // both updates land together, atomically
    echo "Transfer committed.\n";
} catch (Throwable $e) {
    $pdo->rollBack();                  // ANY failure? undo everything as if nothing ran
    echo "Transfer rolled back: " . $e->getMessage() . "\n";
}
?>

Transfer committed.