刪除操作風險最高,本文示範單筆刪除、批次刪除的安全實作,強調 WHERE 條件必要性、預處理語句防注入、事務保障一致性。
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
<?php
// delete_one.php
require 'db.php'; // PDO 連線
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['id'])) {
$id = (int)$_POST['id'];
if ($id <= 0) die('無效 ID');
try {
$stmt = $pdo->prepare('DELETE FROM employees WHERE id = ? LIMIT 1');
$stmt->execute([$id]);
if ($stmt->rowCount()) {
echo "員工 #$id 刪除成功";
} else {
echo "找不到該員工";
}
} catch (PDOException $e) {
error_log($e->getMessage());
echo '系統錯誤,請稍後再試';
}
}
?>
<form method="post">
輸入員工編號:<input type="number" name="id" min="1" required>
<button type="submit">刪除</button>
</form>
<?php
// delete_batch.php
require 'db.php';
if ($_SERVER['REQUEST_METHOD'] === 'POST' && !empty($_POST['ids'])) {
$ids = array_map('intval', $_POST['ids']); // 強制轉整數,防注入
$ids = array_filter($ids, fn($id) => $id > 0);
if (!$ids) die('未選擇有效項目');
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = "DELETE FROM employees WHERE id IN ($placeholders)";
try {
$pdo->beginTransaction();
$stmt = $pdo->prepare($sql);
$stmt->execute($ids);
$deleted = $stmt->rowCount();
$pdo->commit();
echo "成功刪除 $deleted 筆資料";
} catch (PDOException $e) {
$pdo->rollBack();
error_log($e->getMessage());
echo '批次刪除失敗';
}
}
?>
<form method="post">
<table border="1">
<tr><th><input type="checkbox" id="checkAll"></th><th>編號</th><th>姓名</th><th>年齡</th></tr>
<?php
$stmt = $pdo->query('SELECT id, name, age FROM employees');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo "<tr><td><input type='checkbox' name='ids[]' value='{$row['id']}'></td>";
echo "<td>{$row['id']}</td><td>{$row['name']}</td><td>{$row['age']}</td></tr>";
}
?>
</table>
<button type="submit" onclick="return confirm('確定刪除選中項目?')">批次刪除</button>
</form>
<script>document.getElementById('checkAll').onclick = e => document.querySelectorAll('input[name="ids[]"]').forEach(c => c.checked = e.target.checked);</script>
(int)、intval() 確保 ID 為整數。LIMIT 1 防誤刪全表。deleted_at 欄位,查詢時 WHERE deleted_at IS NULL,誤刪可恢復。ALTER TABLE employees ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;
CREATE INDEX idx_deleted ON employees(deleted_at);
-- 查詢時
SELECT * FROM employees WHERE deleted_at IS NULL;
-- 刪除變更新
UPDATE employees SET deleted_at = NOW() WHERE id = ?;