PHP Database Interaction, CRUD Operations & ORM — The Complete Production Guide

The Three Layers Every PHP Developer Must Master

Database interaction is where most PHP applications spend the majority of their execution time. Get it right and your application is secure, fast, and maintainable. Get it wrong and you have SQL injection vulnerabilities, memory leaks from unclosed connections, N+1 query nightmares, and brittle code that breaks every time your schema changes.

The original article shows four mysqli_query() calls and five Eloquent lines. That’s like teaching someone to drive by showing them the steering wheel and the gas pedal — technically accurate, but missing everything that matters.

This guide covers the complete database interaction stack that production PHP applications actually use:

Layer 1 — Raw PHP with PDO: The foundation. Understanding PDO, prepared statements, transactions, error handling, and connection management properly. This is what every other layer is built on.

Layer 2 — The Repository Pattern: The architecture. How to abstract database access so your business logic doesn’t depend on which database or query method you use.

Layer 3 — ORM (Eloquent and Doctrine): The shortcut. How ORMs actually work, when they’re the right choice, when they hurt performance, and how to use them safely.

By the end of this guide, you’ll understand not just how to perform CRUD operations, but how to choose the right approach for each scenario, implement it correctly, and test it.

Part 1 — Setting Up: The Right Database Schema

Every CRUD tutorial uses a users table. Real applications have relationships. Let’s work with a realistic schema throughout:

-- Create a blogging platform database
CREATE DATABASE IF NOT EXISTS blog_platform CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE blog_platform;

-- Users table
CREATE TABLE users (
    id           INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    name         VARCHAR(100)    NOT NULL,
    email        VARCHAR(255)    NOT NULL,
    password     VARCHAR(255)    NOT NULL,          -- bcrypt hash, never plain text
    role         ENUM('admin','author','reader') NOT NULL DEFAULT 'reader',
    bio          TEXT,
    avatar_url   VARCHAR(500),
    is_active    TINYINT(1)      NOT NULL DEFAULT 1,
    deleted_at   DATETIME        NULL DEFAULT NULL, -- Soft delete support
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY  (id),
    UNIQUE KEY   uk_email (email),
    KEY          idx_role_active (role, is_active),
    KEY          idx_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Posts table
CREATE TABLE posts (
    id           INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    user_id      INT UNSIGNED    NOT NULL,
    title        VARCHAR(500)    NOT NULL,
    slug         VARCHAR(500)    NOT NULL,
    excerpt      TEXT,
    content      LONGTEXT        NOT NULL,
    status       ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
    published_at DATETIME        NULL DEFAULT NULL,
    view_count   INT UNSIGNED    NOT NULL DEFAULT 0,
    deleted_at   DATETIME        NULL DEFAULT NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY  (id),
    UNIQUE KEY   uk_slug (slug),
    KEY          idx_user_id (user_id),
    KEY          idx_status_published (status, published_at),
    KEY          idx_deleted_at (deleted_at),
    FULLTEXT KEY ft_content (title, content),

    CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Categories table
CREATE TABLE categories (
    id           INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    name         VARCHAR(100)    NOT NULL,
    slug         VARCHAR(100)    NOT NULL,
    description  TEXT,
    parent_id    INT UNSIGNED    NULL DEFAULT NULL,  -- Self-referencing for nested categories

    PRIMARY KEY  (id),
    UNIQUE KEY   uk_slug (slug),
    KEY          idx_parent_id (parent_id),

    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Post categories pivot (many-to-many)
CREATE TABLE post_categories (
    post_id      INT UNSIGNED    NOT NULL,
    category_id  INT UNSIGNED    NOT NULL,

    PRIMARY KEY  (post_id, category_id),
    KEY          idx_category_id (category_id),

    CONSTRAINT fk_pc_post     FOREIGN KEY (post_id)     REFERENCES posts(id)      ON DELETE CASCADE,
    CONSTRAINT fk_pc_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Comments table
CREATE TABLE comments (
    id           INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    post_id      INT UNSIGNED    NOT NULL,
    user_id      INT UNSIGNED    NULL DEFAULT NULL,  -- NULL for guest comments
    author_name  VARCHAR(100)    NULL DEFAULT NULL,  -- For guest commenters
    content      TEXT            NOT NULL,
    is_approved  TINYINT(1)      NOT NULL DEFAULT 0,
    parent_id    INT UNSIGNED    NULL DEFAULT NULL,  -- For threaded comments
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY  (id),
    KEY          idx_post_id (post_id),
    KEY          idx_user_id (user_id),

    CONSTRAINT fk_comments_post   FOREIGN KEY (post_id)   REFERENCES posts(id)    ON DELETE CASCADE,
    CONSTRAINT fk_comments_user   FOREIGN KEY (user_id)   REFERENCES users(id)    ON DELETE SET NULL,
    CONSTRAINT fk_comments_parent FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Part 2 — PDO: The Modern PHP Database Standard

mysqli_query() (used in the original article) is valid but has limitations: it only works with MySQL, its API is inconsistent, and mixing procedural and object-oriented styles creates confusion. PDO (PHP Data Objects) is the modern standard — database-agnostic, consistent API, and better error handling.

Connection Management: The Right Pattern

<?php
declare(strict_types=1);

/**
 * Database Connection Manager
 * Implements the Singleton pattern for connection reuse.
 * In real applications, use dependency injection (see Part 5).
 */
class Database {

    private static ?PDO $instance = null;

    private function __construct() {} // Prevent direct instantiation
    private function __clone() {}     // Prevent cloning

    public static function connect(array $config = []): PDO {
        if (self::$instance !== null) {
            return self::$instance;
        }

        // Load from environment or config:
        $host    = $config['host']     ?? $_ENV['DB_HOST']     ?? '127.0.0.1';
        $port    = $config['port']     ?? $_ENV['DB_PORT']     ?? '3306';
        $dbname  = $config['dbname']   ?? $_ENV['DB_NAME']     ?? 'blog_platform';
        $user    = $config['user']     ?? $_ENV['DB_USER']     ?? 'root';
        $pass    = $config['password'] ?? $_ENV['DB_PASS']     ?? '';
        $charset = $config['charset']  ?? 'utf8mb4';

        $dsn = "mysql:host={$host};port={$port};dbname={$dbname};charset={$charset}";

        $options = [
            // ── Error handling ──────────────────────────────────────────────
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,    // Throw exceptions

            // ── Fetch mode ─────────────────────────────────────────────────
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,          // Arrays by default

            // ── Prepared statements ────────────────────────────────────────
            PDO::ATTR_EMULATE_PREPARES   => false,                     // Use native prepares

            // ── Connection handling ────────────────────────────────────────
            PDO::ATTR_PERSISTENT         => false,                     // Don't persist by default

            // ── MySQL-specific ─────────────────────────────────────────────
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$charset} COLLATE utf8mb4_unicode_ci",
            PDO::MYSQL_ATTR_FOUND_ROWS   => true,   // Return matched rows for UPDATE
        ];

        try {
            self::$instance = new PDO($dsn, $user, $pass, $options);
        } catch (PDOException $e) {
            // Log the full error but throw a sanitized message to the user
            error_log("Database connection failed: " . $e->getMessage());
            throw new \RuntimeException("Database connection failed. Please try again later.");
        }

        return self::$instance;
    }

    public static function disconnect(): void {
        self::$instance = null;
    }
}

// ── Usage ──────────────────────────────────────────────────────────────────
$pdo = Database::connect();

Understanding PDO Error Handling

<?php
// ── PDO error modes explained ─────────────────────────────────────────────

// PDO::ERRMODE_SILENT (default — never use this):
// Errors are stored in PDO::errorInfo() but NOT thrown
// You must manually check after every query — easy to miss errors
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT); // ❌ Avoid

// PDO::ERRMODE_WARNING (deprecated use case):
// PHP warnings are triggered — visible in development, logged in production
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); // ⚠️ Avoid

// PDO::ERRMODE_EXCEPTION (always use this):
// Throws PDOException on any database error — can be caught and handled properly
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // ✅ Always

// ── Proper error handling pattern ─────────────────────────────────────────
try {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email");
    $stmt->execute([':email' => $email]);
    $user = $stmt->fetch();

} catch (\PDOException $e) {
    // Different errors need different handling:
    $code = (int) $e->getCode();

    switch ($code) {
        case 1062: // Duplicate entry
            throw new \InvalidArgumentException("This email address is already registered.");

        case 1452: // Foreign key constraint violation
            throw new \InvalidArgumentException("Referenced record does not exist.");

        case 2002: // Connection refused
        case 2006: // MySQL server has gone away
            error_log("Database connection lost: " . $e->getMessage());
            throw new \RuntimeException("Database temporarily unavailable.");

        default:
            // Log full error but never expose to user:
            error_log("Database error [{$code}]: " . $e->getMessage());
            throw new \RuntimeException("A database error occurred.");
    }
}

Part 3 — Full CRUD with PDO (Production Patterns)

CREATE — Inserting Data Safely

<?php
class UserRepository {

    public function __construct(private readonly PDO $pdo) {}

    // ── Single insert ────────────────────────────────────────────────────────

    /**
     * Create a new user.
     *
     * @throws \InvalidArgumentException If email already exists
     * @throws \RuntimeException         On database error
     * @return int  The new user's ID
     */
    public function create(array $data): int {

        // Validate required fields before hitting the database:
        if (empty($data['email']) || !filter_var($data['email'], FILTER_VALIDATE_EMAIL)) {
            throw new \InvalidArgumentException("Valid email address is required.");
        }

        if (empty($data['name'])) {
            throw new \InvalidArgumentException("Name is required.");
        }

        if (empty($data['password'])) {
            throw new \InvalidArgumentException("Password is required.");
        }

        $sql = "INSERT INTO users (name, email, password, role, bio)
                VALUES (:name, :email, :password, :role, :bio)";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([
                ':name'     => trim($data['name']),
                ':email'    => strtolower(trim($data['email'])),
                ':password' => password_hash($data['password'], PASSWORD_ARGON2ID),
                ':role'     => $data['role'] ?? 'reader',
                ':bio'      => $data['bio'] ?? null,
            ]);

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

        } catch (\PDOException $e) {
            if ((int)$e->getCode() === 1062) {
                throw new \InvalidArgumentException("Email address '{$data['email']}' is already registered.");
            }
            throw $e;
        }
    }

    // ── Batch insert (much faster than looping create()) ─────────────────────

    /**
     * Insert multiple users efficiently in a single query.
     *
     * @param  array $users Array of user data arrays
     * @return int   Number of rows inserted
     */
    public function createMany(array $users): int {

        if (empty($users)) return 0;

        // Build parameterized placeholders:
        // For 3 users with 3 fields each: (?,?,?),(?,?,?),(?,?,?)
        $placeholders = [];
        $values       = [];

        foreach ($users as $user) {
            $placeholders[] = '(?, ?, ?)';
            $values[]       = trim($user['name']);
            $values[]       = strtolower(trim($user['email']));
            $values[]       = password_hash($user['password'], PASSWORD_BCRYPT);
        }

        $sql  = "INSERT INTO users (name, email, password) VALUES " . implode(', ', $placeholders);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($values);

        return $stmt->rowCount();
    }

    // ── INSERT ... ON DUPLICATE KEY UPDATE (upsert) ───────────────────────────

    /**
     * Insert or update user settings (upsert pattern).
     */
    public function upsertProfile(int $userId, array $profile): void {

        $sql = "INSERT INTO user_profiles (user_id, avatar_url, bio, website)
                VALUES (:user_id, :avatar, :bio, :website)
                ON DUPLICATE KEY UPDATE
                    avatar_url = VALUES(avatar_url),
                    bio        = VALUES(bio),
                    website    = VALUES(website)";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([
            ':user_id' => $userId,
            ':avatar'  => $profile['avatar_url'] ?? null,
            ':bio'     => $profile['bio'] ?? null,
            ':website' => $profile['website'] ?? null,
        ]);
    }
}

