Skip to content
Tutorials Center
My learning

Topics

Lesson 130 of 147 ยท Projects

Project: Mini Blog with PDO

Build a mini blog in PHP in four stages - a PDO SQLite schema with slugs, a tiny router for list and post pages, pagination, and escaped reader comments.

A blog is the classic second web project, and it's a step up from a to-do list in every direction. Posts have readable URLs like /posts/hello-world instead of ids. The home page must show the newest posts a page at a time. Readers can leave comments, which means storing text from strangers and showing it to everyone else. And all of it lives in a database with two related tables.

In this project you'll build a mini blog in four stages, each a complete program. First you'll create the database schema and generate slugs, the URL-friendly names of posts. Then you'll write a tiny router that maps URLs to a list page and a post page, with a proper 404 for anything else. Then you'll add pagination. Finally you'll add comments, with validation, escaping and the POST-redirect-GET pattern. Along the way you'll use PDO, prepared statements, regular expressions and the routing ideas from Front Controllers and Routing.

Our editor runs PHP on the command line, so there's no browser and no web server. The demos simulate requests by calling the app with a method and a URL such as GET /posts/hello-world, and print the response. The database is SQLite in memory, created fresh on every run. The last section shows how to serve the blog for real.

The plan

The blog has two tables:

TEXT
posts                          comments
-----------------------        -----------------------
id            INTEGER PK       id          INTEGER PK
slug          TEXT UNIQUE      post_id     INTEGER โ†’ posts.id
title         TEXT             author      TEXT
body          TEXT             body        TEXT
published_at  TEXT             created_at  TEXT

and four routes:

Method Path Page
GET / and /?page=2 the newest posts, five per page
GET /posts/{slug} one post with its comments
POST /posts/{slug}/comments add a comment, then redirect back to the post
any anything else 404 Not Found (or 405 for a wrong method)

As in the previous projects, SQL lives in a repository class, HTML is produced by small view functions that escape everything, and the app returns responses as data (status, headers, body) instead of printing them directly. That keeps every stage testable with simulated requests.

Stage 1: the schema and slugs

A slug is the part of a URL that names a page: the post "Why I Switched to Tabs (and Back)" gets the slug why-i-switched-to-tabs-and-back. Good slugs are lowercase, use only letters, digits and hyphens, and never change once published, because other sites link to them.

The slugify function builds one in four steps:

  1. Transliterate accented letters to plain ASCII with iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', ...), so Crรจme brรปlรฉe becomes something close to Creme brulee. //TRANSLIT asks for the closest ASCII spelling, and //IGNORE drops characters that have none, such as Japanese letters. The exact result depends on the iconv library PHP was built with: ours turns accents into separate marks like ` and ^, others drop them, and some return false for //IGNORE. The next step and the false check handle all of these.
  2. Remove every character that isn't a letter, digit, space or hyphen.
  3. Replace each run of spaces and hyphens with a single hyphen, and lowercase the result.
  4. Fall back to post if nothing is left, which happens for a title written entirely in, say, Japanese. (A real blog might keep Unicode slugs instead; modern browsers display them fine.)

Two posts can have the same title, but slugs must be unique, which the UNIQUE constraint in the table guarantees. uniqueSlug checks the database and appends -2, -3 and so on until it finds a free slug.

PHP Stage 1: tables, slugs and seed posts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The slug table shows each rule at work: punctuation disappears (hello-world), surrounding spaces and parentheses vanish (why-i-switched-to-tabs-and-back), accented letters are transliterated (creme-brulee-strasse), and the Japanese title and the title made only of hyphens both fall back to post. Then three posts are created. The second "Hello, World!" becomes hello-world-2, and "Hello World" (no comma) slugifies to the same hello-world, so it gets hello-world-3. Finally, inserting a duplicate slug directly is refused by the database with an SQLSTATE[23000] integrity constraint error. The UNIQUE constraint is the real guarantee; uniqueSlug just makes collisions friendly.

A few schema details matter later:

  • published_at is stored as YYYY-MM-DD HH:MM:SS text. In that format, sorting the strings sorts the dates correctly, and comparing with <= works too.
  • REFERENCES posts(id) ON DELETE CASCADE links each comment to its post and deletes the comments when the post is deleted. SQLite only enforces foreign keys after PRAGMA foreign_keys = ON, which you run once per connection. MySQL and PostgreSQL enforce them by default.
  • The index on comments.post_id makes "all comments for post 7" fast even with millions of comments.

Stage 2: a router and two pages

A router matches the request's method and path against a list of routes and calls the matching handler. Ours stores each route as a regular expression. The pattern /posts/{slug} is turned into #^/posts/(?<slug>[a-z0-9-]+)$#, where (?<slug>...) is a named capture group, so the handler receives the slug by name. The pattern only allows the characters a slug can contain; anything else simply doesn't match and becomes a 404.

The router also tells "not found" apart from "wrong method". If a path matches some route but not with this method (say POST /), the right answer is 405 Method Not Allowed with an Allow header, not a 404.

