PHP MySQL DELETE Query

In this tutorial you'll learn how to delete records from a MySQL table using PHP.

Deleting Database Table Data

Just as you insert records into tables, you can delete records from a table using the SQL DELETE statement. It is typically used in conjugation with the WHERE clause to delete only those records that matches specific criteria or condition.

The basic syntax of the DELETE statement can be given with:

DELETE FROM table_name WHERE column_name=some_value

Let's make a SQL query using the DELETE statement and WHERE clause, after that we will execute this query through passing it to the PHP mysqli_query() function to delete the tables records. Consider the following persons table inside 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 in the following example will delete the records of those persons from the persons table whose 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 the deletion the persons table will look something 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 you can see the records has been deleted successfully from the persons table.

 

Warning:The WHERE clause in the DELETE statement specifies which record or records should be deleted. If you omit the WHERE clause, all records will be deleted.

 
Close

Your Feedback:

 

We would love to hear from you! Please say something.