To remove records from a database table, you can use the SQL DELETE
statement. It is commonly used with the WHERE
clause to delete specific records based on certain conditions.
The basic syntax of the DELETE
statement is as follows:
Let's create a SQL query using the DELETE
statement and the WHERE
clause. Afterwards, we'll execute this query using the PHP mysqli_query()
function to delete records from the table. Consider the following persons table within the demo database:
+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | peterparker@mail.com | | 2 | John | Rambo | johnrambo@mail.com | | 3 | Clark | Kent | clarkkent@mail.com | | 4 | John | Carter | johncarter@mail.com | | 5 | Harry | Potter | harrypotter@mail.com | +----+------------+-----------+----------------------+
The PHP code below demonstrates how to delete records from the persons table where the first_name is equal to John.
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Attempt delete query execution
$sql = "DELETE FROM persons WHERE first_name='John'";
if(mysqli_query($link, $sql)){
echo "Records were deleted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
// Close connection
mysqli_close($link);
?>
After deleting, the persons table will appear like this:
+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | peterparker@mail.com | | 3 | Clark | Kent | clarkkent@mail.com | | 5 | Harry | Potter | harrypotter@mail.com | +----+------------+-----------+----------------------+
As shown, the records have been successfully deleted from the persons table.
Warning: The WHERE
clause in the DELETE
statement determines which record or records are deleted. If you don't include the WHERE
clause, all records will be deleted.