Build Dynamic Sites with PHP and MySQL

This is step 1 of the PHP web-app path. Build the smallest useful dynamic site first: one table, one list screen, one create form, and one database boundary that does not leak into the template.

A small PHP database app needs fewer moving parts than people think, but it needs the right boundaries early. The usual mistake is starting with screens and leaving the data model until the first bug proves it matters.

Treat the app like a small workshop. The public route is the front counter, query functions control the storeroom, and templates arrange the display. Start with the database, then add PHP routes around the shape it gives you.

Guide Path

This project path adds one layer at a time:

1. Dynamic site with PHP and MySQL.
2. News and comments CRUD.
3. Search application.
4. Custom API capstone.

Pause for authentication and session hardening before exposing account-specific API behavior. That material has its own reference in PHP authentication and sessions.

A Small App Shape

For a basic notes app, the minimum structure might look like this:

public/
  index.php
  css/
src/
  db.php
  notes.php
  csrf.php
views/
  layout.php
  notes-index.php
  notes-new.php
  notes-edit.php

That is enough for a small app. public/index.php receives the request, src/db.php owns the PDO connection, src/notes.php owns note queries, and views/ renders HTML.

Once the app grows, you can move to Laravel, Symfony, Slim, CakePHP, or Fat-Free Framework. The early boundary still pays off because you already separated request handling, database access, and rendering.

Connection File

Put the database connection in one file and read credentials from the environment. A tutorial can show placeholders, but a real app should not commit database passwords.

<?php

function db(): PDO
{
    $host = getenv("DB_HOST") ?: "127.0.0.1";
    $name = getenv("DB_NAME") ?: "notes_app";
    $user = getenv("DB_USER") ?: "app_user";
    $pass = getenv("DB_PASS") ?: "";

    return new PDO(
        "mysql:host={$host};dbname={$name};charset=utf8mb4",
        $user,
        $pass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ],
    );
}

Exception mode makes failed queries visible where the app can log and handle them. The PDO and MySQL guide explains the setting in depth; this project applies it at the shared connection boundary.

Schema First

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    INDEX notes_user_created_idx (user_id, created_at),
    CONSTRAINT notes_user_fk
        FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE
);

If your database supports foreign keys for the engine you use, add them. The database should know that a note belongs to a user. That is the shape of the data, while authorization remains an application responsibility.

For MySQL, make sure the storage engine and deployment settings enforce the constraints you expect. MyISAM does not support foreign keys, so a schema that depends on referential integrity should use InnoDB and keep foreign-key checks enabled.

If this is your first PHP and MySQL project, use InnoDB and prove the constraint exists before writing application code around it. A quick SHOW CREATE TABLE notes; is often enough to catch a migration that looked fine in the editor but landed differently on the server.

Query Functions

Do not scatter SQL across templates; put the database operations behind small functions with boring names.

<?php

function recentNotes(PDO $pdo, int $userId): array
{
    $statement = $pdo->prepare(
        "SELECT id, title, created_at
         FROM notes
         WHERE user_id = :user_id
         ORDER BY created_at DESC
         LIMIT 20",
    );

    $statement->execute(["user_id" => $userId]);

    return $statement->fetchAll();
}

function createNote(PDO $pdo, int $userId, string $title, string $body): void
{
    $statement = $pdo->prepare(
        "INSERT INTO notes (user_id, title, body)
         VALUES (:user_id, :title, :body)",
    );

    $statement->execute([
        "user_id" => $userId,
        "title" => $title,
        "body" => $body,
    ]);
}

This is plain enough to look uninteresting, which is exactly the advantage. You can test these functions without pretending the template is the model layer.

The query file is the workshop storeroom: request handlers can ask for a defined operation, but templates never wander in and select rows for themselves.

Add update and delete functions while the table shape is still small:

<?php

function updateNote(PDO $pdo, int $userId, int $noteId, string $title, string $body): bool
{
    $statement = $pdo->prepare(
        "UPDATE notes
         SET title = :title,
             body = :body,
             updated_at = CURRENT_TIMESTAMP
         WHERE id = :id AND user_id = :user_id",
    );

    $statement->execute([
        "id" => $noteId,
        "user_id" => $userId,
        "title" => $title,
        "body" => $body,
    ]);

    return $statement->rowCount() === 1;
}

function deleteNote(PDO $pdo, int $userId, int $noteId): bool
{
    $statement = $pdo->prepare(
        "DELETE FROM notes
         WHERE id = :id AND user_id = :user_id",
    );

    $statement->execute([
        "id" => $noteId,
        "user_id" => $userId,
    ]);

    return $statement->rowCount() === 1;
}

