Databases with PDO and a guestbook project

In this lesson you will learn

Connect to a database with PDO and read results in different fetch modes
Perform CRUD operations with prepared statements and prevent SQL injection
Build a complete guestbook with PDO, SQLite, sessions and escaping
Describe Laravel's routes, controllers, Blade and Eloquent in general terms

As soon as a script finishes, all its variables disappear. But guestbook entries, users and orders must be kept for years — for that you need a database. The modern way to work with databases in PHP is PDO (PHP Data Objects): the same code works with MySQL, PostgreSQL, SQLite and other databases. In this final lesson you will learn PDO and build a guestbook that brings together everything from the course.

Connecting with PDO

To connect you need a DSN string: it gives the database type, its address and its name. For MySQL always add charset=utf8mb4 — then Azerbaijani, Russian and Turkish letters, and even emoji, are stored correctly. SQLite needs no server: the whole database is one file, and sqlite::memory: creates a database in memory only — ideal for experiments.

PHP

<?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');

Don't write the password into the code — read it from an environment variable (getenv) or a config file.

ERRMODE_EXCEPTION — a failed query throws a PDOException (this is the default in PHP 8, but it is good to write it explicitly).
FETCH_ASSOC — rows come back as associative arrays like ['name' => ...].
EMULATE_PREPARES => false — MySQL uses real prepared statements.

SQL injection and prepared statements

If you paste what the user typed straight into the SQL text, it can change the query itself. For example, if someone types ' OR '1'='1 instead of an e-mail, the condition is always true and the query returns every user. This is SQL injection — one of the most dangerous website vulnerabilities. In a prepared statement, the SQL text and the data are sent to the server separately, so the data is never executed as SQL.

Vulnerable: SQL injection

$email = $_POST['email'];
$sql = "SELECT * FROM users WHERE email = '$email'";
$user = $pdo->query($sql)->fetch();

Safe: prepared statement

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ?');
$stmt->execute([$_POST['email']]);
$user = $stmt->fetch();

? is a placeholder: the data is passed to execute separately. There are named placeholders too, like :email: execute(['email' => $email]).

CRUD and fetching results

CRUD stands for the four basic operations: Create (INSERT), Read (SELECT), Update (UPDATE) and Delete (DELETE). The program below keeps a to-do list in an in-memory SQLite database and runs in the terminal as it is. A statement prepared once can be executed many times with different data.

PHP

<?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";
}

Expected output

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

PHP

<?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);

Expected output

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

Method / mode	What it returns
`fetch()`	the next row, or `false` when no rows are left
`fetchAll()`	all rows as an array
`fetchColumn()`	the first column of the next row — handy for `COUNT(*)`
`PDO::FETCH_OBJ`	a row as an object: `$row->name`
`PDO::FETCH_KEY_PAIR`	a `key => value` array from two columns
`rowCount()`	how many rows an `UPDATE`/`DELETE` changed

Project: a guestbook

Now let's put it all together: a visitor writes a name and a message, PHP validates them, stores them in an SQLite database and shows the latest 20 entries. The project uses a session, a CSRF token, a prepared statement, a PRG redirect and htmlspecialchars. The whole project is one index.php file; we show it in two parts.

PHP

<?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();
?>

index.php, part 1: the database, validation and saving an entry

PHP

<!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>

index.php, part 2: the template — foreach (...): and endforeach; are a handy syntax inside HTML

1
Create a folder
Create a guestbook folder and put both parts, one after the other, into a single index.php file.
2
Check SQLite
php -m should list pdo_sqlite. On Windows, if needed, remove the ; in front of extension=pdo_sqlite in php.ini.
3
Start the server
Run php -S localhost:8000 in the folder and open http://localhost:8000. The guestbook.sqlite file is created automatically on the first request.
4
Test it and try to break it
Type <b>Hi</b> as the name: it must appear as plain text, not in bold. After sending the form, refresh the page — the entry must not be added a second time.

Next step: Laravel

In real projects nobody writes all of this by hand every time — they use a framework. The most popular PHP framework is Laravel. A new project is created with composer create-project laravel/laravel guestbook, and php artisan serve starts the development server. In Laravel, routes connect an address to a controller method, a controller handles the request, Blade templates build the HTML, and Eloquent presents database tables as PHP classes.

PHP

// 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('/');
    }
}

The same guestbook in Laravel: validation, CSRF protection and prepared statements are built into the framework (the Entry model lists name and message in its $fillable property)

HTML

<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

resources/views/entries.blade.php: {{ }} escapes text automatically, and @csrf adds the hidden token field

Task	Plain PHP	Laravel
Routing	`$_SERVER['REQUEST_METHOD']`	`Route::get(...)`, `Route::post(...)`
Escaping	`htmlspecialchars()`	`{{ $value }}`
CSRF	`random_bytes` + `hash_equals`	`@csrf`
Database	`PDO::prepare()`	`Entry::create($data)`
Creating tables	`CREATE TABLE`	migrations: `php artisan migrate`

A framework is not magic: it automates the same ideas you wrote by hand in this course.

Key points

PDO gives one interface for many databases; you connect with a DSN string and add charset=utf8mb4 for MySQL.
User data reaches SQL only through prepare and execute with placeholders (?, :name) — this prevents SQL injection.
fetch, fetchAll, fetchColumn and the modes FETCH_ASSOC, FETCH_OBJ and FETCH_KEY_PAIR give you results in the shape you need.
A secure web app combines a CSRF token, server-side validation, prepared statements, a PRG redirect and escaped output.
Laravel automates the same ideas with routes, controllers, Blade and Eloquent.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

What is the main benefit of prepared statements?