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

Syntax

php

DELETE FROM table_name WHERE condition;   // without WHERE, every row is deleted

mysqli_query($conn, $sql);
mysqli_affected_rows($conn);   // how many rows were deleted

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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$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");
// Print "Row removed" to the output
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$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");
// Print "Row kept, just flagged as deleted" to the output
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";
?>

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