MySQL Delete Data

Remove records from your MySQL database safely

🗑️ What is DELETE?

The DELETE statement removes records from a database table. Always use a WHERE clause to specify which rows to delete, otherwise all records will be removed permanently from the table. 


<?php
// Delete specific record
$sql = "DELETE FROM users WHERE id = 5";
$conn->query($sql);
?>

Key Concepts

⚠️

WHERE Clause

Specify which rows to delete 

// Target specific rows
DELETE FROM users WHERE id = 1
🔒

Safety First

Always confirm before deleting 

// Check affected rows
$conn->affected_rows
🛡️

Prepared Statements

Use for secure deletion 

// Safe deletion
$stmt->prepare("DELETE...")
💾

Backup Data

Keep backups before deleting 

// Archive first
SELECT * INTO backup_table

🔹 Delete Single Record

Remove a specific record by targeting it with a unique identifier. Always use WHERE to avoid accidentally deleting all records in the table. 

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Delete user with ID 5
$sql = "DELETE FROM users WHERE id = 5";

if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $conn->error;
}

$conn->close();
?>

Output:

Record deleted successfully

🔹 Delete with Condition

Delete multiple records that match specific criteria. Use comparison operators to target rows based on column values. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

// Delete all users younger than 18
$sql = "DELETE FROM users WHERE age < 18";

if ($conn->query($sql) === TRUE) {
    $deleted_count = $conn->affected_rows;
    echo "$deleted_count record(s) deleted successfully";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

3 record(s) deleted successfully

🔹 Delete with Prepared Statement

Use prepared statements for secure deletion, especially when the delete criteria comes from user input. This prevents SQL injection attacks. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

// Prepare delete statement
$stmt = $conn->prepare("DELETE FROM users WHERE email = ?");
$stmt->bind_param("s", $email);

// Set email and execute
$email = "[email protected]";
$stmt->execute();

if ($stmt->affected_rows > 0) {
    echo "User with email $email deleted successfully";
} else {
    echo "No user found with that email";
}

$stmt->close();
$conn->close();
?>

Output:

User with email [email protected] deleted successfully

🔹 Delete Multiple Records

Remove several records at once using logical operators. Combine conditions with AND/OR to precisely target the rows you want to delete. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

// Delete users from specific cities
$sql = "DELETE FROM users WHERE city = 'Boston' OR city = 'Chicago'";

if ($conn->query($sql) === TRUE) {
    $count = $conn->affected_rows;
    echo "Deleted $count users from Boston and Chicago";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Deleted 5 users from Boston and Chicago

🔹 Delete with Confirmation

Implement a safety check before deletion to prevent accidental data loss. This pattern is essential for production applications. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

$user_id = 10;

// First, check if record exists
$check_sql = "SELECT name FROM users WHERE id = $user_id";
$result = $conn->query($check_sql);

if ($result->num_rows > 0) {
    $row = $result->fetch_assoc();
    $name = $row['name'];
    
    // Now delete the record
    $delete_sql = "DELETE FROM users WHERE id = $user_id";
    
    if ($conn->query($delete_sql) === TRUE) {
        echo "User '$name' (ID: $user_id) has been deleted";
    } else {
        echo "Error deleting record: " . $conn->error;
    }
} else {
    echo "No user found with ID $user_id";
}

$conn->close();
?>

Output:

User 'John Doe' (ID: 10) has been deleted

🔹 Delete All Records (TRUNCATE)

Remove all records from a table. Use DELETE without WHERE or TRUNCATE TABLE. TRUNCATE is faster but cannot be rolled back.

⚠️ Warning

This will permanently delete ALL records from the table. Use with extreme caution! 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

// Method 1: DELETE (can be rolled back in transactions)
$sql = "DELETE FROM temp_users";

// Method 2: TRUNCATE (faster, resets auto-increment)
// $sql = "TRUNCATE TABLE temp_users";

if ($conn->query($sql) === TRUE) {
    echo "All records deleted from temp_users table";
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

All records deleted from temp_users table

🔹 Check Affected Rows

Verify how many rows were deleted using affected_rows property. This helps confirm the deletion was successful and shows the impact. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

$sql = "DELETE FROM users WHERE age > 60";

if ($conn->query($sql) === TRUE) {
    $deleted = $conn->affected_rows;
    
    if ($deleted > 0) {
        echo "Successfully deleted $deleted user(s)";
    } else {
        echo "No users matched the criteria";
    }
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

Output:

Successfully deleted 2 user(s)

⚠️ Critical Safety Tips

  • ALWAYS use WHERE clause - Without it, all records will be deleted!
  • Test with SELECT first - Run SELECT with same WHERE to see what will be deleted
  • Backup your data - Create backups before bulk deletions
  • Use transactions - Wrap deletes in transactions for critical data
  • Implement soft deletes - Consider marking records as deleted instead of removing them

🔹 Soft Delete Pattern

Instead of permanently deleting records, mark them as deleted. This allows data recovery and maintains referential integrity. 

<?php
$conn = new mysqli("localhost", "root", "", "myDB");

// Soft delete: Update deleted_at timestamp instead of removing record
$sql = "UPDATE users SET deleted_at = NOW() WHERE id = 5";

if ($conn->query($sql) === TRUE) {
    echo "User marked as deleted (soft delete)";
} else {
    echo "Error: " . $conn->error;
}

// To retrieve only active users:
// SELECT * FROM users WHERE deleted_at IS NULL

$conn->close();
?>

Output:

User marked as deleted (soft delete)

💡 Best Practices

  • Always use WHERE clause to avoid deleting all records
  • Test with SELECT before running DELETE
  • Use prepared statements for user-provided criteria
  • Check affected_rows to verify deletion
  • Consider soft deletes for important data
  • Implement proper user permissions for delete operations
  • Log all deletion activities for audit trails

🧠 Test Your Knowledge

What happens if you run DELETE without a WHERE clause?