How to Delete Record from MySQL Table with AJAX

There are many ways AJAX can enhance user experience, such as adding, editing, or deleting records without reloading the page. With only PHP, deleting records often involves submitting the page or sending values via URL, resulting in page reloads with each deletion.

However, jQuery AJAX offers a more seamless solution. By passing the record ID via AJAX, you can delete records without reloading the page.

In the example, I’m creating an HTML table displaying a list of records with a delete button. Upon button click, the corresponding record is removed, and the HTML table row fades out as an effect.

---

Table of Content

1. Create a Table
2. Create a Database connection file
3. Create HTML Layout
4. Create AJAX file to Delete record
5. jQuery – Send AJAX request to Delete record
6. Demo
7. Conclusion

---

1. Create a Table

I am using posts table in the example.

CREATE TABLE `posts` (
   `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
   `title` varchar(100) NOT NULL,
   `content` text NOT NULL,
   `link` varchar(255) NOT NULL
);

---

2. Create a Database connection file

Create a config.php file for the database connection.

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
   die("Connection failed: " . mysqli_connect_error());
}

---

3. Create HTML Layout

Display the records from the posts table in a <table> format. Additionally, I’ve incorporated a <span> element with the class ‘delete’ to facilitate the deletion of records, storing the delete ID in the data-id variable.

Full Code

<div class='container'>
    <table border='1' >
        <tr style='background: whitesmoke;'>
            <th>S.no</th>
            <th>Title</th>
            <th>Operation</th>
        </tr>

        <?php 
        $query = "SELECT * FROM posts";
        $result = mysqli_query($con,$query);

        $count = 1;
        while($row = mysqli_fetch_array($result) ){
            $id = $row['id'];
            $title = $row['title'];
            $link = $row['link'];

        ?>
            <tr>
                <td align='center'><?= $count; ?></td>
                <td><a href='<?= $link; ?>'><?= $title; ?></a></td>
                <td align='center'>
                    <span class='delete' data-id='<?= $id; ?>'>Delete</span>
                </td>
            </tr>
        <?php
             $count++;
        }
        ?>
    </table>
</div>

---

4. Create AJAX file to Delete the record

Create a remove.php file to manage AJAX requests.

Retrieve the ID from the POST request.

Verify if a record exists in the posts table. If a record is found, execute the delete query using the provided ID, returning 1. If no record is found, return 0.

Full Code

<?php 
include "config.php";

$id = 0;
if(isset($_POST['id'])){
    $id = mysqli_real_escape_string($con,$_POST['id']);
}
if($id > 0){

    // Check record exists
    $checkRecord = mysqli_query($con,"SELECT * FROM posts WHERE id=".$id);
    $totalrows = mysqli_num_rows($checkRecord);

    if($totalrows > 0){
        // Delete record
        $query = "DELETE FROM posts WHERE id=".$id;
        mysqli_query($con,$query);
        echo 1;
        exit;
    }else{
        echo 0;
        exit;
    }
}

echo 0;
exit;

---

5. jQuery – Send AJAX request to Delete record

• Define a click event on the delete class.
• Retrieve the delete ID from the data-id attribute and assign it to the deleteid variable.
• Initiate an AJAX POST request to the ‘remove.php’ file, passing deleteid as data.
• Upon successful AJAX callback, verify the response.
• If the response equals 1, remove the row using remove() and apply a fadeOut() effect to introduce a delay.
• If the response is not equal to 1, display an alert with the message ‘Invalid ID.’.

Full Code

$(document).ready(function(){

    // Delete 
    $('.delete').click(function(){
         var el = this;
  
         // Delete id
         var deleteid = $(this).data('id');
 
         var confirmalert = confirm("Are you sure?");
         if (confirmalert == true) {
             // AJAX Request
             $.ajax({
                 url: 'remove.php',
                 type: 'POST',
                 data: { id:deleteid },
                 success: function(response){

                     if(response == 1){
	                 // Remove row from HTML Table
	                 $(el).closest('tr').css('background','tomato');
	                 $(el).closest('tr').fadeOut(800,function(){
	                      $(this).remove();
	                 });
                     }else{
	                 alert('Invalid ID.');
                     }

                 }
             });
         }

    });

});

---

6. Demo

View Demo

---

7. Conclusion

In this post, I’ve demonstrated how AJAX can streamline the deletion of existing records from a MySQL Table, thereby enhancing user interaction. While I showcased its implementation with an HTML table, it’s worth noting that AJAX can be seamlessly integrated with other elements such as <div> and <ul>.

By leveraging jQuery AJAX, we circumvent the need for page reloads traditionally associated with PHP-based record deletion. This method empowers developers to delete records dynamically, offering a smoother user experience.

If you found this tutorial helpful then don't forget to share.