Introduction: Why PHP MySQL DELETE Matters

In every PHP-MySQL powered application—whether it's a blog, admin panel, or e-commerce dashboard—deleting data is a necessary action. You may need to:

• Remove a user account

• Delete a product from inventory

• Erase outdated log entries

The SQL DELETE statement is the go-to method for removing records. But using it without precautions can be risky. This article walks you through how to securely and efficiently delete data using PHP MySQLi and PDO, with real examples and developer-friendly explanations.

What is the SQL DELETE Statement?

The DELETE command removes one or more rows from a table based on specified conditions.

Basic syntax:

DELETE FROM table_name WHERE condition;

⚠️ Always use a WHERE clause with DELETE to avoid removing all rows unintentionally.

Deleting Data Using MySQLi

✅ Example: Simple MySQLi Delete Query

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

// Check connection
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: " . $conn->error;
}

$conn->close();
?>

Never execute raw delete queries with user-supplied data.

MySQLi Prepared Statement (Secure Delete)

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

$id = $_GET['id']; // e.g. delete.php?id=5

// Use prepared statements to avoid SQL injection
$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $id); // 'i' means integer

if ($stmt->execute()) {
    echo "User deleted successfully.";
} else {
    echo "Error deleting user.";
}

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

✅ Always use prepared statements when working with dynamic input like $_GET or $_POST.

Deleting Data Using PHP PDO

✅ Basic PDO DELETE Query

<?php
$pdo = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "DELETE FROM users WHERE id = 5";

$pdo->exec($sql);
echo "User deleted.";
?>

❌ Not safe with user input—use prepared statements instead.

PDO Prepared Statement (Safe Deletion)

<?php
$pdo = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$id = $_GET['id']; // Be cautious with input

$stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
$stmt->bindParam(':id', $id, PDO::PARAM_INT);

if ($stmt->execute()) {
    echo "User deleted.";
} else {
    echo "Error deleting user.";
}
?>

✅ bindParam() ensures type safety and guards against injection.

Complete Example: Delete via HTML + PHP

<!-- delete_user.php -->
<form method="POST" action="delete_user.php">
    <input type="number" name="user_id" required>
    <button type="submit">Delete User</button>
</form>

<?php
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $id = $_POST['user_id'];

    $stmt = $pdo->prepare("DELETE FROM users WHERE id = :id");
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);

    if ($stmt->execute()) {
        echo "User with ID $id deleted.";
    } else {
        echo "Failed to delete user.";
    }
}
?>

Common DELETE Use Cases

⚠️ Tips & Common Pitfalls

✅ Best Practices

• Always use a WHERE clause to avoid deleting everything.

• Use prepared statements to prevent SQL injection.

• Confirm deletion with the user via UI or prompt before executing.

• Consider soft deletes (e.g., using a deleted_at column) instead of permanent removal.

❌ Common Mistakes

Comparison: MySQLi vs PDO for DELETE Queries

Conclusion: Safely Deleting Data in PHP + MySQL

Deleting data in PHP with MySQL is straightforward—but it’s also powerful and potentially dangerous. You must implement validation, prepared statements, and error handling to ensure safety and reliability.

Actionable Takeaways:

• Always validate input from users before using it in a query.

• Use prepared statements to prevent SQL injection.

• Avoid accidental mass-deletes by including a WHERE clause.

• For sensitive data, consider using soft deletes.