Delete Data

Deleting Data from MySQL in PHP Safely

Deleting records in PHP uses a parameterized DELETE FROM statement executed via PDO—always paired with a strict WHERE condition and authorization check. Because a single missing WHERE clause wipes an entire table instantly, production apps enforce strict safeguards around delete operations.

Parameterized DELETE with Ownership Verification

Always bind the target row ID and, for user-owned records, include AND user_id = :user_id directly in the WHERE clause so one customer can never delete another customer's record by guessing an ID.

<?php
declare(strict_types=1);

function deleteSavedAddress(PDO $pdo, int $addressId, int $ownerUserId): bool
{
    $stmt = $pdo->prepare(
        'DELETE FROM user_addresses WHERE id = :id AND user_id = :user_id LIMIT 1'
    );
    $stmt->execute([
        ':id' => $addressId,
        ':user_id' => $ownerUserId,
    ]);

    return $stmt->rowCount() === 1;
}

Checking $stmt->rowCount() === 1 tells you accurately whether a matching record was actually deleted (true) or did not exist / belonged to someone else (false).

Soft Deletes vs Hard Deletes

For invoices, orders, or user-generated content tied to financial history, prefer a soft delete (UPDATE orders SET deleted_at = NOW() WHERE id = :id) instead of a hard DELETE. Soft deletes keep audit trails intact and make accidental deletions easy to recover, while hard deletes work well for expired tokens, draft items, or GDPR erasure workflows.

Common Mistakes to Avoid

• Deleting via GET links (<a href="delete.php?id=5">): Browser pre-fetchers or crawlers visiting links—or CSRF image tags—can trigger GET deletes unintentionally. Always require a POST or DELETE request protected by a CSRF token.
• Omitting a WHERE clause: Running DELETE FROM users without WHERE erases every row in the table. Add LIMIT 1 for single-record deletes as an extra guardrail.
• Ignoring foreign key errors: Attempting to hard-delete a parent user referenced by a restrictive foreign key in invoices throws a PDOException (23000). Decide deliberately between ON DELETE CASCADE, soft deletes, or archiving.

Frequently Asked Questions

What is the difference between DELETE and TRUNCATE TABLE?

DELETE removes selected rows inside a transaction and can be rolled back. TRUNCATE TABLE drops and recreates the entire table, resets AUTO_INCREMENT, and cannot be rolled back.

Why did $stmt->rowCount() return 0 on a DELETE?

No row matched the WHERE conditions (either the ID did not exist or the ownership user_id check did not match).

Should delete buttons in HTML use <a> links or <form method="POST">?

Always use a <form method="POST"> (with a CSRF token and confirmation prompt) so only intentional user actions mutate data.