Removing data from a MySQL database is a crucial operation in many PHP applications. This guide will walk you through the process of deleting records using PHP and MySQL.

Basic DELETE Statement

The fundamental syntax for deleting data from a MySQL table uses the DELETE statement. Here's a simple example:


$sql = "DELETE FROM users WHERE id = 5";
$conn->query($sql);

This query removes the user with an ID of 5 from the 'users' table. However, directly inserting values into SQL queries can lead to security vulnerabilities.

Using Prepared Statements

For enhanced security and to prevent SQL injection attacks, it's recommended to use prepared statements. Here's how you can delete data safely:


$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $userId);
$userId = 5;
$stmt->execute();

This method separates the SQL logic from the data, making your queries more secure and efficient.

Deleting Multiple Records

To delete multiple records that meet certain criteria, you can use a more complex WHERE clause:


$stmt = $conn->prepare("DELETE FROM orders WHERE order_date < ? AND status = ?");
$stmt->bind_param("ss", $date, $status);
$date = "2023-01-01";
$status = "cancelled";
$stmt->execute();

This query removes all cancelled orders placed before January 1, 2023.

Best Practices

Always use prepared statements to prevent SQL injection.
Implement proper error handling to catch and log any issues during deletion.
Consider using transactions for complex delete operations involving multiple tables.
Backup your data regularly to prevent accidental data loss.
Use LIMIT clause when deleting large amounts of data to avoid overloading the server.

Checking Affected Rows

After executing a DELETE query, you can check how many rows were affected:


$stmt->execute();
$affectedRows = $stmt->affected_rows;
echo "Deleted $affectedRows record(s).";

This information can be useful for confirming the operation's success or providing feedback to users.

Soft Deletes

In some cases, you might prefer to implement "soft deletes" instead of permanently removing data. This involves adding a 'deleted_at' column to your table and updating it instead of deleting the record:


$stmt = $conn->prepare("UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = ?");
$stmt->bind_param("i", $userId);
$userId = 5;
$stmt->execute();

This approach allows for data recovery and maintains referential integrity in complex database structures.

Conclusion

Deleting data from MySQL using PHP is a straightforward process, but it requires careful consideration of security and data integrity. By using prepared statements and following best practices, you can ensure that your delete operations are both safe and efficient.

For more information on working with MySQL in PHP, check out our guides on inserting data and updating records.