Project 7: Blog CRUD

CREATE TABLE posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    body TEXT NOT NULL,
    status ENUM('draft', 'published') NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

<?php

declare(strict_types=1);

final class PostRepository
{
    public function __construct(private PDO $pdo) {}

    public static function slugify(string $title): string
    {
        $slug = strtolower(trim($title));
        $slug = preg_replace('/[^a-z0-9]+/', "-", $slug) ?? "";
        $slug = trim($slug, "-");

        return $slug === "" ? "post" : substr($slug, 0, 180);
    }

    private function uniqueSlug(string $title, ?int $ignoreId = null): string
    {
        $base = self::slugify($title);
        $slug = $base;

        for ($i = 2; $this->slugTaken($slug, $ignoreId); $i++) {
            $slug = $base . "-" . $i;
        }

        return $slug;
    }

    private function slugTaken(string $slug, ?int $ignoreId): bool
    {
        $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM posts WHERE slug = :slug AND id <> :id");
        $stmt->execute(["slug" => $slug, "id" => $ignoreId ?? 0]);

        return (int) $stmt->fetchColumn() > 0;
    }

    public function create(int $userId, string $title, string $body, string $status = "draft"): int
    {
        $this->pdo->prepare(
            "INSERT INTO posts (user_id, title, slug, body, status, created_at) VALUES (:user, :title, :slug, :body, :status, :created)"
        )->execute([
            "user" => $userId,
            "title" => $title,
            "slug" => $this->uniqueSlug($title),
            "body" => $body,
            "status" => $status === "published" ? "published" : "draft",
            "created" => date("Y-m-d H:i:s"),
        ]);

        return (int) $this->pdo->lastInsertId();
    }

    public function update(int $id, int $userId, string $title, string $body, string $status): bool
    {
        $stmt = $this->pdo->prepare(
            "UPDATE posts SET title = :title, slug = :slug, body = :body, status = :status WHERE id = :id AND user_id = :user"
        );
        $stmt->execute([
            "title" => $title,
            "slug" => $this->uniqueSlug($title, $id),
            "body" => $body,
            "status" => $status === "published" ? "published" : "draft",
            "id" => $id,
            "user" => $userId,
        ]);

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

    public function delete(int $id, int $userId): bool
    {
        $stmt = $this->pdo->prepare("DELETE FROM posts WHERE id = :id AND user_id = :user");
        $stmt->execute(["id" => $id, "user" => $userId]);

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

    public function findPublishedBySlug(string $slug): ?array
    {
        $stmt = $this->pdo->prepare("SELECT id, title, slug, body, created_at FROM posts WHERE slug = :slug AND status = 'published'");
        $stmt->execute(["slug" => $slug]);

        return $stmt->fetch() ?: null;
    }

    public function findForAuthor(int $id, int $userId): ?array
    {
        $stmt = $this->pdo->prepare("SELECT id, title, slug, body, status FROM posts WHERE id = :id AND user_id = :user");
        $stmt->execute(["id" => $id, "user" => $userId]);

        return $stmt->fetch() ?: null;
    }

    public function published(int $limit, int $offset = 0): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id, title, slug, body, created_at FROM posts WHERE status = 'published' ORDER BY created_at DESC, id DESC LIMIT :limit OFFSET :offset"
        );
        $stmt->bindValue(":limit", $limit, PDO::PARAM_INT);
        $stmt->bindValue(":offset", $offset, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll();
    }

    public function forAuthor(int $userId): array
    {
        $stmt = $this->pdo->prepare("SELECT id, title, status, created_at FROM posts WHERE user_id = :user ORDER BY id DESC");
        $stmt->execute(["user" => $userId]);

        return $stmt->fetchAll();
    }
}

function excerpt(string $text, int $length = 80): string
{
    $text = trim(preg_replace('/\s+/', " ", strip_tags($text)) ?? "");

    if (mb_strlen($text) <= $length) {
        return $text;
    }

    $cut = mb_substr($text, 0, $length);
    $space = mb_strrpos($cut, " ");

    return rtrim($space === false ? $cut : mb_substr($cut, 0, $space), " ,.;:") . "...";
}

<?php
require __DIR__ . "/PostRepository.php";

$posts = new PostRepository(require __DIR__ . "/db.php");

$a = $posts->create(1, "Hello World!", "My first post about PHP.", "published");
$b = $posts->create(1, "Hello World!", "A second post with the same title.", "published");
$c = $posts->create(1, "Work in progress", "This is not ready.", "draft");
$d = $posts->create(2, "Ben's post", "Written by another user.", "published");

echo $posts->findForAuthor($a, 1)["slug"] . ", " . $posts->findForAuthor($b, 1)["slug"] . "\n";

echo "public: " . implode(" | ", array_column($posts->published(10), "slug")) . "\n";

var_dump($posts->findPublishedBySlug("work-in-progress"));                  // a draft is not public
var_dump($posts->update($a, 2, "Hacked", "x", "published"));                 // Ben cannot edit Ana's post
var_dump($posts->update($c, 1, "Work in progress", "Now ready!", "published"));

echo "public now: " . count($posts->published(10)) . " posts\n";

var_dump($posts->delete($d, 1));                                             // wrong owner
var_dump($posts->delete($d, 2));

echo excerpt("<p>PHP is a popular language, used to build websites and applications of every size.</p>", 40) . "\n";
echo PostRepository::slugify("  Crème brûlée & PHP 8!  ");

hello-world, hello-world-2
public: ben-s-post | hello-world-2 | hello-world
NULL
bool(false)
bool(true)
public now: 4 posts
bool(false)
bool(true)
PHP is a popular language, used to...
cr-me-br-l-e-php-8

<?php
declare(strict_types=1);

require __DIR__ . "/functions.php";
require __DIR__ . "/PostRepository.php";

$posts = new PostRepository(require __DIR__ . "/db.php");

$page = max(1, (int) ($_GET["page"] ?? 1));
$perPage = 5;
$list = $posts->published($perPage, ($page - 1) * $perPage);
?>
<h1>The Blog</h1>
<?php foreach ($list as $p): ?>
  <article>
    <h2><a href="post.php?slug=<?= urlencode($p["slug"]) ?>"><?= h($p["title"]) ?></a></h2>
    <p><?= h(excerpt($p["body"], 160)) ?></p>
  </article>
<?php endforeach; ?>

<?php
declare(strict_types=1);

require __DIR__ . "/functions.php";
require __DIR__ . "/PostRepository.php";

$posts = new PostRepository(require __DIR__ . "/db.php");
$post = $posts->findPublishedBySlug((string) ($_GET["slug"] ?? ""));

if ($post === null) {
    http_response_code(404);
    exit("Post not found.");
}
?>
<article>
  <h1><?= h($post["title"]) ?></h1>
  <p><time datetime="<?= h(date("c", strtotime($post["created_at"]))) ?>"><?= h(date("j F Y", strtotime($post["created_at"]))) ?></time></p>
  <?= nl2br(h($post["body"])) ?>
</article>