MySQL Delete Data

PHP - Delete Data from MySQL/MariaDB

Delete Data From a Database Table Using MySQLi and PDO

The SQL DELETE statement is used to remove one or more records (rows) from a database table.

The basic syntax is:

DELETE FROM table_name
WHERE condition;

⚠️	Crucial: The WHERE clause specifies which record(s) to delete. If you omit the WHERE clause, the DELETE statement will remove all records from the table! Always double-check your WHERE clause before executing a DELETE query, especially in production environments.

The condition typically involves comparing a column’s value (e.g., id = 3, status = 'inactive').

To learn more about SQL syntax, refer to dedicated SQL tutorials.

Let’s assume we have a users table with the following data:

+----+------------+-----------+-----------------------+---------------------+
| id | first_name | last_name | email                 | registration_date   |
+----+------------+-----------+-----------------------+---------------------+
| 1  | John       | Doe       | john.doe@example.com  | 2023-10-26 10:15:00 |
| 2  | Mary       | Moe       | mary.moe@example.com  | 2023-10-27 11:30:00 |
| 3  | Peter      | Jones     | peter.j@example.com   | 2023-10-28 09:05:21 |
+----+------------+-----------+-----------------------+---------------------+

The following examples demonstrate how to delete the user with id=3 from the users table using both MySQLi and PDO, employing prepared statements for security.

Example (MySQLi Object-Oriented Style with Prepared Statement):

<?php
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "my_database";   // Replace with your DB name
$userIdToDelete = 3;

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

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error); // Consider more robust error handling in production
}

// SQL statement with a placeholder (?)
$sql = "DELETE FROM users WHERE id = ?";

// Prepare statement
$stmt = $conn->prepare($sql);

if ($stmt === false) {
    die("Error preparing statement: " . $conn->error); // Basic error check
}

// Bind parameters (s = string, i = integer, d = double, b = blob)
$stmt->bind_param("i", $userIdToDelete);

// Execute the statement
if ($stmt->execute()) {
    // Check how many rows were affected
    if ($stmt->affected_rows > 0) {
        echo "Record with ID $userIdToDelete deleted successfully.";
    } else {
        echo "No record found with ID $userIdToDelete or deletion failed.";
    }
} else {
    echo "Error executing statement: " . $stmt->error;
}

// Close statement and connection
$stmt->close();
$conn->close();
?>

Example (PDO with Prepared Statement):

<?php
$servername = "localhost";
$username = "your_username"; // Replace with your DB username
$password = "your_password"; // Replace with your DB password
$dbname = "my_database";   // Replace with your DB name
$userIdToDelete = 3;

try {
    // Create PDO connection
    $conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8mb4", $username, $password);

    // Set the PDO error mode to exception for better error handling
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // SQL statement with a named placeholder (:id)
    $sql = "DELETE FROM users WHERE id = :id";

    // Prepare statement
    $stmt = $conn->prepare($sql);

    // Bind parameters (named placeholders)
    $stmt->bindParam(':id', $userIdToDelete, PDO::PARAM_INT);

    // Execute the statement
    $stmt->execute();

    // Check how many rows were affected
    $rowCount = $stmt->rowCount();
    if ($rowCount > 0) {
        echo "Record with ID $userIdToDelete deleted successfully ($rowCount row affected).";
    } else {
        echo "No record found with ID $userIdToDelete or deletion failed.";
    }

} catch(PDOException $e) {
    // Catch and display errors
    echo "Error: " . $e->getMessage();
}

// Close connection (optional for PDO, happens automatically when script ends)
$conn = null;
?>

Why Prepared Statements? Using ? (MySQLi) or named placeholders like :id (PDO) and then binding the actual values prevents SQL injection attacks. The database driver handles the proper escaping of the input values, making your query secure.

After successful execution of either script, the users table would look like this:

+----+------------+-----------+-----------------------+---------------------+
| id | first_name | last_name | email                 | registration_date   |
+----+------------+-----------+-----------------------+---------------------+
| 1  | John       | Doe       | john.doe@example.com  | 2023-10-26 10:15:00 |
| 2  | Mary       | Moe       | mary.moe@example.com  | 2023-10-27 11:30:00 |
+----+------------+-----------+-----------------------+---------------------+