Delete Data

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:

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:

Delete Query:

Parameter Binding:

Execution:

Error Handling:

Closing Connection:

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.

Thank you for being at the end. Happy Coding!