• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Delete Data

← Previous Next →

Deleting data from a database is a sensitive operation, as it removes records permanently. MySQL’s DELETE statement allows you to remove one or more rows based on conditions. In PHP applications, combining DELETE with MySQLi or PDO ensures that deletions are controlled, secure, and precise.

This tutorial explains the DELETE statement, how to use it in PHP, best practices, and common pitfalls when deleting data.

What is DELETE Statement?

The DELETE statement removes records from a table. Using it without a WHERE clause will delete all rows, which can be catastrophic. Always double-check your conditions before executing.

Basic Syntax

DELETE FROM table_name WHERE condition;

Example Table: students

id name age email
1 Neha 16 neha@example.com
2 Anika 17 anika@example.com
3 Riya 16 riya@example.com 
DELETE FROM students WHERE name = 'Riya';

This deletes the row where the student’s name is Riya.

Deleting Multiple Rows

You can delete multiple rows using conditions:

DELETE FROM students WHERE age = 16;

This removes all students aged 16.

Using IN Clause

DELETE FROM students WHERE id IN (1, 3);

This deletes the students with IDs 1 and 3.

Deleting All Rows

If you omit the WHERE clause, all rows will be removed:

DELETE FROM students;

This clears the entire table. Use with caution. For large tables, TRUNCATE TABLE students; is faster and resets auto-increment values.

Using DELETE in PHP MySQLi

Procedural Style

<?php
$conn = mysqli_connect("localhost", "root", "", "school_db");
if (!$conn) die("Connection failed: " . mysqli_connect_error());

$sql = "DELETE FROM students WHERE name = 'Anika'";

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

mysqli_close($conn);
?>

Object-Oriented Style

<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

$sql = "DELETE FROM students WHERE age = 16";
if ($conn->query($sql) === TRUE) {
    echo "Records deleted successfully!";
} else {
    echo "Error deleting records: " . $conn->error;
}

$conn->close();
?>

Using PDO for DELETE

PDO allows safe and flexible deletions with prepared statements.

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

    $stmt = $conn->prepare("DELETE FROM students WHERE name = :name");
    $name = "Neha";
    $stmt->bindParam(':name', $name);
    $stmt->execute();

    echo "Record deleted successfully!";
} catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}

$conn = null;
?>

Explanation:

  • :name is a named placeholder for safe deletion.

  • Prepared statements prevent SQL injection when deleting based on user input.

  • execute() performs the deletion safely.

Deleting Multiple Records Dynamically

You can loop through an array of IDs and delete records:

$ids = [2, 3, 4];
$stmt = $conn->prepare("DELETE FROM students WHERE id = :id");

foreach ($ids as $id) {
    $stmt->bindParam(':id', $id);
    $stmt->execute();
}

This approach is useful for bulk deletions in admin panels or batch operations.

Best Practices

  1. Always include a WHERE clause to avoid accidental deletion of all rows.

  2. Use prepared statements for dynamic data.

  3. Double-check the conditions and affected rows before executing.

  4. Use transactions for multi-table deletions to maintain consistency.

  5. Consider soft deletes (a flag column) for important data instead of permanent deletion.

Common Errors

Error Cause Solution
No rows deleted WHERE condition does not match Verify data and condition
Accidental deletion Missing WHERE clause Always include WHERE
SQL syntax error Typo or missing quotes Check syntax carefully
Injection risk Directly using user input Use prepared statements
Foreign key constraint Related table prevents deletion Check constraints or use cascading deletes

Real-World Examples

  1. User Management: Deleting a user account.

  2. E-commerce: Removing discontinued products.

  3. School Management: Deleting graduated students or duplicate entries.

Example:

DELETE FROM orders WHERE order_date < '2024-01-01';

This removes all orders placed before January 1, 2024.

Summary of the Tutorial

  • DELETE removes records from a MySQL table.

  • Always use WHERE clauses unless intentionally clearing the table.

  • Use PHP MySQLi or PDO to implement deletions safely.

  • Prepared statements protect against SQL injection when deleting user-specific data.

  • For bulk operations, consider transactions and validation to maintain integrity.

Properly managing deletions ensures that your application remains secure, efficient, and reliable, preventing accidental data loss.

Practice Questions

  1. Write a SQL query to delete a student named "Riya" from the students table.

  2. Write a PHP MySQLi procedural script to delete all students aged 16.

  3. Write a PHP MySQLi object-oriented script to delete students whose email ends with '@example.com'.

  4. Write a PDO PHP script to delete a student by ID using prepared statements.

  5. Write a SQL query to delete multiple students using the IN clause with IDs 1, 3, and 5.

  6. Explain why it’s dangerous to execute a DELETE statement without a WHERE clause.

  7. Write a PHP script that loops through an array of student IDs and deletes each using PDO prepared statements.

  8. Write a SQL query to delete all orders placed before a specific date.

  9. Describe a real-world scenario where using soft deletes (a flag column) is better than permanent deletion.

  10. Write a PHP script to check the number of rows affected after a deletion and display a confirmation message.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

PHP

online coding class codepractice

PHP Basics

PHP Data

Control Flow

PHP Functions

PHP Arrays

PHP Forms

PHP Advanced

Cookies & Sessions

Filters & JSON

PHP Exceptions

PHP OOP

MySQL with PHP

PHP and XML

AJAX with PHP

codepractice logo
Login
Go Back Top