PHP MySQL Delete Data

Removing a row from a table permanently -- a cancelled order, a deleted comment, an expired session -- is done with a DELETE statement. Because DELETE removes data outright, with no built-in undo, it is one of the SQL statements that deserves the most care around exactly which rows a WHERE clause actually targets.

In this page:

Basic DELETE with a WHERE Clause
Checking How Many Rows Were Deleted
The Danger of Deleting Without WHERE
Soft Deletes as a Safer Alternative
Deleting Related Rows Together

Basic DELETE with a WHERE Clause

DELETE FROM tableName WHERE condition removes only the rows matching that condition, leaving every other row completely untouched -- DELETE FROM users WHERE id = 5 removes exactly one specific user, no more.

Note: Always write and double-check the WHERE clause before running a DELETE -- consider testing the equivalent SELECT first to confirm exactly which rows would be affected.

Warning: DELETE FROM users WHERE id = 5 with a typo turning it into WHERE id = 5 OR 1 = 1 would delete every row in the table -- always review WHERE clauses carefully.

Example: Basic DELETE with a WHERE Clause

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, name TEXT)");
$db->exec("INSERT INTO users VALUES (5, 'Alice')");
$db->exec("DELETE FROM users WHERE id = 5");
echo "Row removed";
?>

Checking How Many Rows Were Deleted

mysqli_affected_rows($conn) returns the number of rows the most recent query actually modified -- for a DELETE, this confirms exactly how many rows were removed, which is useful for detecting when a delete matched zero rows (perhaps the ID never existed).

Note: Check mysqli_affected_rows() after a DELETE to confirm the operation removed the expected number of rows, especially in scripts where "nothing was deleted" matters.

Warning: A DELETE affecting 0 rows is not an error -- it simply means no rows matched the WHERE clause, which mysqli_query() reports as a successful (but empty) operation.

Example: Checking How Many Rows Were Deleted

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER)");
$db->exec("INSERT INTO users VALUES (1), (2)");
$db->exec("DELETE FROM users WHERE id = 1");
echo $db->changes() . " row(s) deleted"; // mysqli_affected_rows($conn) equivalent
?>

The Danger of Deleting Without WHERE

DELETE FROM tableName with no WHERE clause at all is entirely valid SQL, but it deletes every single row in the table -- a devastating mistake if run by accident, since there is no built-in confirmation step or undo.

Note: Before running any DELETE in a live environment, read it back to yourself and confirm a WHERE clause is present and correctly scoped, especially in scripts run manually or via a migration tool.

Warning: Unlike TRUNCATE, DELETE without WHERE still logs each row removal individually and can be slower on very large tables, but the end result -- every row gone -- is identical.

Example: The Danger of Deleting Without WHERE

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER)");
$db->exec("INSERT INTO users VALUES (1), (2), (3)");
// DELETE FROM users; -- with no WHERE, this would remove every row!
$db->exec("DELETE FROM users WHERE id = 1");
echo $db->changes() . " row deleted (not all 3)";
?>

Soft Deletes as a Safer Alternative

Instead of permanently removing a row with DELETE, a "soft delete" sets a flag column (like deleted_at or is_deleted) to mark the row as deleted while keeping it in the table -- queries elsewhere in the app then filter out soft-deleted rows, but the data remains recoverable if needed.

Note: Consider a soft-delete flag for data where recovery might genuinely matter, like user accounts or orders, reserving hard DELETE for truly disposable data like expired sessions.

Warning: Every query elsewhere in the application must remember to exclude soft-deleted rows (WHERE deleted_at IS NULL), or deleted records can accidentally reappear in results.

Example: Soft Deletes as a Safer Alternative

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER, deleted_at TEXT)");
$db->exec("INSERT INTO users VALUES (1, NULL)");
$db->exec("UPDATE users SET deleted_at = '2024-01-01' WHERE id = 1");
echo "Row kept, just flagged as deleted";
?>

Deleting Related Rows Together

When a row being deleted has related rows in another table (like an order and its order items), those related rows need handling too -- either deleted explicitly beforehand, or automatically via a foreign key configured with ON DELETE CASCADE at the database level.

Note: Configure ON DELETE CASCADE at the database schema level for genuinely dependent child records, so related data is cleaned up automatically and consistently.

Warning: Deleting a parent row while related child rows still reference it (without CASCADE configured) typically fails with a foreign key constraint error, rather than silently leaving orphaned data.

Example: Deleting Related Rows Together

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE orders (id INTEGER)");
$db->exec("CREATE TABLE items (order_id INTEGER)");
$db->exec("INSERT INTO orders VALUES (1)");
$db->exec("INSERT INTO items VALUES (1)");
$db->exec("DELETE FROM items WHERE order_id = 1"); // delete children first
$db->exec("DELETE FROM orders WHERE id = 1");
echo "Both order and its items removed";
?>

Common Mistakes

Running DELETE FROM table without a WHERE clause, which deletes every single row in the table -- often the exact opposite of what was intended.
Building a DELETE's WHERE clause from unvalidated, unbound user input, risking both SQL injection and deleting the wrong rows entirely.
Assuming a deleted row can be recovered afterward -- without a backup or a "soft delete" flag column, a DELETE is permanent.

Chapter Summary

DELETE FROM table WHERE condition removes only the rows matching that condition.
DELETE FROM table with no WHERE clause at all removes every row in the table -- always double-check a WHERE clause is present before running one.
mysqli_affected_rows($conn) reports how many rows a DELETE statement actually removed, useful for confirming the expected number were deleted.

Browser Support

DELETE FROM is standard SQL and works identically across all MySQL and MariaDB versions PHP supports.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Order By Next Lesson → PHP MySQL Update Data