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.