The PostRepository has two queries. latest() returns posts with published_at <= :now, newest first, which also gives the blog scheduled posts for free: a post dated in the future stays hidden until its time comes. findBySlug() applies the same rule, so nobody can read a scheduled post early by guessing its URL. The current time is passed in rather than read inside, so the demo is predictable.

The views escape every value with e(). The post body is plain text: it's split into paragraphs at blank lines, each paragraph is escaped, and single line breaks become <br> with nl2br. Escaping comes first, and the <p> and <br> tags are added afterward, so the only tags in the output are ours. Dates are formatted with DateTimeImmutable.

PHP Stage 2: routing to list and post pages

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The home page lists two posts, newest first. The post dated December 2026 is missing because "now" is April 15, and GET /posts/coming-soon is a 404 for the same reason. GET /posts/hello-world shows the post with its two paragraphs. /posts/Hello-World is a 404 because the route only allows lowercase slugs, /posts/hello-world/edit matches no route at all, and POST / gets a 405 with Allow: GET.

The second post was inserted with a deliberately nasty slug and title. In the list, the link's href and text are escaped (tags-are-not-&lt;b&gt;bold&lt;/b&gt; and Tags &amp; &lt;b&gt;Escaping&lt;/b&gt;), so the HTML stays intact. Its URL can't be requested at all, since < isn't allowed by the route pattern. That's a hint that slugs should only ever come from slugify, never be typed in by hand.

Some PHP details in the router are worth a second look:

  • preg_match fills $matches with both numbered and named groups. array_filter(..., 'is_string', ARRAY_FILTER_USE_KEY) keeps only the named ones, so $params is ['slug' => 'hello-world'].
  • ($route['handler'])(...$params) calls the closure and spreads the array as named arguments: the key slug goes to the parameter $slug. That's why the route placeholder and the handler's parameter must have the same name.
  • LIMIT :limit is bound with PDO::PARAM_INT. SQLite would accept a string here, but MySQL with emulated prepares would put quotes around it and fail, so binding integers as integers is a good habit.
  • $statement->fetch() ?: null turns PDO's false ("no row") into null, which matches the ?array return type.

Stage 3: pagination

With a hundred posts, the home page can't show them all. Pagination splits the list into pages of five: / is page 1, /?page=2 is page 2, and so on. The SQL is LIMIT 5 OFFSET 10 for page 3: skip the first ten posts and take the next five. In general, the offset is (page - 1) * perPage.

The page number comes from the query string, so it's untrusted. filter_var with min_range => 1 accepts only whole numbers from 1 upward. Anything else (0, -3, abc, 2.5) is a 404, and so is a page past the end, such as page 9 of a blog with five pages. Answering 404 instead of silently showing page 1 keeps search engines from indexing endless duplicate pages.

The total number of pages is max(1, (int) ceil($total / $perPage)). The max(1, ...) makes an empty blog have one (empty) page instead of zero, so / never returns a 404.

The request now carries a full URL like /?page=2. parse_url splits it into the path (/) and the query (page=2), and parse_str turns the query into an array, the same way PHP fills $_GET on a server.

PHP Stage 3: paginating the post list

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Twenty-three posts make five pages: four full pages and a last one with three posts (Weeks 3, 2 and 1). Page 1 has only an "Older posts" link, page 2 has both links, and page 5 has only "Newer posts". The link from page 2 back to page 1 points to /, not /?page=1, so the first page has one canonical URL. Pages 6, 0 and abc are all 404s.

The ORDER BY published_at DESC, id DESC has a second sort key for a reason. If two posts had the same timestamp, SQL wouldn't promise any order between them, and a post could appear on two pages or on none as you click through. Adding a unique column as a tie-breaker makes the order stable.

OFFSET pagination is simple, but the database still reads and skips all the earlier rows, so page 5,000 is slow. Large sites use keyset pagination instead ("posts older than the last one I saw"), which stays fast on any page. Pagination, Search and Database Patterns shows both.

Stage 4: comments

The final stage puts everything together and adds comments. The post page lists the comments under the post, oldest first, and shows a form. Submitting the form sends POST /posts/{slug}/comments with two fields, author and body:

  • The author must be 2 to 50 characters, the comment 3 to 1,000. Both are trimmed first and checked with mb_strlen.
  • If validation fails, the post page is shown again with status 422, the error messages and the typed values, so the reader doesn't lose a long comment.
  • If it succeeds, the comment is inserted with a prepared statement and the app answers 303 See Other with Location: /posts/{slug}#comments. The #comments fragment makes the browser scroll down to the comments after the redirect.

The comment count on the home page comes from one query with a LEFT JOIN and GROUP BY, not from a separate COUNT query per post. Running one query per post in a loop is the N+1 query problem, and it's one of the most common reasons PHP pages get slow. (LEFT JOIN keeps posts with no comments; an inner join would drop them.)

