Project Architecture & Database
Design a blog CMS architecture with MVC pattern — database schema, PDO connections, and a reusable project structure.
55 min•By Priygop Team•Last updated: Feb 2026
Project Structure & MVC
- MVC Pattern: Model (database queries), View (HTML templates), Controller (business logic). Separation of concerns — each file has one responsibility. Folder structure: models/, views/, controllers/, public/
- Entry Point: public/index.php — single entry point for all requests. URL routing: $uri = parse_url($_SERVER['REQUEST_URI'], PHP_URL_PATH). Route to correct controller based on URL path
- Database Schema: users (id, username, email, password_hash, role, created_at), posts (id, title, slug, content, excerpt, author_id FK, status, featured_image, created_at, updated_at), categories (id, name, slug)
- PDO Connection: $pdo = new PDO('mysql:host=localhost;dbname=blog;charset=utf8mb4', $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC])
- Environment Config: Store credentials in .env file (never commit to Git) — DB_HOST=localhost DB_NAME=blog DB_USER=root DB_PASS=secret. Parse with parse_ini_file() or vlucas/phpdotenv package
- Prepared Statements: $stmt = $pdo->prepare('SELECT * FROM posts WHERE slug = :slug'); $stmt->execute(['slug' => $slug]); $post = $stmt->fetch(); — prevents SQL injection by separating query from data
Database Operations
- Create Post: INSERT INTO posts (title, slug, content, excerpt, author_id, status) VALUES (:title, :slug, :content, :excerpt, :author_id, :status) — generate slug: strtolower(preg_replace('/[^a-z0-9]+/', '-', $title))
- Read Posts: SELECT p.*, u.username AS author FROM posts p JOIN users u ON p.author_id = u.id WHERE p.status = 'published' ORDER BY p.created_at DESC LIMIT :limit OFFSET :offset — pagination with LIMIT/OFFSET
- Update Post: UPDATE posts SET title = :title, content = :content, updated_at = NOW() WHERE id = :id AND author_id = :author_id — always check author_id to prevent unauthorized edits
- Delete Post: DELETE FROM posts WHERE id = :id — use soft delete in production (status = 'deleted' instead of actual DELETE). Keep data for recovery and audit trail
- Search: SELECT * FROM posts WHERE (title LIKE :query OR content LIKE :query) AND status = 'published' — use $query = '%' . $search . '%'. For production, use MySQL FULLTEXT index for better performance
- Pagination: $total = $pdo->query('SELECT COUNT(*) FROM posts WHERE status = "published"')->fetchColumn(); $pages = ceil($total / $perPage); $offset = ($page - 1) * $perPage — calculate total pages and offset