Delete Data from Database in PHP MySQL – Complete Guide

Introduction

When developers need the ability to remove unwanted or outdated information from the database. This process is known as deleting data from the database.

To remove records from a database table, developers use the DELETE SQL statement along with PHP database functions such as mysqli_query().

Deleting data must be handled carefully because once a record is removed, it cannot be recovered unless a backup exists.

What is Delete Data in PHP MySQL?

Deleting data in PHP MySQL refers to the process of removing one or more records from a database table using SQL queries executed through PHP.

The SQL statement used for deleting records is called the DELETE statement.

This statement removes rows from a table based on a specified condition.

Example SQL query:


DELETE FROM users WHERE id = 5;

Explanation:

  • DELETE FROM specifies that records should be removed.
  • users is the table name.
  • WHERE id = 5 specifies the condition for the record to be deleted.

Note: Without a condition, the query will remove all records from the table, which can cause serious data loss.

PHP executes the DELETE query using database functions after establishing a connection with MySQL.

Why It Is Used

Deleting data from a database is necessary in many real-world applications.

1. Removing Inactive Users

Websites may delete accounts that have been inactive for a long time.

2. Deleting Blog Posts

Blog administrators may remove outdated or incorrect articles.

3. Removing Products

E-commerce websites delete products that are no longer available.

4. Managing Comments

Admins may delete spam or inappropriate comments.

5. Cleaning Old Data

Applications may remove outdated records to improve performance and reduce storage usage.

By deleting unnecessary records, developers can maintain a clean and efficient database.

Syntax

The basic SQL syntax for deleting records is:


DELETE FROM table_name WHERE condition;

Explanation:

  • table_name – the name of the database table
  • WHERE – specifies which records should be removed

If the WHERE clause is not used, all rows in the table will be deleted.

Example in PHP:


$sql = "DELETE FROM users WHERE id=3";
mysqli_query($conn, $sql);

This query deletes the user whose id is 3.

Example

Below is a simple PHP example that deletes a record from the database.


$servername = "localhost";
$username = "root";
$password = "";
$database = "testdb";

$conn = mysqli_connect($servername, $username, $password, $database);

if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
}

$sql = "DELETE FROM users WHERE id=2";

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

Explanation:

  1. The script connects to the MySQL database.
  2. The DELETE SQL query is written.
  3. mysqli_query() executes the query.
  4. If the query succeeds, a success message is displayed.
  5. If it fails, an error message is shown.

Real-Life Example

A common real-life example of deleting data is in an admin dashboard.

For instance, consider a blog management system where an administrator manages articles.

Each article stored in the database may contain fields such as:

  • id
  • title
  • content
  • author
  • publish_date

If the administrator decides to remove an article, the system performs the following steps:

  1. The admin clicks the Delete button for a specific article.
  2. The article ID is sent to a PHP script.
  3. PHP executes a DELETE query using that ID.
  4. The corresponding record is removed from the database.

Example query:


DELETE FROM posts WHERE id = 10;

After executing this query, the article with ID 10 will no longer exist in the database.

This type of delete functionality is used in many applications such as:

  • Blog management systems
  • Online stores
  • User management systems
  • Comment moderation systems

Common Mistakes

Beginners often make mistakes while deleting database records.

1. Forgetting the WHERE Clause

A query without a WHERE clause will delete all records.

Example of a dangerous query:


DELETE FROM users;

This will remove every record from the table.

2. Deleting the Wrong Record

If the condition is incorrect, the wrong data may be deleted.

3. Not Creating Database Backups

Developers should create regular backups to recover data if accidental deletion occurs.

4. Not Confirming Before Deleting

Many applications add a confirmation message like:

“Are you sure you want to delete this record?”

5. Ignoring Security

Developers should validate user input to prevent malicious actions.

Conclusion

Deleting data from a MySQL database using PHP is an important database operation that allows developers to remove unwanted or outdated records. By using the SQL DELETE statement and PHP functions like mysqli_query(), developers can easily remove specific records from database tables.

However, developers must use the DELETE statement carefully, especially with the WHERE clause, to avoid accidental deletion of important data. Proper validation, confirmations, and backups help ensure safe database management.

Related PHP Tutorials