PHP MySQL CRUD 操作

CRUD 是指 Create（新增）、Read（讀取）、Update（更新）、Delete（刪除）四種基本資料庫操作。

建立資料表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

資料庫連接

<?php
$pdo = new PDO(
    "mysql:host=localhost;dbname=myapp;charset=utf8mb4",
    "root",
    "secret",
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);
?>

Create（新增）

<?php
function createUser(PDO $pdo, string $name, string $email): int {
    $sql = "INSERT INTO users (name, email) VALUES (?, ?)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$name, $email]);
    return (int) $pdo->lastInsertId();
}

// 使用
$id = createUser($pdo, 'Alice', 'alice@example.com');
echo "新增成功，ID: $id";
?>

Read（讀取）

<?php
// 取得所有使用者
function getAllUsers(PDO $pdo): array {
    return $pdo->query("SELECT * FROM users ORDER BY id")->fetchAll();
}

// 取得單一使用者
function getUserById(PDO $pdo, int $id): ?array {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch() ?: null;
}

// 搜尋使用者
function searchUsers(PDO $pdo, string $keyword): array {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE ?");
    $stmt->execute(["%$keyword%"]);
    return $stmt->fetchAll();
}

// 分頁
function getUsersPaginated(PDO $pdo, int $page = 1, int $perPage = 10): array {
    $offset = ($page - 1) * $perPage;
    $stmt = $pdo->prepare("SELECT * FROM users LIMIT ? OFFSET ?");
    $stmt->execute([$perPage, $offset]);
    return $stmt->fetchAll();
}
?>

Update（更新）

<?php
function updateUser(PDO $pdo, int $id, string $name, string $email): bool {
    $sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$name, $email, $id]);
    return $stmt->rowCount() > 0;
}

// 使用
if (updateUser($pdo, 1, 'Alice Updated', 'alice.new@example.com')) {
    echo "更新成功";
} else {
    echo "沒有資料被更新";
}
?>

Delete（刪除）

<?php
function deleteUser(PDO $pdo, int $id): bool {
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->rowCount() > 0;
}

// 使用
if (deleteUser($pdo, 1)) {
    echo "刪除成功";
} else {
    echo "找不到資料";
}
?>

完整範例：使用者管理類別

<?php
class UserRepository {
    public function __construct(private PDO $pdo) {}
    
    public function findAll(): array {
        return $this->pdo->query("SELECT * FROM users")->fetchAll();
    }
    
    public function findById(int $id): ?array {
        $stmt = $this->pdo->prepare("SELECT * FROM users WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }
    
    public function create(array $data): int {
        $stmt = $this->pdo->prepare(
            "INSERT INTO users (name, email) VALUES (:name, :email)"
        );
        $stmt->execute([
            'name' => $data['name'],
            'email' => $data['email'],
        ]);
        return (int) $this->pdo->lastInsertId();
    }
    
    public function update(int $id, array $data): bool {
        $stmt = $this->pdo->prepare(
            "UPDATE users SET name = :name, email = :email WHERE id = :id"
        );
        $stmt->execute([
            'id' => $id,
            'name' => $data['name'],
            'email' => $data['email'],
        ]);
        return $stmt->rowCount() > 0;
    }
    
    public function delete(int $id): bool {
        $stmt = $this->pdo->prepare("DELETE FROM users WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->rowCount() > 0;
    }
}

// 使用
$repo = new UserRepository($pdo);

// 新增
$id = $repo->create(['name' => 'Alice', 'email' => 'alice@example.com']);

// 讀取
$user = $repo->findById($id);

// 更新
$repo->update($id, ['name' => 'Alice Updated', 'email' => 'alice@example.com']);

// 刪除
$repo->delete($id);
?>