PHP/MySQL/JSON

How to delete records from a database with PHP

Pinterest LinkedIn Tumblr

In a web application, 4 operations are mainly performed, Create,Read, Update and Delete. These are called CRUD operations in short.

  • Create – This is the first operation that is to save the data to the database.
  • Read – This is the operation to read or display the saved data from the database.
  • Update – This is to update the existing records in a database.
  • Delete – Last but not least, this is used to perform delete operation in the database.

Each operation has its own query to perform its task. Let’s take a look at each query and we will understand its concepts in our tutorials. But here we will talk about Delete operation only.

PHP | MySQL Delete Query

The DELETE query is used to delete records from the database table.
It is generally used along with the “Select” statement to delete only those records that satisfy a specific condition.

Syntax

DELETE FROM table_name WHERE column_value=condition

Example

Consider this following table named bird_delete_records has column names id, name, email, contact, and department.

how-to-delete-records-from-a-database-with-php-example-table

To delete the records we will write this query as given below.

DELETE FROM bird_delete_records WHERE id=1

Then what we are doing just putting our table name to query and make a condition. If any column satisfies the condition then we delete the record.

I hope you got the concept behind this. Now, let’s make our hands dirty with code. We need to create these files. Here are the files and folders.

how-to-delete-records-from-a-database-with-php-filesand-folder

So you got the files and folder. Now we will create these files.

index.php

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Delete records from database using PHP - Coding Birds Online</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
    <link rel="icon" href="https://codingbirdsonline.com/wp-content/uploads/2019/12/cropped-coding-birds-favicon-2-1-192x192.png" type="image/x-icon">
    <style>
        #thead>tr>th{ color: white; }
    </style>
</head>
<body>
<div style="margin-top: 20px;padding-bottom: 20px;">
    <center>
        <img width="100" src="https://codingbirdsonline.com/wp-content/uploads/2019/12/cropped-coding-birds-favicon-2-1-192x192.png"/>
        <h3>Delete records from database using PHP </h3>
    </center>
</div>
<div class="container">
    <table class="table">
        <thead id="thead" style="background-color: #26a2af">
        <tr>
            <th>Sr.No</th>
            <th>Name</th>
            <th>Email</th>
            <th>Contact</th>
            <th>Department</th>
            <th>Action</th>
        </tr>
        </thead>
        <tbody>
        <?php
        include "config.php";
        include_once "Common.php";
        $common = new Common();
        $records = $common->getAllRecords($connection);
        if ($records->num_rows>0){
            $sr = 1;
            while ($record = $records->fetch_object()) {
                $recordId = $record->id;
                $name = $record->name;
                $email = $record->email;
                $contact = $record->contact;
                $department = $record->department;?>
                <tr>
                    <th><?php echo $sr;?></th>
                    <th><?php echo $name;?></th>
                    <th><?php echo $email;?></th>
                    <th><?php echo $contact;?></th>
                    <th><?php echo $department;?></th>
                    <td><a href="delete-script.php?recordId=<?php echo $recordId?>" class="btn btn-danger btn-sm">Delete</a> </td>
                </tr>
                <?php
                $sr++;
            }
        }
        ?>
        </tbody>
    </table>
</div>
</body>
</html>

As you see the index.php file includes config.php and Common.php. These files are used to make the database connection and get records from the database and delete them, respectively. Create these files.

config.php

<?php
$hostName = "localhost"; // host name
$username = "root";  // database username
$password = ""; // database password
$databaseName = "codingbirds"; // database name

$connection = new mysqli($hostName,$username,$password,$databaseName);
if (!$connection) {
    die("Error in database connection". $connection->connect_error);
}
?>

Common.php

<?php
class Common
{
    public function getAllRecords($connection) {
        $query = "SELECT * FROM bird_delete_records";
        $result = $connection->query($query) or die("Error in query1".$connection->error);
        return $result;
    }

    public function deleteRecordById($connection,$recordId) {
        $query = "DELETE FROM bird_delete_records WHERE id='$recordId'";
        $result = $connection->query($query) or die("Error in query2".$connection->error);
        return $result;
    }
}

If you see index.php file and head over to where button we created we used delete-script.php?recordId=id. This is a query string variable when we use a question mark, a variable name is equal to value. This value will be received by delete-script.php to delete the records.

delete-script.php

<?php
include "config.php";
include_once "Common.php";
if (isset($_GET['recordId'])){
    $recordId = $_GET['recordId'];
    $common = new Common();
    $delete = $common->deleteRecordById($connection,$recordId);
    if ($delete){
        echo '<script>alert("Record deleted successfully !")</script>';
        echo '<script>window.location.href="index.php";</script>';
    }
}

In this file we are just creating an object of Common.php file, that is the class file. Here I have used the OOPs method. You can use procedural if you want. You can learn about oops from here.

Now its time to test our code. Is it working? surely it will work. Don’t worry, I will provide source code also. So this is the output when you run the code.

how-to-delete-records-from-a-database-with-php-output-screen

When you click the delete button then you will get this success alert message, that Record deleted successfully!. You can download the source code also. So that there is no possibility of errors.

how-to-delete-records-from-a-database-with-php-delete-success-output

If you have any problem then you watch this video also. I uploaded it on youtube a year ago. But explains the same thing but in procedural style.

Thanks for visiting this blog. I will see you in the next article.

Happy Coding 🙂

I am a professional Web / Software developer, B.Tech CSE, who has been working in PHP & CodeIgniter and WordPress for more than 2+ years and know it inside and out, including the creation of Web Apps, WordPress websites from scratch and their customization.

Write A Comment