Project 4: Blog with an admin panel (PHP, MySQL, login, CRUD)

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  slug VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  author_id INT NOT NULL,
  category_id INT NULL,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(200) NOT NULL UNIQUE,
  excerpt VARCHAR(300) NOT NULL DEFAULT '',
  body MEDIUMTEXT NOT NULL,
  cover VARCHAR(255) NULL,
  status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  published_at DATETIME NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (author_id) REFERENCES users(id),
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  INDEX (status, published_at)
);

blog/
├── config.php          (database password: in .gitignore)
├── config.example.php
├── db.php              (PDO connection + helper functions)
├── index.php           (public post list)
├── post.php            (single post)
├── uploads/            (cover images: in .gitignore)
└── admin/
    ├── login.php
    ├── logout.php
    ├── index.php       (list of all posts)
    ├── edit.php        (create + edit)
    └── delete.php

<?php
// db.php
$config = require __DIR__ . '/config.php';   // returns ['dsn' => ..., 'user' => ..., 'pass' => ...]
$pdo = new PDO($config['dsn'], $config['user'], $config['pass'], [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
session_start();

function e(?string $s): string { return htmlspecialchars($s ?? '', ENT_QUOTES, 'UTF-8'); }

function csrf_token(): string {
    if (empty($_SESSION['csrf'])) $_SESSION['csrf'] = bin2hex(random_bytes(32));
    return $_SESSION['csrf'];
}
function check_csrf(): void {
    if (!hash_equals($_SESSION['csrf'] ?? '', $_POST['csrf'] ?? '')) { http_response_code(400); exit('Invalid form. Please go back and try again.'); }
}
function require_login(): void {
    if (empty($_SESSION['user_id'])) { header('Location: login.php'); exit; }
}

<?php
function slugify(string $title): string {
    $s = strtolower(trim($title));
    $s = preg_replace('/[^a-z0-9]+/', '-', $s);   // anything that isn't a letter or digit becomes -
    return trim($s, '-') ?: 'post';
}

function uniqueSlug(string $title, array $taken): string {
    $base = slugify($title);
    $slug = $base;
    $n = 2;
    while (in_array($slug, $taken, true)) {
        $slug = $base . '-' . $n++;
    }
    return $slug;
}

echo slugify("How to Register a KRA PIN (2026)"), "\n";
echo slugify("  Unga prices: what's next?  "), "\n";
$taken = ['ajira-digital-guide', 'ajira-digital-guide-2'];
echo uniqueSlug("Ajira Digital guide", $taken), "\n";

<?php
// make-admin.php: run once from the command line: php make-admin.php, then delete this file
require 'db.php';
$stmt = $pdo->prepare('INSERT INTO users (name, email, password_hash) VALUES (?, ?, ?)');
$stmt->execute(['Admin', 'admin@example.com', password_hash('choose-a-long-password', PASSWORD_DEFAULT)]);
echo "Admin created\n";

<?php
// admin/login.php
require __DIR__ . '/../db.php';
$error = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    check_csrf();
    $stmt = $pdo->prepare('SELECT id, password_hash FROM users WHERE email = ?');
    $stmt->execute([trim($_POST['email'] ?? '')]);
    $user = $stmt->fetch();
    if ($user && password_verify($_POST['password'] ?? '', $user['password_hash'])) {
        session_regenerate_id(true);          // stops session fixation attacks
        $_SESSION['user_id'] = $user['id'];
        header('Location: index.php');
        exit;
    }
    $error = 'Wrong email or password.';      // same message for both: don't reveal which emails exist
}
?>
<form method="post">
  <input type="hidden" name="csrf" value="<?= e(csrf_token()) ?>">
  <label>Email <input type="email" name="email" required></label>
  <label>Password <input type="password" name="password" required></label>
  <button>Log in</button>
  <p><?= e($error) ?></p>
</form>

<?php
// admin/edit.php (core logic)
require __DIR__ . '/../db.php';
require_login();
$id = (int)($_GET['id'] ?? 0);
$post = ['title' => '', 'body' => '', 'excerpt' => '', 'status' => 'draft', 'category_id' => null];
if ($id) {
    $stmt = $pdo->prepare('SELECT * FROM posts WHERE id = ?');
    $stmt->execute([$id]);
    $post = $stmt->fetch() ?: exit('Post not found');
}
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    check_csrf();
    $title = trim($_POST['title'] ?? '');
    $body = trim($_POST['body'] ?? '');
    $status = ($_POST['status'] ?? '') === 'published' ? 'published' : 'draft';   // never trust the value: whitelist it
    if ($title === '' || $body === '') {
        $error = 'Title and body are required.';
    } elseif ($id) {
        $pdo->prepare('UPDATE posts SET title = ?, body = ?, excerpt = ?, status = ?,
                       published_at = IF(? = "published" AND published_at IS NULL, NOW(), published_at) WHERE id = ?')
            ->execute([$title, $body, mb_substr(trim($_POST['excerpt'] ?? ''), 0, 300), $status, $status, $id]);
    } else {
        $taken = $pdo->query('SELECT slug FROM posts')->fetchAll(PDO::FETCH_COLUMN);
        $pdo->prepare('INSERT INTO posts (author_id, title, slug, excerpt, body, status, published_at) VALUES (?, ?, ?, ?, ?, ?, ?)')
            ->execute([$_SESSION['user_id'], $title, uniqueSlug($title, $taken), mb_substr(trim($_POST['excerpt'] ?? ''), 0, 300),
                       $body, $status, $status === 'published' ? date('Y-m-d H:i:s') : null]);
        $id = (int)$pdo->lastInsertId();
    }
    if (empty($error)) { header('Location: index.php?saved=' . $id); exit; }
}

<?php
// index.php
require 'db.php';
$perPage = 10;
$page = max(1, (int)($_GET['page'] ?? 1));
$total = (int)$pdo->query("SELECT COUNT(*) FROM posts WHERE status = 'published'")->fetchColumn();
$stmt = $pdo->prepare("SELECT title, slug, excerpt, published_at FROM posts
                       WHERE status = 'published' ORDER BY published_at DESC LIMIT ? OFFSET ?");
$stmt->bindValue(1, $perPage, PDO::PARAM_INT);
$stmt->bindValue(2, ($page - 1) * $perPage, PDO::PARAM_INT);
$stmt->execute();
foreach ($stmt as $p) {
    echo '<article><h2><a href="post.php?slug=' . urlencode($p['slug']) . '">' . e($p['title']) . '</a></h2>';
    echo '<p>' . e($p['excerpt']) . '</p><small>' . e(date('j M Y', strtotime($p['published_at']))) . '</small></article>';
}
$pages = (int)ceil($total / $perPage);
if ($page < $pages) echo '<a href="?page=' . ($page + 1) . '">Older posts →</a>';