How to Delete Records in MySQL Using PHP

In this tutorial, we will learn how to delete records in MySQL using PHP. Deleting data means removing records from a database table. It is a common task when working with PHP and MySQL databases because you often need to remove unnecessary, duplicate, or outdated records from a table.

When working with PHP, we use the MySQL DELETE statement to delete one or more records from a database table. So, let’s understand what DELETE statement is and how to use it with PHP and MySQL using practical examples.

What is DELETE Statement in MySQL?

The DELETE statement in MySQL removes one or more records from a table in a database. It allows you to delete specific rows based on a specified condition.

Usually, we use the DELETE statement with the WHERE clause to specify which records should be removed from the table. The WHERE clause helps identify and target specific rows that meet the given condition.

If you do not use the WHERE clause, the DELETE statement removes all records from the table, but the table structure remains unchanged.

Basic Syntax of DELETE Statement in MySQL

The basic syntax to delete a specific record from a table using the MySQL DELETE statement is:

DELETE FROM table_name WHERE condition;

In the above syntax:

• DELETE FROM is a command used to remove one or more records from a table.
• table_name specifies the name of the table from which the records will be deleted.
• WHERE condition specifies which record or records should be deleted from the table.

Example:

DELETE FROM users;

This will delete all records from the users table.
[blocksy-content-block id=”12371″]

Database Table Example

Suppose we have a table called students.

Now we want to delete the record with id = 2.

Requirements Before Deleting Records from a Database Table

Before deleting records from a MySQL table using PHP, make sure you meet the following requirements:

• You must have a MySQL database created.
• You must establish a successful connection between PHP and the MySQL database.
• The table must exist in the selected database.
• The table should contain records that you want to delete.
• The database user must have the required permissions to access and modify the table.

Make sure you meet all these basic requirements before attempting to delete records from a MySQL table using PHP.

Ways to Delete Records in MySQL Using PHP

PHP provides three main ways to delete records from a MySQL database table:

• MySQLi Procedural Method
• MySQLi Object-Oriented Method
• PDO (PHP Data Objects) Method

All these methods execute the same MySQL DELETE statement, but the syntax and approach in PHP are different. Let us understand each method one by one.

Delete Records Using MySQLi and PHP

Inside the htdocs folder, create a new file with the name delete_record.php and paste this code into it.
[blocksy-content-block id=”12121″]

Example 1: Procedural Method

<?php
// Include the database connection file.
require_once "connect.php";

// Write an SQL query to delete a record from a database table.
$sql = "DELETE FROM students WHERE id=2";

if (mysqli_query($conn, $sql)) {
     echo "Record deleted successfully.";
} else {
     echo "Error deleting record: " . mysqli_error($conn);
}
// Close connection.
mysqli_close($conn);?>

Now save this file using Ctrl + S.

Now open the browser and go to http://localhost/delete_record.php. If the records exist in the table, you will see the following output in your browser.

Output:

Record deleted successfully.

Explanation of PHP Code

• The line require_once “connect.php”; loads the database connection file. This file connects PHP to the MySQL database.
• Without this connection, PHP cannot access data from the database.
• Next, we create an SQL DELETE statement and store it in a variable named $sql.
• The SQL DELETE statement removes a specific record from the students table.
• The WHERE id=2 ensures that only the record with id 2 is removed from the table.
• The mysqli_query() function executes the SQL query. This function takes two parameters:
  • $conn → database connection variable.
  • $sql → SQL query
• The if condition checks whether the query was executed successfully.
• If the query runs successfully, a message will display on the browser.
• If the query fails, the code inside the else block will execute and display the error message returned by MySQL.
• Finally, the function mysqli_close($conn); closes the database connection to free up server resources.

Example 2: MySQLi Object-oriented

<?php
// Include the database connection file.
require_once "connect.php";

// SQL query to delete a record from the students table.
$sql = "DELETE FROM students WHERE id = 3";

// Execute the query.
if ($conn->query($sql) === TRUE) {
     echo "Record deleted successfully";
} else {
     echo "Error deleting record: " . $conn->error;
}
// Close the database connection.
$conn->close();
?>

Delete Records Using PDO Method

Inside the htdocs folder, create a new file with the name delete_record_pdo.php and paste this code into it.

Example 3: Delete Data with PDO Method

<?php
// Import database connection
require_once "config.php";

try {
 // SQL query to delete a record from the students table.
    $sql = "DELETE FROM students WHERE id = 1";
 // Execute the query
    $conn->exec($sql);
    echo "Record deleted successfully";
}
catch(PDOException $e) {
    echo "Error deleting record: " . $e->getMessage();
}
// Close the connection
$conn = null;
?>

Explanation of PHP Code

• The line require_once “config.php”; includes the database connection file. The file usually contains the PDO database connection code.
• The try block contains the code that may produce an error. It is used with PDO exception handling to manage database errors.
• The SQL query deletes the record with id = 2 from the students table.
• The exec() method executes the SQL query. We commonly use it with INSERT, UPDATE, and DELETE queries.
• If the record is deleted successfully from the table, a success message is displayed in the browser.
• The catch block handles any database errors. If the query fails, the error message will be displayed.
• The line $e->getMessage() returns the actual error message from the database.
• The line $conn = null; closes the database connection.

Conclusion

In MySQL, we perform deletion operations using the DELETE statement, which removes records from a database table. PHP provides three main methods to delete data from a MySQL database:

• MySQLi Procedural Method
• MySQLi Object-Oriented Method
• PDO (PHP Data Objects) Method

All these methods use the same MySQL DELETE statement to remove records from a table. We hope this tutorial helped you understand how to delete records from a MySQL database using PHP.