PHP MySQL DELETE Query

Previous Page Next Page

Deleting Data from Database Tables

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:

DELETE FROM table_name WHERE column_name=some_value

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.

Example

Procedural Object Oriented PDO

Download

<?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);
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");

// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}

// Attempt delete query execution
$sql = "DELETE FROM persons WHERE first_name='John'";
if($mysqli->query($sql) === true){
echo "Records were deleted successfully.";
} else{
echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
}

// Close connection
$mysqli->close();
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}

// Attempt update query execution
try{
$sql = "DELETE FROM persons WHERE first_name='John'";  
$pdo->exec($sql);
echo "Records were deleted successfully.";
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

// Close connection
unset($pdo);
?>

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.

Previous Page Next Page