PHP MySQL Delete Data

In this tutorial, we will learn to delete single or multiple records from the MySQL table using the PHP program. It is a very easy and simple procedure to delete data from a MySQL table. We will show how to delete records using the MySQLi procedural method, MySQLi object-oriented method, and the PDO method. All methods that we will demonstrate are very easy and you can use any of them in your application. So, let’s get started!

Also read: PHP MySQL Update Data

PHP MySQL Delete Data

In MySQL, we use the DELETE statement to delete the records. We can use the DELETE statement to delete a single record, multiple records, or entire records.

As a quick revision, have a look at the DELETE queries. To delete a single record-

DELETE FROM table_name
WHERE column_name= some_value;Code language: SQL (Structured Query Language) (sql)

Here, the WHERE clause is used to target a particular record and delete it. To delete all records-

DELETE FROM table_name;Code language: SQL (Structured Query Language) (sql)

The above query will delete all records from the table because we haven’t specified which records we want to delete. This means that if we omit the WHERE clause, all records will be deleted from the table. To delete multiple records-

DELETE FROM table_name
WHERE id > 10 AND id < 20;Code language: SQL (Structured Query Language) (sql)

In the above query, we will delete all the records having IDs greater than ten and less than twenty. You can use the IN operator as well to target multiple records.

PHP MySQL Delete Data Examples

For demonstrating the delete operations, we will use the table of the following description. The table is filled with some data and we will delete that data in the following examples.

Delete Data Using MySQLi (Procedural)

<?php
$server = "localhost";
$user = "root";
$pass = "";
$db = "journaldev";
$conn = mysqli_connect($server, $user, $pass, $db);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
$sql = "DELETE FROM emp WHERE id=4";
if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error occured: " . mysqli_error($conn);
}
mysqli_close($conn);
?>Code language: PHP (php)

In the above program, we write a delete query and assign it to the variable.

Then we execute the query using the mysqli_query() method. If the query executes successfully, we get the message printed on the webpage.

Let’s check the table now to see if the record is deleted.

SELECT * FROM emp;Code language: SQL (Structured Query Language) (sql)

Delete Records Using Mysqli Procedural — Delete Record Using Mysqli Procedural

As you can see, the record having the id 4 is deleted from the table.

Delete Data Using MySQLi (Object-Oriented)

<?php
$server = "localhost";
$user = "root";
$pass = "";
$db = "journaldev";
$conn = new mysqli($server, $user, $pass, $db);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$sql = "DELETE FROM emp WHERE id=5";
if ($conn->query($sql) === TRUE) {
    echo "Record deleted successfully";
} else {
    echo "Error occured: " . $conn->error;
}
$conn->close();
?>Code language: PHP (php)

Let’s check the table to see if the record is deleted.

SELECT * FROM emp;Code language: SQL (Structured Query Language) (sql)

Delete Records Using Mysqli Object Oriented — Delete Record Using Mysqli Object-Oriented

As you can see, the record having the id 5 is deleted from the table.

Delete Data Using PDO

<?php
$server = "localhost";
$user = "root";
$pass = "";
$db = "journaldev";
try {
    $conn = new PDO("mysql:host=$server;dbname=$db", $user, $pass);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "DELETE FROM emp WHERE id=1";
    $conn->exec($sql);
    echo "Record deleted successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
?>Code language: PHP (php)

Let’s check the table to see if the record is deleted.

SELECT * FROM emp;Code language: SQL (Structured Query Language) (sql)

Delete Records Using Mysqli PDO — Delete Record Using Mysqli PDO

As you can see, the record having the id 1 is deleted from the table.

Conclusion

In this tutorial, we learned to delete records from the MySQL table using the PHP program. As you have seen that it is very easy to delete records from MySQL table using the PHP program and only takes a few lines of codes. You can try to delete multiple records on your own as a practice to have a good command on the PHP MySQL stack.