MySQL Delete Data

Removing Rows From a Table

In simple terms: DELETE removes rows from a table — a cancelled draft, a test account, an expired login token. Deleting is permanent: once the statement runs (and the transaction is committed), the data is gone. That is why deletes must always be targeted precisely and handled with care.

Technically: DELETE FROM table WHERE condition removes every row that matches the condition. Without a WHERE clause, it removes all rows. After execution, affected_rows reports how many rows were deleted. Foreign key constraints may block a delete (ON DELETE RESTRICT) or automatically delete related rows (ON DELETE CASCADE).

Syntax

DELETE FROM customers
WHERE id = ?;

Warning: DELETE FROM customers; — without WHERE — deletes every customer. Always write the WHERE clause first, and test the condition with a SELECT before running a delete on important data.

Deleting a Row With a Prepared Statement

<?php
require __DIR__ . '/src/db.php';

function deleteCustomerByEmail(mysqli $db, string $email): string
{
    $db->execute_query('DELETE FROM customers WHERE email = ?', [$email]);

    return $db->affected_rows === 1
        ? "Deleted customer $email"
        : "No customer found with $email";
}

echo deleteCustomerByEmail(db(), 'test@example.com') . "\n";
echo deleteCustomerByEmail(db(), 'test@example.com') . "\n";

Output

Deleted customer test@example.com
No customer found with test@example.com

How It Works

1. The email is passed as a parameter; the WHERE clause matches exactly one row thanks to the UNIQUE index on email.
2. affected_rows is 1 after the first call and 0 after the second — deleting a non-existent row is not an error, so check affected_rows to know whether anything happened.
3. The function reports the outcome so the calling code (or user) gets meaningful feedback.

Foreign Keys Protect Related Data

The order_items.product_id foreign key uses ON DELETE RESTRICT (see MySQL Create Table). Deleting a product that appears in an order is therefore rejected:

<?php
require __DIR__ . '/src/db.php';

try {
    db()->execute_query('DELETE FROM products WHERE sku = ?', ['PEN-01']);
} catch (mysqli_sql_exception $e) {
    echo "Error {$e->getCode()}\n";
    echo $e->getMessage() . "\n";
}

Output

Error 1451
Cannot delete or update a parent row: a foreign key constraint fails (`gocourse_shop`.`order_items`, CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE RESTRICT)

The Gel Pen is part of order #501, and deleting it would leave that order referring to a product that no longer exists. MySQL refuses. In the application, translate error 1451 into a helpful message, such as "This product has been ordered and cannot be deleted. Deactivate it instead."

Cascading Deletes

The order_items.order_id foreign key uses ON DELETE CASCADE, so deleting an order automatically deletes its items:

<?php
require __DIR__ . '/src/db.php';

$db = db();

// A temporary test order with one item
$db->query('INSERT INTO orders (id, customer_id, status, total) VALUES (900, 2, "pending", 60.00)');
$db->query('INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (900, 2, 1, 60.00)');

$countItems = fn(): int => (int) $db->query('SELECT COUNT(*) FROM order_items WHERE order_id = 900')->fetch_column();

echo "Items before: " . $countItems() . "\n";

$db->execute_query('DELETE FROM orders WHERE id = ?', [900]);
echo "Orders deleted: {$db->affected_rows}\n";
echo "Items after: " . $countItems() . "\n";

Output

Items before: 1
Orders deleted: 1
Items after: 0

affected_rows counts only rows deleted directly from orders; the cascaded item rows are removed automatically. Cascades are convenient for data that has no meaning without its parent (order items without an order). For important records (customers, products), RESTRICT is safer.

Soft Deletes: Hiding Instead of Removing

Many applications never physically delete important records. Instead, they mark them as deleted:

ALTER TABLE customers ADD COLUMN deleted_at DATETIME NULL;

<?php
function softDeleteCustomer(mysqli $db, int $id): bool
{
    $db->execute_query('UPDATE customers SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL', [$id]);
    return $db->affected_rows === 1;
}

// Every normal query must then exclude deleted rows:
// SELECT ... FROM customers WHERE deleted_at IS NULL

Hard Delete vs Soft Delete

ORMs such as Laravel's Eloquent apply the deleted_at IS NULL filter automatically. For personal data, data protection laws may require real deletion or anonymization after a request — soft deletes alone are not enough.

Deleting Many Rows

Deleting by Condition

<?php
// Remove expired password-reset tokens (example table)
$db->execute_query('DELETE FROM password_resets WHERE expires_at < ?', [gmdate('Y-m-d H:i:s')]);
echo "Removed {$db->affected_rows} expired tokens\n";

Deleting in Batches

Deleting millions of rows in one statement can lock the table for a long time and fill the transaction log. Delete in chunks instead:

<?php
do {
    $db->execute_query('DELETE FROM activity_log WHERE created_at < ? LIMIT 1000', ['2025-01-01']);
} while ($db->affected_rows === 1000);

TRUNCATE

TRUNCATE TABLE logs; removes all rows very quickly and resets the AUTO_INCREMENT counter. It cannot be rolled back, does not run row-level checks, and fails on tables referenced by foreign keys. Use it only for staging or log tables.

Deleting Safely From a Web Interface

• Use POST (or DELETE) requests, never GET links: browsers, prefetchers, and crawlers follow links automatically.
• Protect with CSRF tokens (see CSRF Protection).
• Check authorization: a user may delete only records they own or are allowed to manage — DELETE FROM orders WHERE id = ? AND customer_id = ?.
• Ask for confirmation before destructive actions, and consider an "undo" period (soft delete first, purge later).
• Keep backups: regular, tested database backups are the final safety net.

Common Mistakes

Forgetting the WHERE Clause

Effect: All rows are deleted.

Fix: Write and test the WHERE condition first; consider enabling MySQL's sql_safe_updates option in interactive sessions.

Deleting via GET Links

Effect: Data is deleted by crawlers or prefetching.

Fix: Use POST forms with CSRF tokens.

Ignoring affected_rows

Effect: The application reports success although nothing was deleted.

Fix: Check affected_rows.

Not Handling Foreign Key Errors

Effect: Users see raw database errors.

Fix: Catch error 1451 and show a helpful message, or deactivate records instead.

Best Practices

• Always delete with a precise WHERE clause and prepared statement parameters.
• Check affected_rows to confirm the result.
• Choose RESTRICT or CASCADE deliberately for each relationship.
• Prefer soft deletes or deactivation for important business records.
• Delete large amounts of data in batches, and keep reliable backups.

Official reference: https://www.php.net/manual/en/mysqli.query.php