Notice the user_id check on every write. A multi-user app must match the note and its owner together. The tutorial uses requireUserId(), defined in the linked authentication and sessions reference, while the write query preserves the correct authorization shape.

Request Handling

The front controller should route requests and call the right function, with as little cleverness as possible.

<?php

require __DIR__ . "/../src/db.php";
require __DIR__ . "/../src/notes.php";
require __DIR__ . "/../src/csrf.php";

session_start();
$userId = requireUserId();
$path = parse_url($_SERVER["REQUEST_URI"], PHP_URL_PATH);
$method = $_SERVER["REQUEST_METHOD"];

if ($path === "/notes" && $method === "GET") {
    $notes = recentNotes(db(), $userId);
    require __DIR__ . "/../views/notes-index.php";
    return;
}

if ($path === "/notes" && $method === "POST") {
    verifyCsrfToken($_POST["csrf_token"] ?? "");

    $title = trim($_POST["title"] ?? "");
    $body = trim($_POST["body"] ?? "");
    $errors = validateNoteInput($title, $body);

    if ($errors !== []) {
        $old = ["title" => $title, "body" => $body];
        require __DIR__ . "/../views/notes-new.php";
        return;
    }

    createNote(
        db(),
        $userId,
        $title,
        $body,
    );

    header("Location: /notes", true, 303);
    return;
}

if ($path === "/notes/delete" && $method === "POST") {
    verifyCsrfToken($_POST["csrf_token"] ?? "");

    $deleted = deleteNote(
        db(),
        $userId,
        (int) ($_POST["id"] ?? 0),
    );

    if (! $deleted) {
        http_response_code(404);
        exit("Note not found");
    }

    header("Location: /notes", true, 303);
    return;
}

http_response_code(404);
require __DIR__ . "/../views/404.php";

This plain controller exposes the request shape that frameworks organize for larger apps. Laravel and Symfony wrap the same concerns in routing, controllers, requests, validation, middleware, and responses.

As an exercise, add GET /notes/new, GET /notes/edit, and POST /notes/update with the same dispatch pattern before introducing a router.

The front counter now checks identity, CSRF, and validation before any write reaches the storeroom. That order keeps authorization and request rules visible instead of burying them inside the template.

The 303 redirect after a successful write is intentional. It keeps a browser refresh from resubmitting the same form. Old PHP tutorials often skipped this, then left readers wondering why a duplicated record appeared after hitting reload.

Form Rendering

The new-note form should be ordinary HTML. The important part is that the template receives the current values and error messages instead of pulling directly from $_POST.

