Delete Data

Deleting Records Safely (DELETE FROM with WHERE Conditions)

The DELETE FROM statement removes matching rows from a database table. Deletion queries MUST always include a WHERE condition to prevent accidentally truncating all records in the table.

Delete Safety Workflow

flowchart TD
    A["User Triggers Delete Action"] --> B{"Has WHERE Condition?"}
    B -- Missing WHERE --> C["SECURITY FATAL: Deletes ALL Records in Table!"]
    B -- Present WHERE --> D["Prepare SQL: 'DELETE FROM users WHERE id = :id'"]
    D --> E["$stmt->execute(['id' => $id])"]
    E --> F["Check $stmt->rowCount() to Confirm Deleted Count"]

Practical Code Example

<?php
declare(strict_types=1);

function deleteUserAccount(PDO $pdo, int $userId): bool
{
    // Prepared DELETE query with mandatory WHERE clause
    $sql = "DELETE FROM users WHERE id = :id AND status = :status";
    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        'id' => $userId,
        'status' => 'inactive'
    ]);

    // rowCount() returns number of rows affected by DELETE statement
    $deletedRows = $stmt->rowCount();

    if ($deletedRows > 0) {
        echo "Successfully deleted $deletedRows user account(s).
";
        return true;
    } else {
        echo "No matching inactive user account found to delete.
";
        return false;
    }
}

try {
    $pdo = new PDO("mysql:host=127.0.0.1;dbname=ecommerce_db;charset=utf8mb4", 'root', 'secret_password', [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);

    deleteUserAccount($pdo, 42);

} catch (PDOException $e) {
    die("Delete Operation Error: " . $e->getMessage());
}

Best Practices

• NEVER Execute DELETE FROM table Without WHERE: Omitting WHERE wipes out all rows in the target table permanently.
• Use rowCount() to Verify Deletion: rowCount() reports how many records were actually removed by the operation.
• Consider Soft Deletes (deleted_at): Instead of hard-deleting records, set a deleted_at timestamp column to retain data audit history.

Self-Check Challenge

What method on PDOStatement returns the number of rows deleted by a DELETE query? ($stmt->rowCount())