PHP Stage 4: the blog with comments

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The output tells the whole story:

  • Priya's comment is accepted: 303 Location: /posts/sqlite-is-enough#comments.
  • The second comment's author is an XSS payload, an image with an onerror script. It's a valid 28-character name as far as the length rule goes, so it's stored, and that's fine. Escaping on output is what makes it harmless: on the post page it appears as &lt;img src=x onerror=alert(1)&gt;.
  • The third comment fails both rules, so the post page comes back with status 422, two error messages and the typed values in the form.
  • Commenting on a post that doesn't exist is a 404, and GET on the comments URL is a 405 with Allow: POST.
  • The post page shows "2 comments", with Priya's line break turned into <br> after escaping. The home page shows (2 comments) and (0 comments), both from the single LEFT JOIN query.

Why store the dangerous name at all, instead of rejecting anything with <? Because the problem isn't the characters, it's printing them unescaped. People write <3, math like a < b, and code samples in comments. Escape on output, in every view, and the same data is safe in HTML, in an email or in a JSON API. Preventing XSS covers the other output contexts, such as URLs and JavaScript.

The handlers get the request input as their first argument and route parameters after it. use ($blog, &$clock) captures the clock by reference, so each new comment moves the shared clock forward by five minutes; a captured-by-value copy would reset on every call. In a real app, you'd use the actual current time, new DateTimeImmutable('now').

Running it on a real server

Put the router setup in a file under src/, and let public/index.php build the request from the superglobals:

PHP
<?php

declare(strict_types=1);

$pdo = new PDO('sqlite:' . __DIR__ . '/../data/blog.sqlite', options: [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$pdo->exec('PRAGMA foreign_keys = ON');

$router = require __DIR__ . '/../src/routes.php';   // returns the configured Router

$path = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH) ?: '/';
[$status, $headers, $body] = $router->dispatch($_SERVER['REQUEST_METHOD'], $path, $_POST + $_GET);

http_response_code($status);
header('Content-Type: text/html; charset=UTF-8');
foreach ($headers as $name => $value) {
    header("$name: $value");
}
echo $body;

For pretty URLs like /posts/hello-world, the web server must send every request for a file that doesn't exist to index.php. PHP's built-in server does that automatically when you start it with a router script, php -S localhost:8000 -t public public/index.php (static files are served directly if index.php returns false for them). With nginx, it's the classic try_files line:

NGINX
location / {
    try_files $uri /index.php?$query_string;
}

To use MySQL instead of SQLite, change only the DSN and the schema: INTEGER PRIMARY KEY becomes INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, TEXT columns for slugs and titles become VARCHAR(200) (MySQL can't put a UNIQUE index on an unlimited TEXT), and published_at becomes DATETIME. Every query in the repositories works unchanged.

Ideas for extending the blog

  • Markdown posts. Store the body as Markdown and convert it with a library such as league/commonmark, installed with Composer. Configure it to escape raw HTML.
  • Tags. Add a tags table and a post_tag join table, and a route /tags/{slug}.
  • Search. A WHERE title LIKE ? query with the user's term, escaping % and _ as shown in Prepared Statements.
  • An RSS feed at /feed.xml, generated with XMLWriter from the XML lesson.
  • Spam protection for comments: the CSRF token, honeypot and rate limit from the contact form project.
  • An admin area to write and edit posts, protected by the login system you'll build next.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Two posts are both titled Hello, World!. What slugs do they get?

    uniqueSlug tries the base slug first and then appends -2, -3 and so on until it finds one that isn't in the table. The UNIQUE constraint backs this up in the database.
  2. 2. Which SQL fetches page 3 when there are 5 posts per page?

    The offset is (page - 1) * perPage = 2 * 5 = 10. Page 3 skips the first ten posts and takes the next five.
  3. 3. The router has only GET /posts/{slug}. What should a POST to /posts/hello-world return?

    The path exists, just not for this method. 405 with Allow tells the client which methods are valid.
  4. 4. Why does the home page count comments with a LEFT JOIN and GROUP BY instead of a COUNT query for each post?

    Running a query inside a loop over posts means 1 + N queries. A single join with GROUP BY returns all counts at once; LEFT JOIN keeps posts with zero comments.
  5. 5. A comment's body is a < b c. In what order does postPage transform it?

    Escaping first turns < into &lt;; nl2br then adds real <br> tags. The other order would escape our own <br> tag too.

Summary

  • Generate slugs from titles (transliterate, strip, hyphenate, lowercase), make them unique with a suffix, and back that up with a UNIQUE constraint.
  • Store dates as YYYY-MM-DD HH:MM:SS text in SQLite so they sort and compare correctly; enable foreign keys with PRAGMA foreign_keys = ON.
  • A router turns route patterns into regular expressions with named groups, passes the captured values to the handler and distinguishes 404 from 405.
  • Paginate with LIMIT/OFFSET, validate the page number, return 404 for impossible pages, and add a unique tie-breaker to ORDER BY.
  • Store user comments as they are and escape them on output; add <br> only after escaping.
  • Avoid N+1 queries by fetching counts with a JOIN and GROUP BY.

Next: Project: Login System