PHP Project — Build a Complete Blog System from Scratch

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  slug VARCHAR(120) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(280) NOT NULL UNIQUE,
  content LONGTEXT NOT NULL,
  excerpt VARCHAR(500) NOT NULL,
  category_id INT NOT NULL,
  user_id INT NOT NULL,
  status ENUM('draft', 'published') DEFAULT 'draft',
  published_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE tags (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  slug VARCHAR(60) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id INT NOT NULL,
  tag_id INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin', 'editor') DEFAULT 'editor',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  post_id INT NOT NULL,
  author_name VARCHAR(100) NOT NULL,
  author_email VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  status ENUM('pending', 'approved', 'spam') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'blog');
define('DB_USER', 'root');
define('DB_PASS', '');
define('POSTS_PER_PAGE', 10);
define('SITE_NAME', 'My Blog');

function db(): PDO
{
  static $pdo = null;
  if ($pdo === null) {
    $pdo = new PDO(
      "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4",
      DB_USER,
      DB_PASS,
      [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
      ]
    );
  }
  return $pdo;
}

function slugify(string $text): string
{
  $text = preg_replace('/[^\p{L}\p{N}\s-]/u', '', mb_strtolower($text));
  $text = preg_replace('/[\s-]+/', '-', trim($text));
  return trim($text, '-');
}

<?php
function login(string $username, string $password): bool
{
  $pdo = db();
  $stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username');
  $stmt->execute(['username' => $username]);
  $user = $stmt->fetch();

  if ($user && password_verify($password, $user['password_hash'])) {
    $_SESSION['user_id'] = $user['id'];
    $_SESSION['username'] = $user['username'];
    $_SESSION['role'] = $user['role'];
    session_regenerate_id(true);
    return true;
  }
  return false;
}

function isLoggedIn(): bool
{
  return isset($_SESSION['user_id']);
}

function requireLogin(): void
{
  if (!isLoggedIn()) {
    header('Location: /login.php');
    exit;
  }
}

function logout(): void
{
  session_destroy();
  header('Location: /login.php');
  exit;
}

<?php
require_once 'config.php';

$page = max(1, (int)($_GET['page'] ?? 1));
$category = $_GET['category'] ?? null;
$search = $_GET['search'] ?? null;
$offset = ($page - 1) * POSTS_PER_PAGE;

$pdo = db();
$where = ['p.status = :status'];
$params = ['status' => 'published'];

if ($category) {
  $where[] = 'c.slug = :category';
  $params['category'] = $category;
}

if ($search) {
  $where[] = '(p.title LIKE :search OR p.content LIKE :search2)';
  $params['search'] = "%$search%";
  $params['search2'] = "%$search%";
}

$whereClause = implode(' AND ', $where);

$countStmt = $pdo->prepare("SELECT COUNT(*) FROM posts p JOIN categories c ON p.category_id = c.id WHERE $whereClause");
$countStmt->execute($params);
$totalPosts = $countStmt->fetchColumn();
$totalPages = max(1, ceil($totalPosts / POSTS_PER_PAGE));

