PHP CRUD Operations

CRUD — Create, Read, Update, Delete — covers every database operation a web app performs.

1 - Shared DB Helper

function db(): PDO {
    static $pdo = null;
    if ($pdo === null) {
        $pdo = new PDO(
            "mysql:host=localhost;dbname=blog;charset=utf8mb4",
            "root", "",
            [PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
             PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]
        );
    }
    return $pdo;
}

2 - Create

function createPost(string $title, string $body, int $userId): int {
    $stmt = db()->prepare(
        "INSERT INTO posts (title, body, user_id, created_at) VALUES (?, ?, ?, NOW())"
    );
    $stmt->execute([$title, $body, $userId]);
    return (int) db()->lastInsertId();
}

3 - Read

function getPost(int $id): ?array {
    $stmt = db()->prepare("SELECT * FROM posts WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch() ?: null;
}

function getPosts(int $limit = 10, int $offset = 0): array {
    $stmt = db()->prepare("SELECT * FROM posts ORDER BY created_at DESC LIMIT ? OFFSET ?");
    $stmt->execute([$limit, $offset]);
    return $stmt->fetchAll();
}

4 - Update

function updatePost(int $id, string $title, string $body): bool {
    $stmt = db()->prepare("UPDATE posts SET title = ?, body = ?, updated_at = NOW() WHERE id = ?");
    $stmt->execute([$title, $body, $id]);
    return $stmt->rowCount() > 0;
}

5 - Delete

function deletePost(int $id): bool {
    $stmt = db()->prepare("DELETE FROM posts WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->rowCount() > 0;
}