How to DELETE data in MySQL from PHP

Examples of using PHP to perform a DELETE operation in MySQL. Before running these examples, make sure you have established a connection to your MySQL database using the appropriate credentials.

Deleting a single record based on a specific condition.

// Replace these variables with your actual database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Delete a single record based on a condition
$idToDelete = 123; // Replace with the primary key value of the record you want to delete

$sql = "DELETE FROM your_table_name WHERE id = $idToDelete";

if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully.";
} else {
echo "Error deleting record: " . $conn->error;
}

$conn->close();

Deleting multiple records based on a condition.

// Replace these variables with your actual database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// Delete multiple records based on a condition
$condition = "age < 18"; // Replace with your desired condition

$sql = "DELETE FROM your_table_name WHERE $condition";

if ($conn->query($sql) === TRUE) {
echo "Records deleted successfully.";
} else {
echo "Error deleting records: " . $conn->error;
}

$conn->close();

Important: Always be cautious when using DELETE statements, as they permanently remove data from the database. Make sure to take appropriate precautions, such as using prepared statements to prevent SQL injection attacks, and always test your code thoroughly before running it on a live database.

When dealing with complex joined tables and performing DELETE operations, using transactions is crucial to maintain data integrity. Transactions ensure that either all changes are committed together or none of them are, preventing partial deletions or unexpected data inconsistencies. Below is an example of performing a DELETE operation on complex joined tables using PHP and transactions:

Assume we have three tables: `orders`, `order_items`, and `products`. We want to delete an order and all its associated items from the `orders` and `order_items` tables, and we also want to update the `products` table to reflect the removal of the corresponding order items.

PHP code with the use of transactions:

// Replace these variables with your actual database credentials
$servername = "localhost";
$username = "your_username";
$password = "your_password";
$dbname = "your_database";

try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Begin the transaction
$conn->beginTransaction();

$orderIdToDelete = 123; // Replace with the order ID you want to delete

// Step 1: Delete order items
$stmtDeleteOrderItems = $conn->prepare("DELETE FROM order_items WHERE order_id = :order_id");
$stmtDeleteOrderItems->bindParam(':order_id', $orderIdToDelete);
$stmtDeleteOrderItems->execute();

// Step 2: Update product quantities based on the deleted order items
$stmtUpdateProducts = $conn->prepare("
UPDATE products p
JOIN order_items oi ON p.product_id = oi.product_id
SET p.quantity = p.quantity + oi.quantity
WHERE oi.order_id = :order_id
");
$stmtUpdateProducts->bindParam(':order_id', $orderIdToDelete);
$stmtUpdateProducts->execute();

// Step 3: Delete the order
$stmtDeleteOrder = $conn->prepare("DELETE FROM orders WHERE order_id = :order_id");
$stmtDeleteOrder->bindParam(':order_id', $orderIdToDelete);
$stmtDeleteOrder->execute();

// Commit the transaction
$conn->commit();

echo "Order and associated items deleted successfully.";
} catch (PDOException $e) {
// Rollback the transaction on error
$conn->rollback();
echo "Error deleting order: " . $e->getMessage();
}

// Close the connection
$conn = null;

In this example, we use a PDO connection to perform the DELETE operations and the necessary JOIN and UPDATE statements. We wrap the entire process within a transaction using $conn->beginTransaction() and $conn->commit(), and if any error occurs during the process, we roll back the changes using $conn->rollback(). This ensures data integrity, and the database will remain consistent even if there's an error during the deletion process.

Make sure to thoroughly test your code and ensure that it behaves as expected before running it on a production environment.