READ — Fetching Data (Every Pattern)

<?php
class PostRepository {

    public function __construct(private readonly PDO $pdo) {}

    // ── Fetch single row by primary key ──────────────────────────────────────
    public function findById(int $id): ?array {
        $stmt = $this->pdo->prepare(
            "SELECT p.*, u.name AS author_name, u.avatar_url AS author_avatar
             FROM posts p
             JOIN users u ON u.id = p.user_id
             WHERE p.id = :id
               AND p.deleted_at IS NULL"
        );
        $stmt->execute([':id' => $id]);
        $result = $stmt->fetch();
        return $result !== false ? $result : null;
    }

    // ── Fetch single row by unique field ─────────────────────────────────────
    public function findBySlug(string $slug): ?array {
        $stmt = $this->pdo->prepare(
            "SELECT * FROM posts WHERE slug = :slug AND deleted_at IS NULL"
        );
        $stmt->execute([':slug' => $slug]);
        $result = $stmt->fetch();
        return $result ?: null;
    }

    // ── Fetch multiple rows with filtering ───────────────────────────────────

    /**
     * Get published posts with optional filtering and pagination.
     *
     * @param  array $filters  Associative: ['status' => 'published', 'user_id' => 1, ...]
     * @param  int   $page     Current page (1-based)
     * @param  int   $perPage  Results per page
     * @param  string $orderBy Column to sort by
     * @param  string $order   ASC or DESC
     * @return array           ['data' => [...], 'total' => int, 'pages' => int]
     */
    public function findAll(
        array  $filters = [],
        int    $page    = 1,
        int    $perPage = 15,
        string $orderBy = 'published_at',
        string $order   = 'DESC'
    ): array {

        // Whitelist sortable columns (security: prevent SQL injection via ORDER BY)
        $allowedSort = ['id', 'title', 'published_at', 'view_count', 'created_at'];
        if (!in_array($orderBy, $allowedSort, true)) {
            $orderBy = 'published_at';
        }
        $order = strtoupper($order) === 'ASC' ? 'ASC' : 'DESC';

        // Dynamic WHERE clause builder:
        $where  = ['p.deleted_at IS NULL'];
        $params = [];

        if (!empty($filters['status'])) {
            $where[]           = 'p.status = :status';
            $params[':status'] = $filters['status'];
        }

        if (!empty($filters['user_id'])) {
            $where[]            = 'p.user_id = :user_id';
            $params[':user_id'] = (int) $filters['user_id'];
        }

        if (!empty($filters['category_id'])) {
            $where[]                = 'EXISTS (SELECT 1 FROM post_categories pc WHERE pc.post_id = p.id AND pc.category_id = :cat_id)';
            $params[':cat_id']      = (int) $filters['category_id'];
        }

        if (!empty($filters['search'])) {
            // Full-text search (requires FULLTEXT index):
            $where[]            = 'MATCH(p.title, p.content) AGAINST (:search IN BOOLEAN MODE)';
            $params[':search']  = $filters['search'] . '*';
            // Fallback to LIKE if no full-text index:
            // $where[]         = '(p.title LIKE :search OR p.content LIKE :search)';
            // $params[':search'] = '%' . $filters['search'] . '%';
        }

        $whereClause = implode(' AND ', $where);
        $offset      = ($page - 1) * $perPage;

        // Count query (for pagination):
        $countSql = "SELECT COUNT(*) FROM posts p WHERE {$whereClause}";
        $countStmt = $this->pdo->prepare($countSql);
        $countStmt->execute($params);
        $total = (int) $countStmt->fetchColumn();

        // Data query:
        $sql = "SELECT
                    p.id, p.title, p.slug, p.excerpt, p.status,
                    p.published_at, p.view_count,
                    u.id AS author_id, u.name AS author_name
                FROM posts p
                JOIN users u ON u.id = p.user_id
                WHERE {$whereClause}
                ORDER BY p.{$orderBy} {$order}
                LIMIT :limit OFFSET :offset";

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $stmt->bindValue(':limit',  $perPage, PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset,  PDO::PARAM_INT);
        $stmt->execute($params + [':limit' => $perPage, ':offset' => $offset]);

        return [
            'data'  => $stmt->fetchAll(),
            'total' => $total,
            'page'  => $page,
            'pages' => (int) ceil($total / $perPage),
        ];
    }

