PHP MySQL Delete Data

To remove records from a MySQL table, you use the DELETE FROM SQL statement. Like the update command, deleting is a powerful action that must be used carefully to avoid accidental data loss.

The SQL Statement

The DELETE FROM statement specifies the table and a WHERE clause to identify which record(s) to remove.

DELETE FROM Users 
WHERE id = 3;

1. MySQLi (Object-Oriented)

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

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

    // SQL to delete a record
    $sql = "DELETE FROM Users WHERE id=3";

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

    $conn->close();
?>

2. MySQLi (Procedural)

<?php
    $conn = mysqli_connect($servername, $username, $password, $dbname);

    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }

    $sql = "DELETE FROM Users WHERE id=3";

    if (mysqli_query($conn, $sql)) {
        echo "Record deleted successfully";
    } else {
        echo "Error deleting record: " . mysqli_error($conn);
    }

    mysqli_close($conn);
?>

3. PDO

<?php
    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $sql = "DELETE FROM Users WHERE id=3";

        // use exec() because no results are returned
        $conn->exec($sql);
        echo "Record deleted successfully";
    } catch(PDOException $e) {
        echo $sql . "<br>" . $e->getMessage();
    }

    $conn = null;
?>

Confirming Deletion

Just like with updates, you can check how many rows were actually removed:

• MySQLi (OO): $conn->affected_rows;
• MySQLi (Proc): mysqli_affected_rows($conn);
• PDO: $stmt->rowCount(); (if using a prepared statement).

Best Practices

• Always use a WHERE clause unless you truly want to empty the table.
• Confirm before delete: In a real web application, always ask the user for confirmation (e.g., a JavaScript popup) before executing a delete script.
• Use Prepared Statements: Especially if the ID comes from a URL parameter (like delete.php?id=3), to prevent SQL injection.
• Soft Deletes: Consider using a "deleted_at" column instead of actually removing the row, so you can recover data if needed.

Summary

• DELETE FROM statement removes rows from a table.
• The WHERE clause identifies which rows to delete.
• Omitting WHERE deletes all data in the table.
• Checking affected rows helps verify the operation's impact.