MySQLi (MySQL Improved) is a PHP extension that facilitates interaction with MySQL databases, allowing developers to perform DELETE operations efficiently. This article delves into the various aspects of performing DELETE operations using MySQLi, covering basic DELETE queries and providing code examples to illustrate these concepts.
Understanding the DELETE Operation
The DELETE statement is used to remove existing records from a table. The basic syntax for a DELETE operation is:
DELETE FROM table_name WHERE condition;
Here:
table_name is the name of the table from which the data will be deleted.
condition specifies which record(s) should be deleted. Without a WHERE clause, all records in the table will be deleted, which is often not desirable.
Connecting to MySQL Database Using MySQLi
Before performing any database operations, we need to establish a connection to the MySQL database using MySQLi. Here is how to connect to a MySQL database:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
In the above code:
Replace “localhost” with your database server name if it is different.
Replace “username” and “password” with your MySQL database username and password.
Replace “database_name” with the name of your database.
Basic DELETE Queries in MySQLi
Example 1: Deleting a Single Row
Let’s assume we have a table called users with the following columns: id, username, email, and password. Here is how you can delete a single row in the users table:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to delete a record
$sql = "DELETE FROM users WHERE username='john_doe'";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Example 2: Deleting Multiple Rows
To delete multiple rows based on a condition, extend the WHERE clause to include the condition that matches multiple records:
<?php
$servername = "localhost";
$username = "username";
password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to delete multiple records
$sql = "DELETE FROM users WHERE email LIKE '%@example.com'";
if ($conn->query($sql) === TRUE) {
echo "Records deleted successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
$conn->close();
?>
Example 3: Using Prepared Statements
Using prepared statements is a good practice to prevent SQL injection. Here’s how to use prepared statements with MySQLi for a DELETE operation:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Prepare and bind
$stmt = $conn->prepare("DELETE FROM users WHERE username=?");
$stmt->bind_param("s", $username);
// Set parameters and execute
$username = "john_doe";
$stmt->execute();
echo "Record deleted successfully";
$stmt->close();
$conn->close();
?>
Example 4: Deleting Data from a Form
When deleting data collected from an HTML form, ensure to validate and sanitize the inputs to avoid security vulnerabilities. Here’s an example:
<!DOCTYPE html>
<html>
<body>
<form action="delete.php" method="post">
Username: <input type="text" name="username"><br>
<input type="submit">
</form>
<script>var rocket_lcp_data = {"ajax_url":"https:\/\/codersship.com\/wp-admin\/admin-ajax.php","nonce":"caf671a010","url":"https:\/\/codersship.com\/mysqli\/performing-delete-operations","is_mobile":false,"elements":"img, video, picture, p, main, div, li, svg","width_threshold":1600,"height_threshold":700,"debug":null}</script><script data-name="wpr-lcp-beacon" src='https://codersship.com/wp-content/plugins/wp-rocket/assets/js/lcp-beacon.min.js' async></script></body>
</html>
In the delete.php file:
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
if ($_SERVER["REQUEST_METHOD"] == "POST") {
// Collect and sanitize input data
$username = $conn->real_escape_string($_POST['username']);
// Prepare and bind
$stmt = $conn->prepare("DELETE FROM users WHERE username=?");
$stmt->bind_param("s", $username);
// Execute the statement
if ($stmt->execute()) {
echo "Record deleted successfully";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
}
$conn->close();
?>
Best Practices for DELETE Operations
1. Use Prepared Statements
Prepared statements not only enhance security by preventing SQL injection but also optimize performance for repeated queries.2. Validate and Sanitize Inputs
Always validate and sanitize user inputs before using them in your SQL queries to avoid SQL injection and other security risks.3. Handle Errors Gracefully
Always handle errors gracefully to avoid exposing sensitive information. Use proper error handling techniques to log errors and inform users without exposing details.4. Close Connections
Always close the database connection after completing the database operations to free up resources.5. Use Transactions
For complex operations that involve multiple DELETE statements, consider using transactions to ensure data integrity.
<?php
$conn->begin_transaction();
try {
// Multiple delete queries
$conn->query("DELETE FROM users WHERE username='user1'");
$conn->query("DELETE FROM users WHERE username='user2'");
// Commit transaction
$conn->commit();
echo "Transaction completed successfully";
} catch (Exception $e) {
// Rollback transaction in case of an error
$conn->rollback();
echo "Transaction failed: " . $e->getMessage();
}
?>