    // ── Fetch single column value ─────────────────────────────────────────────
    public function countByUser(int $userId): int {
        $stmt = $this->pdo->prepare(
            "SELECT COUNT(*) FROM posts WHERE user_id = :id AND deleted_at IS NULL"
        );
        $stmt->execute([':id' => $userId]);
        return (int) $stmt->fetchColumn();
    }

    // ── Fetch as column array ─────────────────────────────────────────────────
    public function getSlugs(): array {
        return $this->pdo->query("SELECT slug FROM posts WHERE deleted_at IS NULL")
                         ->fetchAll(PDO::FETCH_COLUMN);
    }

    // ── Fetch as key-value pairs ──────────────────────────────────────────────
    public function getIdTitleMap(): array {
        return $this->pdo->query("SELECT id, title FROM posts WHERE status = 'published'")
                         ->fetchAll(PDO::FETCH_KEY_PAIR); // [id => title, id => title, ...]
    }

    // ── Aggregate queries ─────────────────────────────────────────────────────
    public function getStatsByStatus(): array {
        return $this->pdo->query(
            "SELECT
                status,
                COUNT(*) AS count,
                SUM(view_count) AS total_views,
                AVG(view_count) AS avg_views,
                MAX(published_at) AS latest_published
             FROM posts
             WHERE deleted_at IS NULL
             GROUP BY status"
        )->fetchAll();
    }
}

UPDATE — Modifying Data Safely

<?php
// In PostRepository:

// ── Simple update ────────────────────────────────────────────────────────────
public function update(int $id, array $data): bool {

    // Build SET clause dynamically (only update provided fields):
    $allowedFields = ['title', 'slug', 'excerpt', 'content', 'status'];
    $setClauses    = [];
    $params        = [':id' => $id];

    foreach ($data as $field => $value) {
        if (!in_array($field, $allowedFields, true)) continue;
        $setClauses[]       = "`{$field}` = :{$field}";
        $params[":{$field}"] = $value;
    }

    if (empty($setClauses)) return false;

    // Automatically update timestamp:
    $setClauses[] = 'updated_at = CURRENT_TIMESTAMP';

    // Set published_at when status changes to published:
    if (($data['status'] ?? '') === 'published') {
        $setClauses[] = 'published_at = COALESCE(published_at, CURRENT_TIMESTAMP)';
    }

    $sql = "UPDATE posts SET " . implode(', ', $setClauses) .
           " WHERE id = :id AND deleted_at IS NULL";

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);

    return $stmt->rowCount() > 0; // true if a row was actually changed
}

// ── Increment a counter (atomic operation) ────────────────────────────────
public function incrementViewCount(int $postId): void {
    $this->pdo->prepare(
        "UPDATE posts SET view_count = view_count + 1 WHERE id = :id"
    )->execute([':id' => $postId]);
    // Atomic: safe even with concurrent requests hitting the same post
}

// ── Conditional update (optimistic locking) ───────────────────────────────
public function updateIfNotModified(int $id, array $data, string $lastModified): bool {
    // Only update if the record hasn't been modified since the user loaded it:
    $setClauses = [];
    $params     = [':id' => $id, ':last_modified' => $lastModified];

    foreach ($data as $field => $value) {
        $setClauses[]        = "`{$field}` = :{$field}";
        $params[":{$field}"] = $value;
    }

    $sql = "UPDATE posts SET " . implode(', ', $setClauses) .
           " WHERE id = :id AND updated_at = :last_modified";

    $stmt = $this->pdo->prepare($sql);
    $stmt->execute($params);

    if ($stmt->rowCount() === 0) {
        // Either row doesn't exist OR it was modified by someone else:
        $exists = $this->findById($id);
        if ($exists) {
            throw new \RuntimeException("Record was modified by another user. Please reload and try again.");
        }
    }

    return $stmt->rowCount() > 0;
}

DELETE — Hard Delete vs Soft Delete

<?php
// ── Hard delete (permanent) ──────────────────────────────────────────────────
public function delete(int $id): bool {
    $stmt = $this->pdo->prepare("DELETE FROM posts WHERE id = :id");
    $stmt->execute([':id' => $id]);
    return $stmt->rowCount() > 0;
}

// ── Soft delete (mark as deleted, keep data) ─────────────────────────────────
public function softDelete(int $id): bool {
    $stmt = $this->pdo->prepare(
        "UPDATE posts SET deleted_at = CURRENT_TIMESTAMP WHERE id = :id AND deleted_at IS NULL"
    );
    $stmt->execute([':id' => $id]);
    return $stmt->rowCount() > 0;
}

// ── Restore soft-deleted record ───────────────────────────────────────────────
public function restore(int $id): bool {
    $stmt = $this->pdo->prepare(
        "UPDATE posts SET deleted_at = NULL WHERE id = :id AND deleted_at IS NOT NULL"
    );
    $stmt->execute([':id' => $id]);
    return $stmt->rowCount() > 0;
}

// ── Permanently purge soft-deleted records older than N days ────────────────
public function purgeDeleted(int $daysOld = 30): int {
    $stmt = $this->pdo->prepare(
        "DELETE FROM posts WHERE deleted_at < DATE_SUB(NOW(), INTERVAL :days DAY)"
    );
    $stmt->execute([':days' => $daysOld]);
    return $stmt->rowCount();
}

Part 4 — Transactions: The Most Critical Feature Developers Skip

The original article mentions transactions in a bullet point. Here’s why they matter and exactly how to use them:

<?php
/**
 * Transactions ensure that a group of operations either ALL succeed
 * or ALL fail — preventing partial data corruption.
 *
 * Example: Creating a post with categories and sending a notification.
 * If the notification fails, we don't want a post created without categories.
 */
class PostService {

    public function __construct(
        private readonly PDO            $pdo,
        private readonly PostRepository $posts,
        private readonly NotificationService $notifications
    ) {}

    /**
     * Create a post with its categories in a single atomic transaction.
     */
    public function createPostWithCategories(array $postData, array $categoryIds): array {

        $this->pdo->beginTransaction();

        try {
            // Step 1: Insert the post
            $postSql = $this->pdo->prepare(
                "INSERT INTO posts (user_id, title, slug, content, excerpt, status)
                 VALUES (:user_id, :title, :slug, :content, :excerpt, :status)"
            );
            $postSql->execute([
                ':user_id' => $postData['user_id'],
                ':title'   => $postData['title'],
                ':slug'    => $postData['slug'],
                ':content' => $postData['content'],
                ':excerpt' => $postData['excerpt'] ?? null,
                ':status'  => $postData['status'] ?? 'draft',
            ]);
            $postId = (int) $this->pdo->lastInsertId();

            // Step 2: Attach categories (pivot table)
            if (!empty($categoryIds)) {
                $placeholders = implode(', ', array_fill(0, count($categoryIds), '(?, ?)'));
                $values = [];
                foreach ($categoryIds as $catId) {
                    $values[] = $postId;
                    $values[] = (int) $catId;
                }
                $catSql = $this->pdo->prepare(
                    "INSERT INTO post_categories (post_id, category_id) VALUES {$placeholders}"
                );
                $catSql->execute($values);
            }

            // Step 3: Send notification (if this fails, both inserts are rolled back)
            if ($postData['status'] === 'published') {
                $this->notifications->notifySubscribers($postId); // May throw
            }

            // All steps succeeded — commit everything:
            $this->pdo->commit();

            return ['id' => $postId, ...$postData];

        } catch (\Throwable $e) {
            // Something failed — roll back ALL changes:
            $this->pdo->rollBack();

            error_log("Post creation failed: " . $e->getMessage());
            throw new \RuntimeException("Failed to create post: " . $e->getMessage(), 0, $e);
        }
    }

