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.
Table of Content
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
PDOobject is initialized with the hostname, database name, username, and password. PDO::ATTR_ERRMODEensures exceptions are thrown for database errors.
Delete Query:
- The
DELETEstatement uses a named placeholder:inactiveStatusfor the condition.
Parameter Binding:
- The
bindParammethod assigns the value of$inactiveStatusto the placeholder, ensuring secure data handling.
Execution:
- The
executemethod 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 = nullexplicitly 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
In this article, You will understand everything you need to know about how to use PHP to select data from…
One of the first tasks you will likely encounter is creating a table in databases. Tables are the main part…
Escape characters appeared in PHP because some symbols in strings serve special purposes. For example, a quote can end a…
PHP developers may be need to determine the number of elements in an array. The count() function in PHP makes…
PHP array_push lets you add one or more values to the end of an array. It appeared to make adding…
PHP added the array_map() function to help you apply one function to each item in an array. Understand the array_map…
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 helps you add new elements to appear before the existing ones. Understand the array_unshift function in PHP The…
Bitwise operators use symbols like &, |, and ^, and they work at the binary level. But once you understand…
If you're coding in PHP you've most probably come across the terms 'parameters' and 'arguments' in functions. Well, they are…