Build Dynamic Sites with PHP and MySQL

A dynamic PHP website reads data from MySQL and uses it to generate HTML. This project in the PHP web-app path builds a notes app with a list, forms, and queries that restrict each note to its owner.

The connection defaults below are for a local demo. In production, require explicit database settings and stop with a generic error if they are missing.

You need PHP with pdo_mysql and mbstring, a running MySQL database, and basic HTML and SQL knowledge. These snippets show the main parts of the app rather than a complete downloadable project.

Keep request handling, database queries, and HTML templates in separate files so you can change one without searching through the others.

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.

How Should a Small PHP and MySQL App Be Organized?

A small PHP and MySQL notes app can separate the public entry point, query functions, and HTML views into three folders:

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.

How Does PHP Connect to the MySQL Database?

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.

Create the Users and Notes Tables

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.

How Does PHP Create and Read Notes?

PHP creates and reads notes through prepared INSERT and SELECT statements. Put those queries in functions that accept the current user’s ID.

<?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,
    ]);
}

These functions can be checked independently of the HTML templates.

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, and each write checks ownership in its WHERE clause. An unchanged UPDATE can return zero; the edit handler must distinguish that from a missing note if it needs to report success accurately.

How Does PHP Route Requests to the Notes App?

The PHP front controller checks the request path and method, then calls the matching note function. Load the authentication helper from the linked session guide before using requireUserId().

<?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 = is_string($_POST["title"] ?? null) ? trim($_POST["title"]) : "";
    $body = is_string($_POST["body"] ?? null) ? 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 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.

How Does the PHP Form Keep Entered Values?

The PHP form keeps entered values by receiving an $old array from the handler and escaping each value when rendering it. Error messages should appear beside the affected field.

<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>
        <?php if (isset($errors["body"])): ?>
            <p><?= htmlspecialchars($errors["body"], ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8") ?></p>
        <?php endif; ?>
    </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.

What Should PHP Validate Before Saving a Note?

PHP should reject an empty title or body and enforce lengths that fit the MySQL columns before saving a note.

<?php

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

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

    if (mb_strlen($title, "UTF-8") > 255) {
        $errors["title"] = "Use no more than 255 characters for the title.";
    }

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

    if (strlen($body) > 65535) {
        $errors["body"] = "Keep the note body within 65,535 bytes.";
    }

    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(mixed $token): void
{
    $sessionToken = $_SESSION["csrf_token"] ?? null;

    if (
        ! is_string($sessionToken)
        || ! is_string($token)
        || ! 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.

When Does the Notes App Need a Transaction?

The PHP notes app needs a transaction when several related writes must all succeed together. 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) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

Use a transaction when two related database changes must either both succeed or both be rolled back.

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.

Check the Notes App Before Adding Features

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 clear purpose.

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.

Check these behaviors before adding more features.

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. For DELETE, zero affected rows means no owned note matched. For UPDATE, zero can also mean nothing changed, so do not treat it alone as proof that the note is missing. 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 check whether the note belongs to that user. An UPDATE can affect zero rows when the submitted values are unchanged.

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?

A small PHP app can reuse one PDO connection during a request. Keep connection settings in one helper and pass the resulting object to query functions. Separate HTTP requests normally create their own connections.

Does this PHP notes app prevent conflicting edits?

This PHP notes app does not prevent conflicting edits. The last save wins when two tabs edit the same note. Add a version column and check it during UPDATE if you need to reject stale edits.

Does moving a PHP app to a framework require new SQL queries?

Moving a PHP app to a framework does not necessarily require new SQL queries. Existing PDO queries can often be retained, although connection management and where the functions live may change.

Can a MySQL trigger stop a note from changing owners?

A MySQL trigger can reject an UPDATE that changes a note’s owner. Application permission checks are still needed to decide who may edit the note.

Should a PHP database connection use default production credentials?

A production PHP database connection should fail clearly when required configuration is missing. Local defaults can be convenient during development, but they should not silently select a different production database.

What do affected-row counts mean for PHP note updates and deletes?

MySQL normally counts changed rows for UPDATE and deleted rows for DELETE. In this PHP app, updateNote() returns true only when data changes; false can also mean the submitted values were already saved. deleteNote() returns false when no row matches the note and owner.

Does this PHP notes app change its CSRF token on every page load?

This PHP notes app reuses its session CSRF token across page loads. The token changes when the session ends or the application deliberately regenerates it. Changing it on every page load would invalidate forms open in other tabs.

When Should a PHP App Add a Framework?

A PHP app benefits from a framework when routing, validation, sessions, and error handling become repetitive:

• 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.

Add Comments or Chat After the Notes App Works

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.