PHP PDO 开发 CRUD API：列表、详情、新增、更新、删除完整示例 [复制链接]

这份示例使用 PDO + SQLite，单文件即可运行，演示常见 CRUD 接口。把代码保存为 crud-api.php，启动后即可测试。

<?php
declare(strict_types=1);

header('Content-Type: application/json; charset=utf-8');

$pdo = new PDO('sqlite:' . __DIR__ . '/demo.sqlite');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    created_at TEXT NOT NULL
)');

function response(int $code, string $message, $data = null, int $status = 200): void
{
    http_response_code($status);
    echo json_encode(compact('code', 'message', 'data'), JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}

function input(): array
{
    $data = json_decode(file_get_contents('php://input') ?: '{}', true);
    return is_array($data) ? $data : [];
}

$method = $_SERVER['REQUEST_METHOD'] ?? 'GET';
$path = trim(parse_url($_SERVER['REQUEST_URI'] ?? '/', PHP_URL_PATH), '/');
$parts = explode('/', $path);

if (($parts[0] ?? '') !== 'api' || ($parts[1] ?? '') !== 'posts') {
    response(404, '接口不存在', null, 404);
}

$id = isset($parts[2]) ? (int)$parts[2] : 0;

if ($method === 'GET' && $id === 0) {
    $page = max(1, (int)($_GET['page'] ?? 1));
    $pageSize = min(50, max(1, (int)($_GET['page_size'] ?? 10)));
    $offset = ($page - 1) * $pageSize;

    $stmt = $pdo->prepare('SELECT * FROM posts ORDER BY id DESC LIMIT :limit OFFSET :offset');
    $stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    response(0, 'ok', [
        'page' => $page,
        'page_size' => $pageSize,
        'list' => $stmt->fetchAll(PDO::FETCH_ASSOC),
    ]);
}

if ($method === 'GET' && $id > 0) {
    $stmt = $pdo->prepare('SELECT * FROM posts WHERE id = ?');
    $stmt->execute([$id]);
    $post = $stmt->fetch(PDO::FETCH_ASSOC);
    $post ? response(0, 'ok', $post) : response(404, '记录不存在', null, 404);
}

if ($method === 'POST') {
    $data = input();
    $title = trim((string)($data['title'] ?? ''));
    $content = trim((string)($data['content'] ?? ''));
    if ($title === '' || $content === '') {
        response(422, 'title 和 content 必填', null, 422);
    }

    $stmt = $pdo->prepare('INSERT INTO posts(title, content, created_at) VALUES (?, ?, ?)');
    $stmt->execute([$title, $content, date('Y-m-d H:i:s')]);
    response(0, '创建成功', ['id' => (int)$pdo->lastInsertId()], 201);
}

if ($method === 'PUT' && $id > 0) {
    $data = input();
    $stmt = $pdo->prepare('UPDATE posts SET title = ?, content = ? WHERE id = ?');
    $stmt->execute([
        trim((string)($data['title'] ?? '')),
        trim((string)($data['content'] ?? '')),
        $id,
    ]);
    response(0, '更新成功');
}

if ($method === 'DELETE' && $id > 0) {
    $stmt = $pdo->prepare('DELETE FROM posts WHERE id = ?');
    $stmt->execute([$id]);
    response(0, '删除成功');
}

response(405, '请求方法不支持', null, 405);

运行方式：

# 启动
php -S 127.0.0.1:8000 crud-api.php

# 新增
curl -X POST http://127.0.0.1:8000/api/posts   -H 'Content-Type: application/json'   -d '{"title":"第一篇","content":"CRUD 示例"}'

# 列表
curl 'http://127.0.0.1:8000/api/posts?page=1&page_size=10'