PHP MySQL – Delete Data

Welcome to The Coding College! In this tutorial, we’ll explore how to use PHP to delete data from a MySQL database. Deleting data is an essential task when managing databases, such as removing outdated records, user accounts, or temporary data.

What is the DELETE Statement in SQL?

The DELETE statement is used to remove rows from a table. You can specify which rows to delete using the WHERE clause. If you omit the WHERE clause, all rows in the table will be deleted (use with caution).

Syntax:

DELETE FROM table_name WHERE condition;

Why Use PHP to Delete Data from MySQL?

By combining PHP and MySQL, you can dynamically delete records based on user actions or program logic. Examples include:

• Removing a user account when requested.
• Deleting old or expired data (e.g., old orders or session logs).
• Cleaning up temporary or testing records.

Example: Basic PHP MySQL Query to Delete Data

Scenario:

We have a Users table, and we want to delete a user with a specific ID.

Table Example:

Example 1: Delete Data Using MySQLi (Object-Oriented)

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

// Delete user with id = 2
$sql = "DELETE FROM Users WHERE id = 2";

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

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

Explanation:

• The SQL statement DELETE FROM Users WHERE id = 2 removes the row where the id equals 2.
• The query ensures that only one specific record is deleted.

Example 2: Using Prepared Statements to Delete Data

Prepared statements improve security by preventing SQL injection. If the id is dynamically provided (e.g., from user input), you should always use prepared statements.

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

// User input (e.g., from a form)
$user_id = 3;

// Prepare statement
$sql = "DELETE FROM Users WHERE id = ?";
$stmt = $conn->prepare($sql);

// Bind parameter
$stmt->bind_param("i", $user_id); // "i" indicates the parameter is an integer

// Execute the statement
if ($stmt->execute()) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . $stmt->error;
}

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

Explanation:

• Prepared Statements: Ensure secure handling of user input.
• The bind_param() function binds the $user_id variable as an integer (i) to the query.

Deleting All Records in a Table

If you want to delete all records from a table (e.g., clearing a log table), use the following query:

$sql = "DELETE FROM Users";

Warning:

This will delete all rows in the table. Be cautious when using this command. If you want to keep the table structure but empty the data, consider using:

TRUNCATE TABLE Users;

Using the LIMIT Clause with DELETE

If you want to delete only a limited number of rows, you can use the LIMIT clause. For example:

DELETE FROM Users WHERE username = 'TestUser' LIMIT 1;

This query will delete at most one row where the username is 'TestUser'.

Best Practices for Deleting Data

1. Use the WHERE Clause: Always include a WHERE clause to avoid accidentally deleting all records.
2. Use Prepared Statements: For any user-provided input, use prepared statements to prevent SQL injection.
3. Backup Your Database: Before executing delete operations, especially on production systems, create a backup.
4. Test Queries First: Test your SQL query on a test database to ensure it deletes only the intended records.
5. Log Deletions: Keep a log of deleted records if needed for auditing purposes.

Full Example: Delete User Based on a Form Submission

Here’s a real-world example where a user ID is provided via an HTML form.

HTML Form:

<form action="delete_user.php" method="POST">
    <label for="user_id">Enter User ID to Delete:</label>
    <input type="number" name="user_id" id="user_id" required>
    <button type="submit">Delete User</button>
</form>

PHP Script (delete_user.php):

<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "my_database";

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

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

// Get user ID from POST request
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST["user_id"])) {
    $user_id = intval($_POST["user_id"]);

    // Prepare the delete statement
    $sql = "DELETE FROM Users WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("i", $user_id);

    if ($stmt->execute()) {
        echo "User with ID $user_id has been deleted successfully.";
    } else {
        echo "Error deleting user: " . $stmt->error;
    }

    $stmt->close();
}

$conn->close();
?>

Conclusion

The DELETE statement is a powerful tool for managing database records. With PHP, you can dynamically delete records while ensuring security and accuracy through prepared statements. Remember to always handle deletions carefully, especially on production databases.