PDO ile veritabanları ve ziyaretçi defteri projesi

<?php

$pdo = new PDO(
    'mysql:host=localhost;dbname=educora;charset=utf8mb4',
    'educora_app',
    getenv('DB_PASSWORD'),
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

// SQLite: a file or memory, no server needed
$sqlite = new PDO('sqlite:' . __DIR__ . '/app.sqlite');

<?php

$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$pdo->exec('CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    done INTEGER NOT NULL DEFAULT 0
)');

$insert = $pdo->prepare('INSERT INTO tasks (title) VALUES (:title)');
foreach (['Learn PDO', 'Build a guestbook', 'Read about Laravel'] as $title) {
    $insert->execute(['title' => $title]);
}
echo "Last id: ", $pdo->lastInsertId(), "\n";

$pdo->prepare('UPDATE tasks SET done = 1 WHERE id = ?')->execute([1]);

$delete = $pdo->prepare('DELETE FROM tasks WHERE id = ?');
$delete->execute([3]);
echo "Deleted rows: ", $delete->rowCount(), "\n";

foreach ($pdo->query('SELECT id, title, done FROM tasks ORDER BY id') as $row) {
    $mark = $row['done'] ? 'x' : ' ';
    echo "[$mark] {$row['id']}. {$row['title']}\n";
}

Last id: 3
Deleted rows: 1
[x] 1. Learn PDO
[ ] 2. Build a guestbook

<?php

$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE scores (name TEXT, score INTEGER)");
$pdo->exec("INSERT INTO scores VALUES ('Aysel', 92), ('Murad', 78), ('Leyla', 95)");

$count = $pdo->query('SELECT COUNT(*) FROM scores')->fetchColumn();
echo "Students: $count\n";

$best = $pdo->query('SELECT * FROM scores ORDER BY score DESC')->fetch(PDO::FETCH_OBJ);
echo "Best: {$best->name} ({$best->score})\n";

$pairs = $pdo->query('SELECT name, score FROM scores ORDER BY name')
    ->fetchAll(PDO::FETCH_KEY_PAIR);
print_r($pairs);

Students: 3
Best: Leyla (95)
Array
(
    [Aysel] => 92
    [Leyla] => 95
    [Murad] => 78
)

<?php
declare(strict_types=1);
session_start();

$pdo = new PDO('sqlite:' . __DIR__ . '/guestbook.sqlite');
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$pdo->exec('CREATE TABLE IF NOT EXISTS entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    message TEXT NOT NULL,
    created_at TEXT NOT NULL
)');

function e(string $value): string
{
    return htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
}

$_SESSION['csrf'] ??= bin2hex(random_bytes(32));
$errors = [];

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (!hash_equals($_SESSION['csrf'], (string) ($_POST['csrf'] ?? ''))) {
        http_response_code(403);
        exit('Invalid CSRF token');
    }
    $name = trim((string) ($_POST['name'] ?? ''));
    $message = trim((string) ($_POST['message'] ?? ''));
    if ($name === '' || mb_strlen($name) > 50) {
        $errors[] = 'Name must be 1-50 characters';
    }
    if ($message === '' || mb_strlen($message) > 500) {
        $errors[] = 'Message must be 1-500 characters';
    }
    if ($errors === []) {
        $stmt = $pdo->prepare('INSERT INTO entries (name, message, created_at) VALUES (?, ?, ?)');
        $stmt->execute([$name, $message, date('Y-m-d H:i')]);
        header('Location: /');
        exit;
    }
}

$entries = $pdo->query('SELECT * FROM entries ORDER BY id DESC LIMIT 20')->fetchAll();
?>

<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Guestbook</title></head>
<body>
<h1>Guestbook</h1>

<?php foreach ($errors as $error): ?>
    <p style="color: red"><?= e($error) ?></p>
<?php endforeach; ?>

<form method="post">
    <input type="hidden" name="csrf" value="<?= e($_SESSION['csrf']) ?>">
    <input name="name" placeholder="Your name" maxlength="50" required>
    <textarea name="message" placeholder="Your message" maxlength="500" required></textarea>
    <button>Sign the guestbook</button>
</form>

<?php foreach ($entries as $entry): ?>
    <article>
        <strong><?= e($entry['name']) ?></strong> · <?= e($entry['created_at']) ?>
        <p><?= nl2br(e($entry['message'])) ?></p>
    </article>
<?php endforeach; ?>
</body>
</html>

// routes/web.php
Route::get('/', [EntryController::class, 'index']);
Route::post('/entries', [EntryController::class, 'store']);

// app/Http/Controllers/EntryController.php
class EntryController extends Controller
{
    public function index()
    {
        return view('entries', ['entries' => Entry::latest()->take(20)->get()]);
    }

    public function store(Request $request)
    {
        $data = $request->validate([
            'name' => 'required|max:50',
            'message' => 'required|max:500',
        ]);
        Entry::create($data);
        return redirect('/');
    }
}

<form method="post" action="/entries">
    @csrf
    <input name="name">
    <textarea name="message"></textarea>
    <button>Send</button>
</form>

@foreach ($entries as $entry)
    <p><strong>{{ $entry->name }}</strong>: {{ $entry->message }}</p>
@endforeach