How to Delete Data in PHP MySQL

In this tutorials, we are going to explain the PHP MySQL Delete Data process with a simple, unique guide. By the end of this, you will feel confident about handling deletion tasks in your database.

Working with databases is an adventure. Whether you are building a dynamic website or managing user records, one thing is certain: sometimes, you need to delete data.

Let’s move on to the following section to see how to delete data using PHP MySQL.

PHP MySQL Delete Query

Before we jump into the code, let’s clarify why deleting data is such an important operation.

Here are some reasons to do that:

  • Removing outdated or incorrect records.
  • Cleaning up test or duplicate entries.
  • Complying with user data deletion requests.

To delete data, you need to execute a DELETE query. The syntax looks like this:

DELETE FROM table_name WHERE condition;

DELETE FROM table_name WHERE condition;

The WHERE clause ensures you only delete the rows you intend to. Without it, your entire table could be wiped out.

Let’s move on to the following section to see how to achieve that using MySQLi in PHP.

Delete a Record from a Database Table Using MySQLi in PHP

Let’s translate that SQL query into PHP. Below is the full script to delete discontinued products (those with stock = 0).

$dbServerName = 'localhost';
$dbUsername = 'unique_admin';
$dbPassword = 'secure_pass123';
$dbName = 'online_store';

$conne = new mysqli($dbServerName, $dbUsername, $dbPassword, $dbName);


if ($conne->connect_error) {
    die("Connection failed: " . $conne->connect_error);
}


$productCondition = 0;  
$deleteQuery = "DELETE FROM products WHERE stock = ?";


$stmt = $conne->prepare($deleteQuery);
$stmt->bind_param("i", $productCondition);

if ($stmt->execute()) {
    echo "Products with no stock have been successfully deleted.";
} else {
    echo "Error deleting records: " . $conne->error;
}


$stmt->close();
$conne->close();

We started the script by initializing the database connection using MySQLi and prepared the delete query, which takes a parameter (?). This ensures the use of prepared statements, protecting against SQL injection. We then used bind_param() to secure the query with the provided parameter. In the final process, we checked if the query executed successfully; if it did, a success message is displayed; otherwise, an error message is shown.

Let’s move on to the below section to see how we can delete the records from database using PDO.

Delete a Record from a Database Table Using PDO in PHP

Now, let us write a PHP script to delete inactive users (is_active = 0) from this table.

$hostName = 'localhost';
$dbUser = 'unique_user_pdo';
$dbPass = 'strong_password_2024';
$dbName = 'app_database';

try {

    $database = new PDO("mysql:host=$hostName;dbname=$dbName", $dbUser, $dbPass);
    $database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


    $deleteQuery = "DELETE FROM users WHERE is_active = :inactiveStatus";


    $deleteStatement = $database->prepare($deleteQuery);


    $inactiveStatus = 0; 
    $deleteStatement->bindParam(':inactiveStatus', $inactiveStatus, PDO::PARAM_INT);


    if ($deleteStatement->execute()) {
        echo "Inactive users have been successfully deleted.";
    } else {
        echo "Failed to delete inactive users.";
    }
} catch (PDOException $exception) {

    echo "Error: " . $exception->getMessage();
}


$database = null;

Database Connection:

  • The PDO object is initialized with the hostname, database name, username, and password.
  • PDO::ATTR_ERRMODE ensures exceptions are thrown for database errors.

Delete Query:

  • The DELETE statement uses a named placeholder :inactiveStatus for the condition.

Parameter Binding:

  • The bindParam method assigns the value of $inactiveStatus to the placeholder, ensuring secure data handling.

Execution:

  • The execute method runs the prepared statement. Success or failure messages are displayed based on the result.

Error Handling:

  • Any issues are caught and displayed using PDOException.

Closing Connection:

  • While PDO automatically closes the connection, setting $database = null explicitly releases resources.

Let’s summarize it.

Wrapping Up

In this guide, you learned how to perform deletion tasks in PHP using both MySQLi and PDO.

We started with the basics of crafting a DELETE query and knew the importance of the WHERE clause to avoid unintended data loss. Then, we explained that by examples—first with MySQLi, showcasing prepared statements and parameter binding to secure your database operations. Afterward, we transitioned to PDO, highlighting its error-handling capabilities.

Similar Reads

How to Select Data Using PHP and MySQL?

In this article, You will understand everything you need to know about how to use PHP to select data from…

How to Create Table in MySQL with PHP?

One of the first tasks you will likely encounter is creating a table in databases. Tables are the main part…

PHP Escape Characters: How to Escape Special Characters

Escape characters appeared in PHP because some symbols in strings serve special purposes. For example, a quote can end a…

How to Use PHP count() to Get Array Length

PHP developers may be need to determine the number of elements in an array. The count() function in PHP makes…

PHP array_push Function: How it Works with Examples

PHP array_push lets you add one or more values to the end of an array. It appeared to make adding…

PHP array_map Function: How to Transform Arrays with Examples

PHP added the array_map() function to help you apply one function to each item in an array. Understand the array_map…

PHP is_readable: Check File Accessibility

The PHP is_readable helps you validate file readability before any operation like read or include files. What Is PHP is_readable? The is_readable() function…

PHP array_unshift: How to Add Values to the Start of an Array

PHP array_unshift helps you add new elements to appear before the existing ones. Understand the array_unshift function in PHP The…

PHP Bitwise Operators: AND, OR, XOR, NOT with Examples

Bitwise operators use symbols like &, |, and ^, and they work at the binary level. But once you understand…

Parameters and Arguments in PHP: What’s the Difference?

If you're coding in PHP you've most probably come across the terms 'parameters' and 'arguments' in functions. Well, they are…

Previous Article

PHP MySQL ORDER BY: How to Sort Data in SQL?

Next Article

How to Update MySQL Data with PHP?

Write a Comment

Leave a Comment

Your email address will not be published. Required fields are marked *


Subscribe to Get Updates

Get the latest updates on Coding, Database, and Algorithms straight to your inbox.
No spam. Unsubscribe anytime.