Delete data from the table using PHP and MYSQL database

In this tutorial, we are going to perform the deletion operation. We will create a PHP script to securely delete data from a MySQL database table. Before performing a delete operation, we first need to create a database table and insert some data into it. Once the data is populated, it can be deleted using its unique column ID. In this process, we will fetch the data into an HTML table and then use a MySQL DELETE query to remove specific rows.

Create a Database Table

First, we need to create a database table. You can use the MySQL CREATE TABLE query to do this. Adding an AUTO_INCREMENT primary key is essential because it allows us to identify and delete specific rows using a unique ID. Go to your phpMyAdmin panel and execute this query:


CREATE TABLE `tutorials` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `mobile` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Insert Data into the Database for the Delete Operation

You cannot delete data from a table if it is empty. Data must first be inserted into the MySQL table before it can be removed. There are two ways to insert information into the database:

1. Insert Data using a MySQL Query

To quickly insert sample data into the database table, you can execute the following MySQL INSERT query:


INSERT INTO `tutorials` (`name`, `email`, `mobile`) VALUES ('Mohan', 'mohan@example.com', '9090909012');

2. Create an HTML Form to Insert Data

In a previous tutorial, we covered how to insert data into a MySQL database table and display it using an HTML form. You can read the complete guide here: Insert Data into Database and Display in HTML Table using PHP & MySQL.

1. Create an HTML form and execute an insert query.
2. Fetch and display the data in an HTML table.
3. Finally, perform the delete query using the row ID.

Make a Connection to Perform the Delete Operation

Create a connection file. This file connects your PHP script to the MySQL database securely. Once connected, we can easily insert, display, and delete data. Save this file as config.php.


<?php
// config.php
$host = '127.0.0.1'; // or localhost
$dbName = 'tutorials'; // Your database name
$dbUsername = 'root'; 
$dbPassword = '';  

try {
    // Create a secure PDO connection
    $pdo = new PDO("mysql:host=$host;dbname=$dbName;charset=utf8mb4", $dbUsername, $dbPassword);
    // Set PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    die("Database Connection Failed: " . $e->getMessage());
}
?>

1. Define your host (localhost or 127.0.0.1).
2. Define your database name.
3. Define your username (default is root for localhost).
4. Define your database password (leave blank for localhost).

Create a PHP Script for the Delete Operation

After inserting and displaying the data in an HTML table, you can easily delete it. We typically add a "Delete" link or button inside the HTML table for each row. When the user clicks this link, the unique ID of that row is sent via the URL, and the PHP script processes the MySQL DELETE query.

Delete Data by ID

Deleting data by its unique ID is the safest and most common method. Here is the basic syntax of a MySQL delete query using the WHERE clause:


DELETE FROM `tutorials` WHERE id = 1;

Since the ID changes for every row, we dynamically fetch the ID from the URL and securely delete the record using PDO Prepared Statements.

Delete Script (delete.php)


<?php
// Include the database connection file
require_once("config.php"); 

// Check if the 'id' parameter exists in the URL
if(isset($_GET['id'])) {
    $id = $_GET['id'];
    
    try {
        // Securely prepare the DELETE query to prevent SQL Injection
        $sql = "DELETE FROM tutorials WHERE id = :id";
        $stmt = $pdo->prepare($sql);
        
        // Execute the query with the bound ID parameter
        $stmt->execute([':id' => $id]);
        
    } catch(PDOException $e) {
        die("Error deleting record: " . $e->getMessage());
    }
}

// Redirect back to the display page (e.g., index.php)
header("Location: index.php");
exit();
?>

How this works:
1. In the PHP script above, we fetch the id parameter from the URL. This ID represents a specific row in the database. For example, clicking the delete link for row 1 passes ?id=1 to the script.
2. The DELETE query is then securely executed using a PDO prepared statement, removing only the data associated with that specific ID from the MySQL database.
3. Finally, the header() function redirects the user back to the main data table page.