<form method="post" action="/notes">
    <input type="hidden" name="csrf_token" value="<?= htmlspecialchars(csrfToken(), ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?>">

    <label>
        Title
        <input
            name="title"
            value="<?= htmlspecialchars($old["title"] ?? "", ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?>"
            maxlength="255"
            required
        >
    </label>

    <?php if (isset($errors["title"])): ?>
        <p class="error"><?= htmlspecialchars($errors["title"], ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?></p>
    <?php endif; ?>

    <label>
        Body
        <textarea name="body" required><?= htmlspecialchars($old["body"] ?? "", ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?></textarea>
    </label>

    <button type="submit">Save note</button>
</form>

The template is allowed to be plain, but it should not be careless. Keep the form action explicit, keep the method as POST for writes, and escape both the old input and the error message before rendering them.

For edit screens, use the same template shape with a different action and the current row as the starting values:

<form method="post" action="/notes/update">
    <input type="hidden" name="csrf_token" value="<?= htmlspecialchars(csrfToken(), ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?>">
    <input type="hidden" name="id" value="<?= (int) $note["id"] ?>">
    <!-- same title and body fields -->
    <button type="submit">Update note</button>
</form>

The hidden ID tells the handler which row the browser requested. Authorization comes from the WHERE id = :id AND user_id = :user_id clause, which limits the update to the authenticated user's note.

Validation Belongs Before SQL

Validate before writing so PHP can produce useful errors while the database enforces the final data rules.

<?php

function validateNoteInput(string $title, string $body): array
{
    $errors = [];

    if ($title === "") {
        $errors["title"] = "Give the note a title.";
    }

    if (mb_strlen($title) > 255) {
        $errors["title"] = "Keep the title under 255 characters.";
    }

    if ($body === "") {
        $errors["body"] = "Write something before saving.";
    }

    return $errors;
}

Database constraints protect the stored data, while validation helps the human fix the form before the write is attempted.

The full form validation in PHP guide separates those layers: browser hints, server-side checks, CSRF, database constraints, and output escaping.

CSRF Protection

A same-site form can still be submitted by a hostile page unless the server checks that the request came from a form it issued. A small PHP app can start with a session-backed CSRF token.

<?php

function csrfToken(): string
{
    if (empty($_SESSION["csrf_token"])) {
        $_SESSION["csrf_token"] = bin2hex(random_bytes(32));
    }

    return $_SESSION["csrf_token"];
}

function verifyCsrfToken(string $token): void
{
    $sessionToken = $_SESSION["csrf_token"] ?? null;

    if (
        ! is_string($sessionToken)
        || ! hash_equals($sessionToken, $token)
    ) {
        http_response_code(403);
        exit("Invalid form token.");
    }
}

Then render the token in the form:

<input type="hidden" name="csrf_token" value="<?= htmlspecialchars(csrfToken(), ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?>">

Verify the submitted token before the write reaches the database:

verifyCsrfToken($_POST["csrf_token"] ?? "");

CSRF protection closes the cross-site form risk. Authentication still identifies the current user, and authorization still decides which note that user may change.

Escape Output Every Time

Prepared statements protect values as they enter SQL. HTML output needs its own protection, so escape each user-supplied note title before rendering it.

<?php foreach ($notes as $note): ?>
    <article>
        <h2><?= htmlspecialchars($note["title"], ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?></h2>
        <time><?= htmlspecialchars($note["created_at"], ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?></time>
    </article>
<?php endforeach; ?>

Plain PHP repeats this call because every data-to-HTML crossing needs protection. A framework template engine can escape by default, while a small hand-rolled app keeps the call beside the output.

Transactions

The first version creates one note row at a time. Later, you may create a note, attach tags, and write an audit event in one request. At that point, use a transaction so the database does not keep half the change.

<?php

function createNoteWithTags(PDO $pdo, int $userId, string $title, string $body, array $tags): void
{
    $pdo->beginTransaction();

    try {
        createNote($pdo, $userId, $title, $body);
        $noteId = (int) $pdo->lastInsertId();

        foreach ($tags as $tag) {
            attachTag($pdo, $noteId, $tag);
        }

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Transactions are one of those features that look advanced until you have cleaned up a partial write by hand. Learn the shape early, even if the first CRUD screen does not need it.

Passwords and Sessions

The schema above includes password_hash because most database-backed sites eventually need users. Use PHP's password_hash() and password_verify() rather than inventing a hashing scheme. The login/session details live in PHP authentication and sessions, but the database app should not teach a pattern that boxes you into plain passwords later.

For the project path, treat account handling as the point where this beginner app hands off to PHP security fundamentals and the dedicated auth-session material. Start with the notes table and add real users only when you can protect the session, CSRF token, password reset, and authorization checks properly.

What Counts as Done

This first project is finished when the app can list notes, create a note, edit a note, delete a note, reject invalid input, redirect after successful writes, escape output, and keep all SQL inside src/notes.php. That gives you a useful stopping point before the project starts collecting features.

Do not add comments, tags, search, uploads, or account settings until the small shape is clean. Those features arrive in the next tutorials, and they are much easier to follow when the first app has one table and one responsibility.

Use this quick review checklist before moving on:

1. One file explains the database connection.
2. One file explains note queries and writes.
3. Templates receive data instead of querying the database.
4. Writes use POST, validation, CSRF, and a redirect.
5. Every rendered user value passes through htmlspecialchars().
6. Each write filters by the current user, even in tutorial form.

The checklist is deliberately plain because most bad PHP database apps go wrong by skipping one of those basics, then adding a framework later to hide the mess.

Local Verification

Do a manual pass before the next tutorial. Create two notes, edit one, delete the other, refresh after each write, and confirm no duplicate row appears. Then submit an empty title and confirm the form shows the old body instead of discarding it. Finally, type a title such as <script>alert(1)</script> and confirm the browser renders the value as text instead of executing it.

That final check catches the exact mistake old PHP snippets made for years. SQL injection and XSS are separate failures, and a prepared statement protects only the database side. Safe storage can still feed unsafe output.

Also test the authorization shape, even with a fake user helper. Change a note ID in the edit or delete form and make sure the write function still filters by user_id. If rowCount() returns zero, treat that as either "not found" or "not yours." Do not build a branch where the browser chooses which user's row to change.

Once those checks pass, the app is ready for the next layer: a second table for comments, then search, then an API. That order is slower than bolting on features at random, but it keeps each new lesson attached to a boundary the reader already understands.

Keep a short README beside the project with the database name, migration command, environment variables, and the routes you expect to work. Future you will be grateful, and future you is always less patient than present you expects.

Common Pitfalls & Debugging

A Write Changes the Wrong User's Note

Symptom: changing a hidden note ID updates or deletes another account's row. Cause: the query filters by note ID alone and trusts the browser to supply ownership. Fix: load the authenticated user on the server, filter writes by both id and user_id, and treat zero affected rows as unavailable.

Saved Text Runs as HTML

Symptom: a saved title changes the page markup or runs a script when the note list opens. Cause: stored text crossed into HTML without context-appropriate escaping. Fix: render every text value through htmlspecialchars() with UTF-8 and quote handling at the output boundary.

Refreshing Creates a Duplicate Row

Symptom: reloading after a successful form post inserts the same note again. Cause: the handler renders a response directly after the write, so the browser repeats the POST on refresh. Fix: return a 303 redirect to a GET route after the transaction succeeds.

Frequently Asked Questions

Can one PDO connection serve every request handler?

One configured PDO connection can serve every request handler in a small app. Pass it to the query functions that need it, and centralize setup so credentials, character encoding, error mode, and native prepared-statement behavior cannot drift between routes.

Does the example handle two tabs editing the same note?

No. The code shown has no conflict handling, so whichever tab saves last silently overwrites the other. Comparing a version or updated_at value before saving is the deliberate next step this tutorial leaves out.

Does moving to a framework mean rewriting the queries?

Not usually. The query functions and the PDO boundary generally move into the framework's model or repository layer with little change, because the SQL and its bindings were never tied to the plain routing around them.

Can a trigger stop a note changing owner after creation?

Yes, a trigger can reject an update that changes a protected column. For a single-table rule like this an application check is usually simpler to write, test, and find later than logic hidden in the database.

Should the database connection function fall back to a default host if an environment variable is missing?

Not in production. A fallback such as 127.0.0.1 is convenient for local development, but the same fallback can mask a missing environment variable in production, silently connecting to the wrong database instead of failing with a clear error.

Why do updateNote and deleteNote check that exactly one row was affected?

Exactly one row is the only outcome that proves the write matched the intended note. Zero means the note does not exist or does not belong to that user. More than one would mean the WHERE clause matched too broadly, which a primary key lookup should never do.

Does the CSRF token change every time a page loads?

No. The token is generated once and stored in the session, then reused for every form until the session ends or the code regenerates it deliberately, such as at login. Generating a new token on every page load would invalidate forms still open in other tabs.

When to Add a Framework

Add a framework when the boundaries become repeated work:

• You need routing beyond a few paths.
• You need middleware for sessions, auth, CSRF, and permissions.
• You need migrations and environment-aware config.
• You need tests around requests.
• You need a team to understand the project quickly.

Until then, the small app can stay small. The danger is pretending small means casual. A small PHP app still needs prepared statements, password hashing, CSRF protection, output escaping, and a schema that matches the data.

Keep learning from the plain version while the routing file remains readable. Move to a framework before every route requires another hand-built copy of validation, CSRF, layout, middleware, and error handling.

Self-Check

Predict the output from this ownership check:

<?php

function ownershipLabel(int $sessionUserId, int $rowUserId): string
{
    return $sessionUserId === $rowUserId ? "allowed" : "denied";
}

echo ownershipLabel(7, 7);

1. Predict the output: Which word does the snippet print?
2. Multiple choice: Which SQL clause protects a note write: WHERE id = :id, or WHERE id = :id AND user_id = :user_id?
3. Multiple choice: Which control protects the HTML boundary: a prepared statement, or htmlspecialchars() at render time?
4. Predict the output: Which word prints for ownershipLabel(7, 9)?
5. Multiple choice: Which response prevents a refresh from repeating a successful POST: rendering the form again, or a 303 redirect?

Answers

1. allowed. Both integer arguments are 7, so the strict comparison selects the first label.
2. WHERE id = :id AND user_id = :user_id. The row must match both the requested note and the authenticated owner.
3. htmlspecialchars() at render time. Prepared statements protect SQL values, while HTML escaping protects the output context.
4. denied. The session user ID and row owner ID differ.
5. A 303 redirect. The browser follows it with a GET, so a later refresh does not repeat the write.

Next Steps

If you want the same app shape with a more interactive surface, build a mini chat app with PHP and pay attention to the polling, CSRF, and output-escaping boundaries.

Read PHP security fundamentals before the app accepts real users, then read schema design basics before the tables become hard to change.

Next, add a second table and build the news and comments CRUD step.