    /**
     * Transfer post ownership with full audit trail.
     * Demonstrates nested transactions (savepoints).
     */
    public function transferOwnership(int $postId, int $fromUserId, int $toUserId): void {

        $this->pdo->beginTransaction();

        try {
            // Verify source ownership:
            $post = $this->posts->findById($postId);
            if (!$post || $post['user_id'] !== $fromUserId) {
                throw new \InvalidArgumentException("Post not found or not owned by specified user.");
            }

            // Create SAVEPOINT (nested transaction point):
            $this->pdo->exec("SAVEPOINT sp_transfer");

            try {
                // Update post ownership:
                $this->pdo->prepare("UPDATE posts SET user_id = :new_owner WHERE id = :id")
                           ->execute([':new_owner' => $toUserId, ':id' => $postId]);

                // Create audit log entry:
                $this->pdo->prepare(
                    "INSERT INTO audit_log (action, entity_type, entity_id, from_value, to_value, created_at)
                     VALUES ('ownership_transfer', 'post', :post_id, :from_user, :to_user, NOW())"
                )->execute([
                    ':post_id'   => $postId,
                    ':from_user' => $fromUserId,
                    ':to_user'   => $toUserId,
                ]);

                $this->pdo->exec("RELEASE SAVEPOINT sp_transfer");

            } catch (\PDOException $e) {
                // Roll back to savepoint (not entire transaction):
                $this->pdo->exec("ROLLBACK TO SAVEPOINT sp_transfer");
                throw $e;
            }

            $this->pdo->commit();

        } catch (\Throwable $e) {
            $this->pdo->rollBack();
            throw $e;
        }
    }
}

