PHP PDO DELETE - Deleting Data

Deleting Data (DELETE)

The DELETE command permanently removes records from a table. Since this is a critical operation, in PDO we always use prepared statements with parameters (e.g., :id) to prevent potential abuse.

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "pdo_database";

try {
    $connection = new PDO("mysql:host=$server;dbname=$database;charset=utf8mb4", $username, $password);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // STEP 1: ID for deletion
    $id_to_delete = 6;

    // STEP 2: Executing the deletion
    $sql = "DELETE FROM users WHERE id = :id";
    $statement = $connection->prepare($sql);
    $statement->execute(['id' => $id_to_delete]);

    echo "<div style='font-family: monospace; color: #2e7d32; margin-bottom: 12px;'>User with ID 6 was successfully deleted!</div>";

    // STEP 3: Displaying the refreshed table (without ID 6)
    $query = $connection->query("SELECT id, first_name, last_name, email FROM users");
    $results = $query->fetchAll(PDO::FETCH_ASSOC);

    echo "<table border='1' style='border-collapse: collapse; width: 100%; font-family: monospace;'>";
    echo "<tr><th>ID</th><th>First Name</th><th>Last Name</th><th>Email</th></tr>";

    foreach ($results as $row) {
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['first_name'] . "</td>";
        echo "<td>" . $row['last_name'] . "</td>";
        echo "<td>" . $row['email'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";

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

$connection = null;
?>

RESULT

The result will be displayed here...

To delete, we use the prepare() method followed by execute(). The method confirms whether the row was successfully removed from the database.

Always use a WHERE clause unless you intend to delete all data.

Prepared statements protect against SQL injection.

The exec() method would also work, but prepare() is a safer choice for variables.