DELETE Queries

DELETE removes rows.

1
2
3
4
5
6
7

<?php
$pdo = require __DIR__ . "/db.php";

$statement = $pdo->prepare("DELETE FROM users WHERE id = :id");
$statement->execute(["id" => 3]);

echo $statement->rowCount() . " user deleted";

1

1 user deleted

Do not forget WHERE

1
2

DELETE FROM users;               -- deletes EVERY user
DELETE FROM users WHERE id = 3;  -- deletes one

A DELETE without WHERE empties the table, and there is no undo. Back up before risky changes, and test your condition with a SELECT first.

Check that something was deleted

1
2
3
4
5
6
7
8
9

<?php
$pdo = require __DIR__ . "/db.php";

$statement = $pdo->prepare("DELETE FROM products WHERE id = :id");
$statement->execute(["id" => 999]);

if ($statement->rowCount() === 0) {
    echo "Nothing to delete";
}

1

Nothing to delete

Delete must be a POST request

Never delete with a plain link (delete.php?id=5). A link is a GET request, and anything can trigger a GET: a search engine crawler, a browser pre-loader, or an image tag on another site (<img src="https://yoursite/delete.php?id=5">).

Use a small form with POST, and a CSRF token (see CSRF).

1
2
3
4
5

<form method="post" action="delete.php">
  <input type="hidden" name="id" value="5">
  <input type="hidden" name="csrf_token" value="<?= htmlspecialchars($_SESSION["csrf_token"]) ?>">
  <button type="submit">Delete</button>
</form>

1
2
3
4
5

<?php
if ($_SERVER["REQUEST_METHOD"] !== "POST") {
    http_response_code(405);
    exit;
}

Check ownership

As with updates, make sure the user may delete that row.

1
2
3

<?php
$statement = $pdo->prepare("DELETE FROM posts WHERE id = :id AND user_id = :user");
$statement->execute(["id" => $id, "user" => $_SESSION["user_id"]]);

Foreign keys

If other tables point to the row (a user has posts), the database may refuse the delete, or delete the related rows too (ON DELETE CASCADE). Know which one you set up.

Soft delete: mark, do not remove

Often it is better not to delete the row at all, but to mark it as deleted. You can restore it, keep a history and avoid broken relations.

1

ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

1
2

UPDATE posts SET deleted_at = NOW() WHERE id = 5;       -- "delete"
SELECT * FROM posts WHERE deleted_at IS NULL;           -- normal listings

Truncate

TRUNCATE TABLE notes removes all rows and resets the AUTO_INCREMENT counter. It is fast and cannot be rolled back in MySQL, so use it only for temporary tables and test data.

Delete in a transaction

If you delete from several tables, use a transaction so that the work is done completely or not at all. See Handling Database Errors.