MySQL Delete Data

La Lakshmi Priya Updated 04 Nov 2026
5 min read ·Lesson 221 of 229
« Previous Next »

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 The email is passed as a parameter; the WHERE clause matches exactly one row thanks to the UNIQUE index on email. 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. 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 Hard Delete (DELETE) Soft Delete (deleted_at) Data recoverable No (only from backups) Yes Related records stay valid Must be handled by foreign keys Yes, references remain Queries Simple Must filter deleted_at IS NULL everywhere Privacy (right to erasure) Data is gone Data still exists; may need real deletion or anonymization Unique constraints Freed immediately The email remains "taken" unless handled 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

← Previous
MySQL Order By
Next →
MySQL Update Data

More in PHP

See all
Post image PHP
PDO Fetch Modes
PDO Fetch Modes What Are Fetch Modes? In simple terms: After a query runs, PDO hands you the result row…
La Lakshmi Priya 04 Nov 2026
Post image PHP
PDO Prepared Statements
PDO Prepared Statements Why Prepared Statements? In simple terms: A prepared statement keeps the SQL co…
La Lakshmi Priya 04 Nov 2026
Post image PHP
PDO Connect
PDO Connect Connecting With PDO In simple terms: To use a database through PDO, you create a PDO object…
La Lakshmi Priya 04 Nov 2026
Post image PHP
PDO Introduction
PDO Introduction What Is PDO? In simple terms: PDO (PHP Data Objects) is PHP's standard way to talk to …
La Lakshmi Priya 04 Nov 2026

0 Comments

Reviewed before they appear

No comments yet.

PHP
PHP Home PHP Switch PHP Match Alternative Syntax PHP Loops While Loop Do While Loop For Loop Foreach Loop Nested Loops Break Statement PHP Intro PHP Install PHP Built-in Web Server PHP Syntax PHP Tags PHP and HTML PHP Comments PHP Multiline Comments PHP Variables Variables Scope PHP Casting Logical Operators Null Coalescing Operator Spaceship Operator Operator Precedence Continue Statement Modify Strings PHP If...Else...Elseif PHP If Modify Strings PHP If Operators PHP Functions Concatenate Strings PHP If...Else User-defined Functions Slicing Strings Function Arguments PHP Shorthand if Escape Characters Default Argument Values PHP Nested if Pass by Reference String Interpolation Named Arguments PHP File Upload Variadic Functions Return Values Search in Strings Multibyte Strings Type Declarations in Functions PHP Numbers PHP Math PHP Floats PHP Random Numbers PHP Constants PHP Properties PHP Methods PHP Constructor Constructor Property Promotion PHP Cookies PHP Destructor PHP Access Modifiers Readonly Properties PHP Sessions PHP Inheritance Method Overriding PHP Filters The final Keyword CSRF Protection PHP Filters Advanced Session Security Secure Cookies PHP Magic Constants File Upload Security PHP Callback Functions PHP Operators Filesystem Security PHP JSON Arithmetic Operators Error Reporting in Production Hiding PHP Assignment Operators PHP Configuration (php.ini) Comparison Operators PHP Command Line (CLI) PHP Exceptions Increment / Decrement Operators Migrating to PHP 8 PHP Form Validation PHP Form Required PHP Form URL/E-mail Error Handling Form Input Sanitizing Escaping Output (htmlspecialchars) Custom Error Handler Checkbox, Radio and Select Inputs Sticky Forms PHP Form Complete PHP Date and Time Date Formatting Error Logging HTTP Headers and Redirects Output Buffering Sending Email PHP cURL Consuming REST APIs PHP Generators AJAX PHP AJAX Database AJAX XML AJAX Live Search AJAX Poll AJAX with JSON Security Introduction Password Hashing SQL Injection Prevention XSS Prevention Garbage Collection Project: Simple Blog PHP What is OOP PHP Classes/Objects The $this Keyword Access Array Items Update Array Items Add Array Items Remove Array Items Sorting Arrays Multidimensional Arrays Array Destructuring Array Spread Operator Search in Arrays Merge Arrays PHP Abstract Classes PHP Interfaces Polymorphism PHP Traits PHP Static Methods PHP Static Properties Late Static Binding Magic Methods Object Cloning MySQL Database MySQL Connect MySQL Create DB MySQL Create Table MySQL Insert Data MySQL Get Last ID MySQL Insert Multiple MySQL Prepared MySQL Select Data MySQL Where Debugging PHP Project: Image Upload Gallery $_SESSION $_ENV PHP RegEx RegEx Patterns Heredoc and Nowdoc RegEx Modifiers preg_match preg_replace preg_split PHP RegEx Functions Nullable Types Mixed and Never Types Nullsafe Operator PHP Attributes PHP Fibers Readonly Classes Property Hooks (PHP 8.4) Asymmetric Visibility (PHP 8.4) Reflection API SPL Data Structures Composer PHP Form Handling PDO Transactions PDO Error Handling PHP XML Parsers PHP SimpleXML Parser PHP SimpleXML - Get PHP XML Expat Parser PHP DOM Parser PHP XMLReader PHP XMLWriter AJAX Intro PHP Class Constants Encryption with Sodium Recursive Functions Variable Functions Anonymous Functions Closures Arrow Functions First-class Callable Syntax PHP Arrays Indexed Arrays Associative Arrays Create Arrays Variables Scope Static Variables Variable Variables PHP Echo / Print PHP printf / sprintf PHP Data Types PHP Booleans PHP NULL PHP Strings String Functions array_map / array_filter / array_reduce Array Functions PHP Superglobals $GLOBALS What Is `$_SERVER` ? $_REQUEST $_POST $_GET $_FILES $_COOKIE PHP Type Juggling MySQL Order By MySQL Delete Data MySQL Update Data MySQL Limit Data MySQL Joins with PHP Pagination with PHP PDO Introduction PDO Connect PDO Prepared Statements PDO Fetch Modes
Ask about this post
AI Ask about this post

Ask questions about MySQL Delete Data and get answers drawn from it.

Signed-in readers only.