SQL Lesson 06 - PHP + MySQL

1. MySQL Connect — mysqli vs PDO

<?php
// ===== mysqli method =====
$conn = mysqli_connect('localhost', 'root', '', 'my_school');

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected!";

// ===== PDO method (recommended) =====
try {
    $pdo = new PDO(
        "mysql:host=localhost;dbname=my_school;charset=utf8mb4",
        'root',       // username
        ''            // password (XAMPP default = blank)
    );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected!";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

PDO = PHP Data Objects — multiple DB support (MySQL, PostgreSQL, SQLite)
mysqli = MySQL ပဲ (MySQL-specific)
Production မှာ password ကို .env file မှာ သိမ်း — code ထဲမထည့်ရ

2. SELECT — Data ဖတ်ယူ

<?php
// ===== PDO — fetch all rows =====
$stmt = $pdo->query("SELECT * FROM users ORDER BY id DESC");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($users as $user) {
    echo $user['name'] . ' - ' . $user['city'] . '<br>';
}

// HTML table ထဲ display
?>
<table>
    <tr><th>Name</th><th>City</th><th>Score</th></tr>
    <?php foreach ($users as $u): ?>
    <tr>
        <td><?= htmlspecialchars($u['name']) ?></td>
        <td><?= htmlspecialchars($u['city']) ?></td>
        <td><?= $u['score'] ?></td>
    </tr>
    <?php endforeach; ?>
</table>

3. Prepared Statements — SQL Injection ကာကွယ်

<?php
// ❌ NEVER DO THIS — SQL Injection ဖြစ်နိုင်
$city = $_GET['city'];
$sql = "SELECT * FROM users WHERE city = '$city'";
// hacker: city = ' OR '1'='1 → gets ALL rows!

// ✅ Prepared Statement — safe
$city = $_GET['city'] ?? '';

$stmt = $pdo->prepare("SELECT * FROM users WHERE city = :city");
$stmt->execute([':city' => $city]);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Multiple parameters
$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE city = :city AND score >= :min_score"
);
$stmt->execute([
    ':city'      => 'Yangon',
    ':min_score' => 80
]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

⚠️ SQL Injection = hacker က SQL code ကို user input ထဲ ထည့်တိုက်တာ
Prepared Statement သုံးရင် parameter ကို plain text အဖြစ်သာ treat တယ် — safe!

4. INSERT — Form Data သိမ်း

<?php
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $name  = trim($_POST['name'] ?? '');
    $email = trim($_POST['email'] ?? '');
    $city  = trim($_POST['city'] ?? '');

    if ($name && filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $stmt = $pdo->prepare(
            "INSERT INTO users (name, email, city) VALUES (:name, :email, :city)"
        );
        $stmt->execute([
            ':name'  => $name,
            ':email' => $email,
            ':city'  => $city
        ]);
        $newId = $pdo->lastInsertId();
        echo "✅ Created user ID: $newId";
    } else {
        echo "❌ Invalid data";
    }
}
?>

5. UPDATE & DELETE

<?php

// UPDATE
$stmt = $pdo->prepare(
    "UPDATE users SET name = :name, city = :city WHERE id = :id"
);
$stmt->execute([
    ':name' => 'Ko Min Updated',
    ':city' => 'Mandalay',
    ':id'   => 1
]);
echo $stmt->rowCount() . " rows updated";

// DELETE
$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->execute([':id' => 5]);
echo $stmt->rowCount() . " rows deleted";

?>

6. Complete CRUD File Structure

// config/db.php — connection file
<?php
function getDB() {
    static $pdo = null;
    if ($pdo === null) {
        $pdo = new PDO(
            "mysql:host=localhost;dbname=my_school;charset=utf8mb4",
            'root', ''
        );
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    return $pdo;
}
?>

// users.php — list + form page
<?php
require_once 'config/db.php';
$pdo = getDB();

// Handle delete action
if (isset($_GET['delete'])) {
    $id = (int)$_GET['delete'];  // cast to int — safe!
    $pdo->prepare("DELETE FROM users WHERE id = ?")->execute([$id]);
    header("Location: users.php");
    exit;
}

// Fetch all users
$users = $pdo->query("SELECT * FROM users ORDER BY id DESC")->fetchAll(PDO::FETCH_ASSOC);
?>

File structure:
📁 my_project/
  📄 index.php — home
  📄 users.php — user list + delete
  📄 user_add.php — add form
  📄 user_edit.php — edit form
  📁 config/
    📄 db.php — database connection

📌 Study Checklist

အပေါ်က concept ကို တစ်ကြောင်းချင်းဖတ်ပြီး example ကို ကိုယ်တိုင်ပြန်ရေးကြည့်ပါ။
Code/command ပါတဲ့ lesson ဆိုရင် value/name/path တစ်ခုခု ပြောင်းပြီး result ဘာကွာလဲ စမ်းပါ။
မမှတ်မိသေးတဲ့ keyword 3 ခုကို notebook ထဲရေးပြီး နောက် lesson မသွားခင် ပြန်ရှင်းကြည့်ပါ။
ပြီးရင် Home / Hub ကိုပြန်သွားပြီး next lesson ဆက်သင်ပါ။

🗄️ SQL Lesson 06 — PHP + MySQL