$stmt = $pdo->prepare("
  SELECT p.id, p.title, p.slug, p.excerpt, p.published_at,
         c.name as category_name, c.slug as category_slug,
         u.username
  FROM posts p
  JOIN categories c ON p.category_id = c.id
  JOIN users u ON p.user_id = u.id
  WHERE $whereClause
  ORDER BY p.published_at DESC
  LIMIT :limit OFFSET :offset
");
$stmt->bindValue('limit', POSTS_PER_PAGE, PDO::PARAM_INT);
$stmt->bindValue('offset', $offset, PDO::PARAM_INT);
foreach ($params as $key => $val) {
  $stmt->bindValue(":$key", $val);
}
$stmt->execute();
$posts = $stmt->fetchAll();
?>
<!DOCTYPE html>
<html>
<head><title><?= SITE_NAME ?></title></head>
<body>
  <h1><?= SITE_NAME ?></h1>

  <form method="GET">
    <input type="text" name="search" placeholder="Search posts..." value="<?= htmlspecialchars($search ?? '') ?>">
    <button type="submit">Search</button>
  </form>

  <?php foreach ($posts as $post): ?>
    <article>
      <h2><a href="/post.php?slug=<?= urlencode($post['slug']) ?>"><?= htmlspecialchars($post['title']) ?></a></h2>
      <p class="meta">By <?= htmlspecialchars($post['username']) ?> in
         <a href="/?category=<?= urlencode($post['category_slug']) ?>"><?= htmlspecialchars($post['category_name']) ?></a>
         on <?= $post['published_at'] ?></p>
      <p><?= htmlspecialchars($post['excerpt']) ?></p>
    </article>
  <?php endforeach; ?>

  <div class="pagination">
    <?php for ($i = 1; $i <= $totalPages; $i++): ?>
      <a href="/?page=<?= $i ?><?= $category ? '&category=' . urlencode($category) : '' ?>"><?= $i ?></a>
    <?php endfor; ?>
  </div>
</body>
</html>

<?php
require_once 'config.php';

$slug = $_GET['slug'] ?? '';
$pdo = db();

$stmt = $pdo->prepare("
  SELECT p.*, c.name as category_name, u.username
  FROM posts p
  JOIN categories c ON p.category_id = c.id
  JOIN users u ON p.user_id = u.id
  WHERE p.slug = :slug AND p.status = 'published'
");
$stmt->execute(['slug' => $slug]);
$post = $stmt->fetch();

if (!$post) {
  http_response_code(404);
  die('Post not found');
}

$commentStmt = $pdo->prepare(
  "SELECT * FROM comments WHERE post_id = :post_id AND status = 'approved' ORDER BY created_at DESC"
);
$commentStmt->execute(['post_id' => $post['id']]);
$comments = $commentStmt->fetchAll();

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['submit_comment'])) {
  $errors = [];
  $author = trim($_POST['author_name'] ?? '');
  $email = filter_var($_POST['author_email'] ?? '', FILTER_VALIDATE_EMAIL);
  $content = trim($_POST['content'] ?? '');

  if (strlen($author) < 2) $errors[] = 'Name is required';
  if ($email === false) $errors[] = 'Valid email is required';
  if (strlen($content) < 5) $errors[] = 'Comment must be at least 5 characters';

  if (empty($errors)) {
    $insert = $pdo->prepare(
      'INSERT INTO comments (post_id, author_name, author_email, content) VALUES (:post_id, :author, :email, :content)'
    );
    $insert->execute([
      'post_id' => $post['id'],
      'author' => htmlspecialchars($author, ENT_QUOTES, 'UTF-8'),
      'email' => $email,
      'content' => htmlspecialchars($content, ENT_QUOTES, 'UTF-8'),
    ]);
    $success = 'Your comment is pending moderation.';
  }
}
?>
<!DOCTYPE html>
<html>
<head><title><?= htmlspecialchars($post['title']) ?> - <?= SITE_NAME ?></title></head>
<body>
  <article>
    <h1><?= htmlspecialchars($post['title']) ?></h1>
    <p class="meta">By <?= htmlspecialchars($post['username']) ?> in
       <?= htmlspecialchars($post['category_name']) ?></p>
    <div class="content"><?= nl2br(htmlspecialchars($post['content'])) ?></div>
  </article>

  <section class="comments">
    <h2>Comments (<?= count($comments) ?>)</h2>
    <?php foreach ($comments as $comment): ?>
      <div class="comment">
        <strong><?= htmlspecialchars($comment['author_name']) ?></strong>
        <p><?= nl2br(htmlspecialchars($comment['content'])) ?></p>
        <small><?= $comment['created_at'] ?></small>
      </div>
    <?php endforeach; ?>
  </section>
</body>
</html>