How to Delete Data from MySQL Using PHP: Simple Guide

To delete data from MySQL using PHP, use the DELETE FROM SQL statement inside a PHP script with a MySQL connection. Prepare and execute the query with conditions to specify which rows to delete, ensuring safe and effective data removal.

Syntax

The basic syntax to delete data from a MySQL table using PHP is:

• DELETE FROM table_name: specifies the table to delete from.
• WHERE condition: specifies which rows to delete (important to avoid deleting all rows).

In PHP, you connect to MySQL, then run this SQL command using functions like mysqli_query() or prepared statements.

php

<?php
// SQL syntax example
$sql = "DELETE FROM table_name WHERE condition";
?>

Example

This example shows how to delete a user with a specific ID from a MySQL database using PHP's mysqli extension.

php

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "testdb";

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

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

// ID of the user to delete
$user_id = 3;

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

// Execute and check
if ($stmt->execute()) {
    echo "User deleted successfully.";
} else {
    echo "Error deleting user: " . $conn->error;
}

$stmt->close();
$conn->close();
?>

Output

User deleted successfully.

Common Pitfalls

Common mistakes when deleting data from MySQL using PHP include:

• Forgetting the WHERE clause, which deletes all rows in the table.
• Not using prepared statements, which can lead to SQL injection attacks.
• Not checking if the database connection or query execution succeeded.

Always validate inputs and use prepared statements to keep your database safe.

php

<?php
// Wrong: Deletes all rows unintentionally
$sql = "DELETE FROM users";
$conn->query($sql);

// Right: Deletes only the user with id 5
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $conn->prepare($sql);
$id = 5;
$stmt->bind_param("i", $id);
$stmt->execute();
?>

Quick Reference

Remember these tips when deleting data from MySQL using PHP:

• Always use WHERE to target specific rows.
• Use prepared statements to prevent SQL injection.
• Check connection and query success to handle errors.
• Close statements and connections after use.

Key Takeaways

Always include a WHERE clause in DELETE statements to avoid removing all data.

Use prepared statements in PHP to safely pass variables to SQL queries.

Check for successful database connection and query execution to handle errors.

Close your database connections and statements to free resources.

Validate and sanitize inputs before using them in SQL queries.