// ── Transaction helper function ───────────────────────────────────────────────
function withTransaction(PDO $pdo, callable $callback): mixed {
    $pdo->beginTransaction();
    try {
        $result = $callback($pdo);
        $pdo->commit();
        return $result;
    } catch (\Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// Usage:
$postId = withTransaction($pdo, function(PDO $pdo) use ($data, $categoryIds) {
    // All database operations here are wrapped in a transaction
    $stmt = $pdo->prepare("INSERT INTO posts ...");
    $stmt->execute($data);
    $postId = $pdo->lastInsertId();
    // ... more operations ...
    return $postId;
});

Part 5 — The Repository Pattern (Proper Architecture)

Rather than scattering database calls throughout your application, the Repository pattern centralises data access behind a clean interface:

<?php
/**
 * The Repository Pattern separates data access logic from business logic.
 *
 * Benefits:
 * - Swap MySQL for PostgreSQL without changing business logic
 * - Test business logic with in-memory fake repositories
 * - Single place to add caching, logging, or query optimisation
 */

// ── Step 1: Define an interface (the contract) ───────────────────────────────

interface UserRepositoryInterface {
    public function findById(int $id): ?array;
    public function findByEmail(string $email): ?array;
    public function findAll(array $filters, int $page, int $perPage): array;
    public function create(array $data): int;
    public function update(int $id, array $data): bool;
    public function delete(int $id): bool;
}

// ── Step 2: MySQL implementation ──────────────────────────────────────────────

class MySQLUserRepository implements UserRepositoryInterface {

    public function __construct(private readonly PDO $pdo) {}

    public function findById(int $id): ?array {
        $stmt = $this->pdo->prepare(
            "SELECT id, name, email, role, bio, avatar_url, is_active, created_at
             FROM users WHERE id = :id AND deleted_at IS NULL"
        );
        $stmt->execute([':id' => $id]);
        return $stmt->fetch() ?: null;
    }

    public function findByEmail(string $email): ?array {
        $stmt = $this->pdo->prepare(
            "SELECT * FROM users WHERE email = :email AND deleted_at IS NULL"
        );
        $stmt->execute([':email' => strtolower($email)]);
        return $stmt->fetch() ?: null;
    }

    public function findAll(array $filters = [], int $page = 1, int $perPage = 15): array {
        $where  = ['deleted_at IS NULL'];
        $params = [];

        if (!empty($filters['role'])) {
            $where[]         = 'role = :role';
            $params[':role'] = $filters['role'];
        }

        if (!empty($filters['search'])) {
            $where[]           = '(name LIKE :search OR email LIKE :search)';
            $params[':search'] = '%' . $filters['search'] . '%';
        }

        if (isset($filters['is_active'])) {
            $where[]               = 'is_active = :active';
            $params[':active']     = (int) $filters['is_active'];
        }

        $whereClause = implode(' AND ', $where);
        $offset      = ($page - 1) * $perPage;

        $total = (int) $this->pdo->prepare(
            "SELECT COUNT(*) FROM users WHERE {$whereClause}"
        )->execute($params) ? $this->pdo->prepare(
            "SELECT COUNT(*) FROM users WHERE {$whereClause}"
        )->execute($params) : 0;

        // Cleaner approach:
        $countStmt = $this->pdo->prepare("SELECT COUNT(*) FROM users WHERE {$whereClause}");
        $countStmt->execute($params);
        $total = (int) $countStmt->fetchColumn();

        $dataStmt = $this->pdo->prepare(
            "SELECT id, name, email, role, is_active, created_at
             FROM users WHERE {$whereClause}
             ORDER BY created_at DESC
             LIMIT :limit OFFSET :offset"
        );

        foreach ($params as $key => $value) {
            $dataStmt->bindValue($key, $value);
        }
        $dataStmt->bindValue(':limit',  $perPage, PDO::PARAM_INT);
        $dataStmt->bindValue(':offset', $offset,  PDO::PARAM_INT);
        $dataStmt->execute();

        return [
            'data'       => $dataStmt->fetchAll(),
            'total'      => $total,
            'page'       => $page,
            'per_page'   => $perPage,
            'last_page'  => max(1, (int) ceil($total / $perPage)),
        ];
    }

    public function create(array $data): int {
        $stmt = $this->pdo->prepare(
            "INSERT INTO users (name, email, password, role)
             VALUES (:name, :email, :password, :role)"
        );
        $stmt->execute([
            ':name'     => $data['name'],
            ':email'    => strtolower($data['email']),
            ':password' => password_hash($data['password'], PASSWORD_ARGON2ID),
            ':role'     => $data['role'] ?? 'reader',
        ]);
        return (int) $this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): bool {
        $allowed    = ['name', 'email', 'bio', 'avatar_url', 'is_active', 'role'];
        $setClauses = [];
        $params     = [':id' => $id];

        foreach ($data as $field => $value) {
            if (!in_array($field, $allowed, true)) continue;
            $setClauses[]        = "`{$field}` = :{$field}";
            $params[":{$field}"] = $value;
        }

        if (empty($setClauses)) return false;

        if (isset($data['password'])) {
            $setClauses[]       = 'password = :password';
            $params[':password'] = password_hash($data['password'], PASSWORD_ARGON2ID);
        }

        $stmt = $this->pdo->prepare(
            "UPDATE users SET " . implode(', ', $setClauses) . " WHERE id = :id"
        );
        $stmt->execute($params);
        return $stmt->rowCount() > 0;
    }

    public function delete(int $id): bool {
        $stmt = $this->pdo->prepare(
            "UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = :id"
        );
        $stmt->execute([':id' => $id]);
        return $stmt->rowCount() > 0;
    }
}

// ── Step 3: In-memory fake for testing ───────────────────────────────────────

class InMemoryUserRepository implements UserRepositoryInterface {

    private array $users = [];
    private int   $nextId = 1;

    public function findById(int $id): ?array {
        return $this->users[$id] ?? null;
    }

    public function findByEmail(string $email): ?array {
        foreach ($this->users as $user) {
            if ($user['email'] === strtolower($email)) return $user;
        }
        return null;
    }

    public function findAll(array $filters = [], int $page = 1, int $perPage = 15): array {
        $filtered = array_values($this->users);
        // Apply basic filters for testing...
        return [
            'data'      => array_slice($filtered, ($page - 1) * $perPage, $perPage),
            'total'     => count($filtered),
            'page'      => $page,
            'per_page'  => $perPage,
            'last_page' => max(1, (int) ceil(count($filtered) / $perPage)),
        ];
    }

    public function create(array $data): int {
        $id = $this->nextId++;
        $this->users[$id] = array_merge($data, [
            'id'         => $id,
            'created_at' => date('Y-m-d H:i:s'),
        ]);
        return $id;
    }

    public function update(int $id, array $data): bool {
        if (!isset($this->users[$id])) return false;
        $this->users[$id] = array_merge($this->users[$id], $data);
        return true;
    }

    public function delete(int $id): bool {
        if (!isset($this->users[$id])) return false;
        unset($this->users[$id]);
        return true;
    }
}

// ── Usage with dependency injection ──────────────────────────────────────────
class UserService {

    public function __construct(
        private readonly UserRepositoryInterface $users  // Accepts EITHER implementation
    ) {}

    public function register(array $data): int {
        if ($this->users->findByEmail($data['email'])) {
            throw new \InvalidArgumentException("Email already in use.");
        }
        return $this->users->create($data);
    }
}

// In production:
$service = new UserService(new MySQLUserRepository($pdo));

// In tests:
$service = new UserService(new InMemoryUserRepository());

Part 6 — ORM Deep Dive: Laravel Eloquent (How It Actually Works)

Setting Up Eloquent Outside Laravel

<?php
// Install: composer require illuminate/database illuminate/events

require_once 'vendor/autoload.php';

use Illuminate\Database\Capsule\Manager as Capsule;
use Illuminate\Events\Dispatcher;
use Illuminate\Container\Container;

$capsule = new Capsule;

$capsule->addConnection([
    'driver'    => 'mysql',
    'host'      => '127.0.0.1',
    'database'  => 'blog_platform',
    'username'  => 'root',
    'password'  => '',
    'charset'   => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix'    => '',
    'strict'    => true,       // Strict mode — enforce correct data types
    'engine'    => 'InnoDB',
]);

// Enable events (for model observers):
$capsule->setEventDispatcher(new Dispatcher(new Container));
$capsule->setAsGlobal();
$capsule->bootEloquent();

Complete Eloquent Model Definitions

<?php
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

// ── User Model ────────────────────────────────────────────────────────────────
class User extends Model {

    use SoftDeletes; // Enables soft delete with deleted_at column

    protected $table    = 'users';
    protected $fillable = ['name', 'email', 'password', 'role', 'bio', 'avatar_url'];
    protected $hidden   = ['password'];  // Never returned in toArray()/toJson()
    protected $casts    = [
        'is_active'  => 'boolean',
        'created_at' => 'datetime',
        'updated_at' => 'datetime',
        'deleted_at' => 'datetime',
    ];

    // ── Relationships ─────────────────────────────────────────────────────────

    public function posts(): \Illuminate\Database\Eloquent\Relations\HasMany {
        return $this->hasMany(Post::class, 'user_id');
    }

    public function publishedPosts(): \Illuminate\Database\Eloquent\Relations\HasMany {
        return $this->hasMany(Post::class, 'user_id')
                    ->where('status', 'published')
                    ->orderByDesc('published_at');
    }

    public function comments(): \Illuminate\Database\Eloquent\Relations\HasMany {
        return $this->hasMany(Comment::class, 'user_id');
    }

    // ── Accessors (computed attributes) ──────────────────────────────────────
    public function getFullNameAttribute(): string {
        return ucwords($this->name);
    }

    public function getPostCountAttribute(): int {
        return $this->posts()->count();
    }

    // ── Mutators (transform on set) ───────────────────────────────────────────
    public function setPasswordAttribute(string $value): void {
        $this->attributes['password'] = password_hash($value, PASSWORD_ARGON2ID);
    }

    public function setEmailAttribute(string $value): void {
        $this->attributes['email'] = strtolower(trim($value));
    }

    // ── Query Scopes (reusable query constraints) ─────────────────────────────
    public function scopeActive($query): \Illuminate\Database\Eloquent\Builder {
        return $query->where('is_active', true);
    }

    public function scopeRole($query, string $role): \Illuminate\Database\Eloquent\Builder {
        return $query->where('role', $role);
    }

    public function scopeSearch($query, string $term): \Illuminate\Database\Eloquent\Builder {
        return $query->where(function($q) use ($term) {
            $q->where('name', 'LIKE', "%{$term}%")
              ->orWhere('email', 'LIKE', "%{$term}%");
        });
    }
}

// ── Post Model ────────────────────────────────────────────────────────────────
class Post extends Model {

    use SoftDeletes;

    protected $fillable = ['user_id', 'title', 'slug', 'excerpt', 'content', 'status'];
    protected $casts    = [
        'published_at' => 'datetime',
        'view_count'   => 'integer',
    ];

    // ── Relationships ─────────────────────────────────────────────────────────

    // Belongs to one User (many posts → one user):
    public function author(): \Illuminate\Database\Eloquent\Relations\BelongsTo {
        return $this->belongsTo(User::class, 'user_id');
    }

    // Belongs to many Categories (many-to-many via post_categories):
    public function categories(): \Illuminate\Database\Eloquent\Relations\BelongsToMany {
        return $this->belongsToMany(
            Category::class,
            'post_categories',  // Pivot table
            'post_id',          // FK for this model
            'category_id'       // FK for related model
        )->withTimestamps();    // Include pivot timestamps if they exist
    }

    // Has many Comments:
    public function comments(): \Illuminate\Database\Eloquent\Relations\HasMany {
        return $this->hasMany(Comment::class, 'post_id')
                    ->where('is_approved', true)
                    ->orderBy('created_at');
    }

    // ── Scopes ────────────────────────────────────────────────────────────────
    public function scopePublished($query) {
        return $query->where('status', 'published')
                     ->whereNotNull('published_at')
                     ->where('published_at', '<=', now());
    }

    public function scopeByAuthor($query, int $userId) {
        return $query->where('user_id', $userId);
    }
}

// ── Category Model ────────────────────────────────────────────────────────────
class Category extends Model {

    public $timestamps  = false;
    protected $fillable = ['name', 'slug', 'description', 'parent_id'];

    public function posts(): \Illuminate\Database\Eloquent\Relations\BelongsToMany {
        return $this->belongsToMany(Post::class, 'post_categories', 'category_id', 'post_id');
    }

    public function parent(): \Illuminate\Database\Eloquent\Relations\BelongsTo {
        return $this->belongsTo(Category::class, 'parent_id');
    }

    public function children(): \Illuminate\Database\Eloquent\Relations\HasMany {
        return $this->hasMany(Category::class, 'parent_id');
    }
}

Eloquent CRUD — The Full Picture

<?php
// ── CREATE ─────────────────────────────────────────────────────────────────────

// Method 1: create() — mass assignment (requires $fillable)
$user = User::create([
    'name'     => 'Alice Johnson',
    'email'    => 'alice@example.com',
    'password' => 'securepassword123',  // Mutator auto-hashes this
    'role'     => 'author',
]);

// Method 2: new + save()
$post = new Post();
$post->user_id = $user->id;
$post->title   = 'My First Post';
$post->slug    = 'my-first-post';
$post->content = 'Post content here...';
$post->status  = 'draft';
$post->save();

// Method 3: firstOrCreate() — find or create
$category = Category::firstOrCreate(
    ['slug' => 'technology'],        // Find by these attributes
    ['name' => 'Technology', 'description' => 'Tech articles'] // Create with these if not found
);

// Method 4: updateOrCreate() — upsert
Category::updateOrCreate(
    ['slug' => 'technology'],        // Match condition
    ['name' => 'Technology & Dev']   // Update or create with these values
);

// ── Attach relationships ───────────────────────────────────────────────────────
// Attach categories to a post (many-to-many):
$post->categories()->attach([1, 2, 3]); // Attach category IDs 1, 2, 3
$post->categories()->detach([2]);        // Remove category 2
$post->categories()->sync([1, 3]);       // Set EXACTLY these categories (removes 2)

// ── READ ──────────────────────────────────────────────────────────────────────

// Eager loading (prevents N+1 queries):
$posts = Post::with(['author', 'categories'])          // Load related data in 3 queries total
              ->published()                             // Use scope
              ->orderByDesc('published_at')
              ->paginate(15);

// Lazy eager loading (after initial query):
$posts = Post::published()->get();
$posts->load('author'); // Load author for all posts in ONE query

// Nested eager loading:
$posts = Post::with([
    'author:id,name,avatar_url',  // Only select specific columns
    'categories',
    'comments.author',            // Nested: comments AND their authors
])->published()->get();

// Conditional eager loading:
$posts = Post::with(['categories' => function($query) {
    $query->where('parent_id', null); // Only top-level categories
}])->get();

// Aggregate in eager load:
$users = User::withCount('posts')->withSum('posts', 'view_count')->get();
// Each user now has: $user->posts_count and $user->posts_sum_view_count

// Chunking for large datasets (memory-efficient):
Post::published()->chunk(100, function($posts) {
    foreach ($posts as $post) {
        // Process each batch of 100 posts
        // Memory usage stays constant regardless of total record count
    }
});

// Lazy collection (even more memory-efficient for huge datasets):
foreach (Post::published()->lazy() as $post) {
    // Processes one at a time using cursor
}

// ── UPDATE ────────────────────────────────────────────────────────────────────

// Single record:
$post = Post::findOrFail(1); // Throws ModelNotFoundException if not found
$post->title  = 'Updated Title';
$post->status = 'published';
$post->save();

// Mass update (no model events fired):
Post::where('user_id', $userId)
    ->where('status', 'draft')
    ->update(['status' => 'archived']);

// Touch timestamps without changing data:
$post->touch(); // Updates updated_at to now

// ── DELETE ────────────────────────────────────────────────────────────────────

// Soft delete (model must use SoftDeletes trait):
$post->delete(); // Sets deleted_at, doesn't remove from DB

// Hard delete (bypasses soft delete):
$post->forceDelete();

// Restore soft-deleted record:
Post::withTrashed()->find(1)->restore();

// Query including soft-deleted:
$allPosts = Post::withTrashed()->get();
$deletedOnly = Post::onlyTrashed()->get();

// ── Advanced Queries via Eloquent ─────────────────────────────────────────────

// Raw expressions (when Eloquent methods aren't enough):
Post::selectRaw('DATE(published_at) as date, COUNT(*) as count')
    ->groupByRaw('DATE(published_at)')
    ->orderByRaw('date DESC')
    ->get();

// Subqueries:
$latestPostDate = Post::select('published_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
    ->limit(1)
    ->getQuery();

$users = User::addSelect(['latest_post_date' => $latestPostDate])->get();

Part 7 — Doctrine ORM (Enterprise-Grade Mapping)

Doctrine takes a different approach to Eloquent. Where Eloquent uses Active Record (model = database row), Doctrine uses Data Mapper (model is a pure PHP object, completely separate from persistence).

<?php
// Install: composer require doctrine/orm doctrine/dbal

use Doctrine\ORM\EntityManager;
use Doctrine\ORM\ORMSetup;
use Doctrine\DBAL\DriverManager;
use Doctrine\ORM\Mapping as ORM;

// ── Setup ─────────────────────────────────────────────────────────────────────
$config = ORMSetup::createAttributeMetadataConfiguration(
    paths: [__DIR__ . '/src/Entity'],
    isDevMode: true,  // false in production
);

$connection = DriverManager::getConnection([
    'dbname'   => 'blog_platform',
    'user'     => 'root',
    'password' => '',
    'host'     => '127.0.0.1',
    'driver'   => 'pdo_mysql',
    'charset'  => 'utf8mb4',
]);

$em = new EntityManager($connection, $config);

// ── Entity Definition (PHP 8 Attributes) ─────────────────────────────────────
// File: src/Entity/User.php
#[ORM\Entity(repositoryClass: UserDoctrineRepository::class)]
#[ORM\Table(name: 'users')]
#[ORM\HasLifecycleCallbacks]
class UserEntity {

    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer', unsigned: true)]
    private ?int $id = null;

    #[ORM\Column(type: 'string', length: 100)]
    private string $name;

    #[ORM\Column(type: 'string', length: 255, unique: true)]
    private string $email;

    #[ORM\Column(type: 'string', length: 255)]
    private string $password;

    #[ORM\Column(type: 'string', length: 20, enumType: UserRole::class)]
    private UserRole $role = UserRole::Reader;

    #[ORM\Column(type: 'text', nullable: true)]
    private ?string $bio = null;

    #[ORM\Column(name: 'is_active', type: 'boolean')]
    private bool $isActive = true;

    #[ORM\Column(name: 'created_at', type: 'datetime_immutable')]
    private \DateTimeImmutable $createdAt;

    #[ORM\Column(name: 'updated_at', type: 'datetime')]
    private \DateTime $updatedAt;

    // Relationship: One user has many posts
    #[ORM\OneToMany(
        mappedBy: 'author',
        targetEntity: PostEntity::class,
        cascade: ['persist', 'remove'],
        orphanRemoval: true
    )]
    #[ORM\OrderBy(['createdAt' => 'DESC'])]
    private \Doctrine\Common\Collections\Collection $posts;

    public function __construct(string $name, string $email, string $password) {
        $this->name      = $name;
        $this->email     = strtolower(trim($email));
        $this->password  = password_hash($password, PASSWORD_ARGON2ID);
        $this->createdAt = new \DateTimeImmutable();
        $this->updatedAt = new \DateTime();
        $this->posts     = new \Doctrine\Common\Collections\ArrayCollection();
    }

    // Lifecycle callbacks:
    #[ORM\PreUpdate]
    public function onPreUpdate(): void {
        $this->updatedAt = new \DateTime();
    }

    // Getters/Setters:
    public function getId(): ?int        { return $this->id; }
    public function getName(): string    { return $this->name; }
    public function getEmail(): string   { return $this->email; }
    public function isActive(): bool     { return $this->isActive; }
    public function getPosts(): \Doctrine\Common\Collections\Collection { return $this->posts; }

    public function setName(string $name): void  { $this->name = $name; }
    public function setBio(?string $bio): void    { $this->bio = $bio; }

    public function verifyPassword(string $password): bool {
        return password_verify($password, $this->password);
    }

    public function changePassword(string $newPassword): void {
        $this->password = password_hash($newPassword, PASSWORD_ARGON2ID);
    }

    public function addPost(PostEntity $post): void {
        if (!$this->posts->contains($post)) {
            $this->posts->add($post);
            $post->setAuthor($this);
        }
    }
}

// ── Doctrine CRUD Operations ──────────────────────────────────────────────────

// CREATE:
$user = new UserEntity('Bob Smith', 'bob@example.com', 'secretpass');
$em->persist($user);  // Tell Doctrine to track this entity
$em->flush();         // Write ALL pending changes to database in one transaction

// READ:
// By primary key:
$user = $em->find(UserEntity::class, 1);

// Using repository:
$userRepo = $em->getRepository(UserEntity::class);
$user = $userRepo->find(1);
$users = $userRepo->findAll();
$users = $userRepo->findBy(['role' => 'author'], ['name' => 'ASC'], 10, 0);
$user = $userRepo->findOneBy(['email' => 'bob@example.com']);

// Doctrine Query Language (DQL):
$dql = "SELECT u, p FROM UserEntity u LEFT JOIN u.posts p WHERE u.role = :role";
$query = $em->createQuery($dql)->setParameter('role', 'author');
$users = $query->getResult();

// QueryBuilder:
$qb = $em->createQueryBuilder();
$users = $qb->select('u', 'COUNT(p.id) AS post_count')
            ->from(UserEntity::class, 'u')
            ->leftJoin('u.posts', 'p')
            ->where('u.isActive = :active')
            ->setParameter('active', true)
            ->groupBy('u.id')
            ->orderBy('post_count', 'DESC')
            ->setMaxResults(20)
            ->getQuery()
            ->getResult();

// UPDATE:
$user = $em->find(UserEntity::class, 1);
$user->setName('Robert Smith');  // Modify the entity
$em->flush();                     // Doctrine detects change and generates UPDATE

// DELETE:
$user = $em->find(UserEntity::class, 1);
$em->remove($user);
$em->flush();

// ── Custom Repository ─────────────────────────────────────────────────────────
use Doctrine\ORM\EntityRepository;

class UserDoctrineRepository extends EntityRepository {

    public function findActiveAuthors(): array {
        return $this->createQueryBuilder('u')
            ->where('u.role = :role')
            ->andWhere('u.isActive = true')
            ->setParameter('role', 'author')
            ->orderBy('u.name', 'ASC')
            ->getQuery()
            ->getResult();
    }

    public function findByEmailDomain(string $domain): array {
        return $this->createQueryBuilder('u')
            ->where('u.email LIKE :domain')
            ->setParameter('domain', '%@' . $domain)
            ->getQuery()
            ->getResult();
    }

    public function searchByNameOrEmail(string $term): array {
        return $this->createQueryBuilder('u')
            ->where('u.name LIKE :term OR u.email LIKE :term')
            ->setParameter('term', '%' . $term . '%')
            ->getQuery()
            ->getResult();
    }
}

Part 8 — Database Migrations

The original article never mentions migrations. In any real project, you need a versioned history of schema changes that the whole team can apply consistently.

<?php
/**
 * Simple Migration System (without a framework)
 * For production use, consider Phinx (composer require robmorgan/phinx)
 */

class Migration {

    public function __construct(private readonly PDO $pdo) {}

    public function up(): void {
        $this->pdo->exec("
            CREATE TABLE IF NOT EXISTS users (
                id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
                name       VARCHAR(100) NOT NULL,
                email      VARCHAR(255) NOT NULL,
                password   VARCHAR(255) NOT NULL,
                role       ENUM('admin','author','reader') NOT NULL DEFAULT 'reader',
                is_active  TINYINT(1) NOT NULL DEFAULT 1,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY uk_email (email)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
    }

    public function down(): void {
        $this->pdo->exec("DROP TABLE IF EXISTS users");
    }
}

// ── Using Phinx (the industry-standard PHP migration tool) ─────────────────────

// phinx.php configuration:
return [
    'paths' => ['migrations' => 'db/migrations'],
    'environments' => [
        'default_migration_table' => 'phinxlog',
        'default_environment'     => 'development',
        'development' => [
            'adapter' => 'mysql',
            'host'    => '127.0.0.1',
            'name'    => 'blog_platform',
            'user'    => 'root',
            'pass'    => '',
            'port'    => '3306',
            'charset' => 'utf8mb4',
        ],
        'production' => [
            'adapter' => 'mysql',
            'host'    => $_ENV['DB_HOST'],
            'name'    => $_ENV['DB_NAME'],
            'user'    => $_ENV['DB_USER'],
            'pass'    => $_ENV['DB_PASS'],
        ],
    ],
];

// Create migration: vendor/bin/phinx create AddPostsTable
// Run migrations:   vendor/bin/phinx migrate
// Rollback last:    vendor/bin/phinx rollback

// Example Phinx migration:
use Phinx\Migration\AbstractMigration;

class AddPostsTable extends AbstractMigration {

    public function change(): void {
        $table = $this->table('posts', ['engine' => 'InnoDB', 'charset' => 'utf8mb4']);

        $table
            ->addColumn('user_id',      'integer',  ['unsigned' => true])
            ->addColumn('title',         'string',   ['limit' => 500])
            ->addColumn('slug',          'string',   ['limit' => 500])
            ->addColumn('content',       'longtext',  [])
            ->addColumn('status',        'enum',     ['values' => ['draft','published','archived'], 'default' => 'draft'])
            ->addColumn('published_at',  'datetime', ['null' => true, 'default' => null])
            ->addColumn('deleted_at',    'datetime', ['null' => true, 'default' => null])
            ->addColumn('created_at',    'datetime', ['default' => 'CURRENT_TIMESTAMP'])
            ->addColumn('updated_at',    'datetime', ['default' => 'CURRENT_TIMESTAMP', 'update' => 'CURRENT_TIMESTAMP'])
            ->addForeignKey('user_id', 'users', 'id', ['delete' => 'CASCADE'])
            ->addIndex(['slug'], ['unique' => true])
            ->addIndex(['status', 'published_at'])
            ->addIndex(['deleted_at'])
            ->create();
    }
}

Part 9 — Testing Database Interactions

The original article doesn’t mention testing. In production PHP, untested database code is a liability.

<?php
use PHPUnit\Framework\TestCase;

class UserRepositoryTest extends TestCase {

    private PDO $pdo;
    private MySQLUserRepository $repo;

    protected function setUp(): void {
        // Use SQLite in-memory database for fast, isolated tests:
        $this->pdo = new PDO('sqlite::memory:', null, null, [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);

        // Create the schema in the test database:
        $this->pdo->exec("
            CREATE TABLE users (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                name       TEXT NOT NULL,
                email      TEXT NOT NULL UNIQUE,
                password   TEXT NOT NULL,
                role       TEXT NOT NULL DEFAULT 'reader',
                is_active  INTEGER NOT NULL DEFAULT 1,
                deleted_at TEXT,
                created_at TEXT DEFAULT (datetime('now')),
                updated_at TEXT DEFAULT (datetime('now'))
            )
        ");

        $this->repo = new MySQLUserRepository($this->pdo);
    }

    protected function tearDown(): void {
        $this->pdo->exec("DELETE FROM users");
    }

    /** @test */
    public function it_creates_a_user_and_returns_the_new_id(): void {
        $id = $this->repo->create([
            'name'     => 'Alice',
            'email'    => 'alice@example.com',
            'password' => 'password123',
        ]);

        $this->assertIsInt($id);
        $this->assertGreaterThan(0, $id);
    }

    /** @test */
    public function it_finds_a_user_by_id(): void {
        $id   = $this->repo->create(['name' => 'Bob', 'email' => 'bob@test.com', 'password' => 'pass']);
        $user = $this->repo->findById($id);

        $this->assertNotNull($user);
        $this->assertEquals('Bob', $user['name']);
        $this->assertEquals('bob@test.com', $user['email']);
    }

    /** @test */
    public function it_returns_null_when_user_not_found(): void {
        $user = $this->repo->findById(999999);
        $this->assertNull($user);
    }

    /** @test */
    public function it_throws_on_duplicate_email(): void {
        $this->repo->create(['name' => 'Alice', 'email' => 'dup@test.com', 'password' => 'pass']);

        $this->expectException(\InvalidArgumentException::class);
        $this->repo->create(['name' => 'Alice 2', 'email' => 'dup@test.com', 'password' => 'pass']);
    }

    /** @test */
    public function it_soft_deletes_a_user(): void {
        $id = $this->repo->create(['name' => 'Carol', 'email' => 'carol@test.com', 'password' => 'pass']);

        $this->repo->delete($id);

        // findById should return null for soft-deleted user:
        $user = $this->repo->findById($id);
        $this->assertNull($user);

        // But the record should still exist in the database:
        $raw = $this->pdo->query("SELECT * FROM users WHERE id = {$id}")->fetch();
        $this->assertNotNull($raw['deleted_at']);
    }

    /** @test */
    public function it_paginates_results_correctly(): void {
        // Create 25 users:
        for ($i = 1; $i <= 25; $i++) {
            $this->repo->create(['name' => "User {$i}", 'email' => "user{$i}@test.com", 'password' => 'pass']);
        }

        $page1 = $this->repo->findAll([], 1, 10);
        $page2 = $this->repo->findAll([], 2, 10);
        $page3 = $this->repo->findAll([], 3, 10);

        $this->assertCount(10, $page1['data']);
        $this->assertCount(10, $page2['data']);
        $this->assertCount(5,  $page3['data']);
        $this->assertEquals(25, $page1['total']);
        $this->assertEquals(3,  $page1['last_page']);
    }
}

// ── Testing with the InMemoryRepository (no database needed) ─────────────────
class UserServiceTest extends TestCase {

    /** @test */
    public function it_prevents_duplicate_registrations(): void {
        $repo    = new InMemoryUserRepository();
        $service = new UserService($repo);

        $service->register(['name' => 'Alice', 'email' => 'alice@test.com', 'password' => 'pass']);

        $this->expectException(\InvalidArgumentException::class);
        $this->expectExceptionMessage("Email already in use.");

        $service->register(['name' => 'Alice 2', 'email' => 'alice@test.com', 'password' => 'pass']);
    }
}

Part 10 — When to Use Raw SQL vs Query Builder vs ORM

This is the most important decision in any PHP application. Here’s the definitive guide:

DECISION MATRIX
──────────────────────────────────────────────────────────────────────────────
Scenario                                         Recommended Approach
──────────────────────────────────────────────────────────────────────────────
Simple CRUD on 1-2 tables, small team            ORM (Eloquent or Doctrine)
Complex JOIN across 5+ tables                    Raw SQL with PDO
Real-time analytics / reporting queries          Raw SQL (ORM overhead matters)
Bulk insert of 10,000+ rows                      Raw SQL with batch insert
Full-text search                                 Raw SQL (MATCH AGAINST)
Geospatial queries (ST_Distance, etc.)           Raw SQL
Database-agnostic application                    ORM (change driver, not code)
High-traffic API (< 5ms response budget)         Raw SQL or Query Builder
Rapid prototyping                                ORM
Legacy codebase with complex schema              Query Builder (middle ground)
Microservice with thin data layer                Raw SQL with PDO
Enterprise app with domain-driven design         Doctrine (Data Mapper pattern)
WordPress / content site                         WordPress $wpdb + WP_Query
Laravel application                              Eloquent + raw SQL for complex queries
──────────────────────────────────────────────────────────────────────────────

PERFORMANCE COMPARISON (approximate):
──────────────────────────────────────────────────────────────────────────────
Method                  Overhead      Best For
──────────────────────────────────────────────────────────────────────────────
Raw SQL + PDO           ~0.1ms        High-frequency endpoints, reporting
Query Builder           ~0.3ms        Variable filters, readable code
Eloquent Active Record  ~0.5-2ms      Standard CRUD, rapid development
Doctrine Data Mapper    ~1-3ms        Complex domain models, DDD
──────────────────────────────────────────────────────────────────────────────
The overhead is per-query. For an endpoint making 5 queries:
- Raw: 0.5ms in ORM overhead
- Eloquent: 2.5-10ms in ORM overhead
For total response times of 50-200ms, this is rarely the bottleneck.
But for endpoints making 50-200 queries (admin pages, complex reports):
Raw SQL saves 25-100ms of overhead — potentially significant.

Every dynamic web application relies heavily on databases for storing, retrieving, and managing data. As a developer, understanding database interaction is essential — it’s how your application communicates with data stored in systems like MySQL, PostgreSQL, or SQLite.

In this guide, we’ll dive into the CRUD model (Create, Read, Update, Delete) and explore Object-Relational Mapping (ORM) — a modern way to simplify database operations.

1. What Is Database Interaction?

Database interaction refers to the communication between your application’s code and its database. Through queries, data can be created, read, updated, or deleted.

Developers use either:

• Raw SQL queries (traditional method), or
• ORM (Object-Relational Mapping) (modern, object-oriented approach).

2. Understanding CRUD Operations

CRUD stands for Create, Read, Update, and Delete — the four basic operations for managing data in a database table.

Let’s break it down with MySQL + PHP examples.

2.1 Create (Insert Data)

The Create operation adds a new record to your database.

$sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com')";
mysqli_query($conn, $sql);

Best Practice: Always use prepared statements to prevent SQL injection:

$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$stmt->execute();

2.2 Read (Fetch Data)

The Read operation retrieves stored records.

$result = $conn->query("SELECT * FROM users");
while($row = $result->fetch_assoc()) {
    echo $row['name'] . " - " . $row['email'] . "<br>";
}

Tip: Use LIMIT and WHERE clauses for performance optimization.

2.3 Update (Modify Data)

To modify existing records:

$sql = "UPDATE users SET email='newemail@example.com' WHERE id=1";
mysqli_query($conn, $sql);

Pro Tip: Always verify which record you are updating — accidental updates can overwrite data globally.

2.4 Delete (Remove Data)

To delete a record:

$sql = "DELETE FROM users WHERE id=1";
mysqli_query($conn, $sql);

Caution: Always add a WHERE condition to avoid deleting all rows!

3. What Is ORM (Object-Relational Mapping)?

While raw SQL gives full control, it can become repetitive and error-prone. ORM is a technique that lets developers interact with the database using objects instead of SQL queries.

ORM translates your object-oriented code into SQL commands behind the scenes.

Advantages of Using ORM

• ✅ Reduces boilerplate SQL code
• ✅ Prevents SQL injection automatically
• ✅ Easier maintenance and scalability
• ✅ Compatible with multiple databases (MySQL, PostgreSQL, SQLite)

4. Popular PHP ORMs

If you’re a PHP developer, you can use several ORM libraries:

Example: CRUD in Laravel Eloquent ORM

// Create
User::create(['name' => 'John', 'email' => 'john@example.com']);

// Read
$users = User::all();

// Update
$user = User::find(1);
$user->email = 'new@example.com';
$user->save();

// Delete
User::destroy(1);

Why Developers Prefer ORM:

This single syntax replaces multiple lines of SQL, improving both readability and maintainability.

5. When to Use ORM vs Raw SQL

6. Best Practices for Database Interaction

1. Always sanitize inputs (even with ORM).
2. Use transactions for critical operations (insert/update/delete).
3. Index your tables for faster queries.
4. Backup regularly and monitor query performance.
5. Use connection pooling in high-traffic systems.

The Three Principles That Separate Good Database Code From Bad

The original article gives you the syntax. This guide gives you the architecture, the patterns, and the judgment.

Principle 1: Always Use Prepared Statements — not because “SQL injection is bad” (which is obvious), but because prepared statements also improve performance through query caching, handle type conversion correctly, and eliminate the entire class of input-escaping bugs. There is never a valid reason to use string concatenation in SQL queries.

Principle 2: Transactions Are Mandatory for Multi-Step Operations — Any operation that modifies more than one row, or modifies one row and sends an email, or modifies a row and writes to a log must be wrapped in a transaction. The cost of not doing so is data inconsistency that appears only under failure conditions — exactly when it’s hardest to debug.

Principle 3: The Repository Pattern is the Minimum Viable Architecture — Scattering $pdo->prepare() calls throughout controllers, models, and views is technical debt that compounds. A repository interface between your business logic and your database means you can test without a database, switch databases without rewriting business logic, and add caching, logging, or query optimisation in a single place.

The choice between raw PDO, a query builder, Eloquent, and Doctrine is secondary to these three principles. Any of the four approaches, applied correctly with transactions, prepared statements, and a repository layer, produces secure and maintainable code. Applied without them, all four produce vulnerabilities and unmaintainable spaghetti.

Database interaction is the foundation of every dynamic application. Mastering CRUD operations builds your core understanding, while adopting ORM makes your development cleaner and faster.

Whether you’re managing a small user database or architecting enterprise-scale systems, the combination of CRUD principles and ORM techniques ensures your application remains secure, maintainable, and scalable.