How to Delete Record from MySQL Table with AJAX

Last updated on by

There are many things you can do with AJAX to improve the user experience, for example, Add, edit or delete the record and update the layout without reloading the page.

With only PHP you can easily delete records but require to submit the page or send value by URL and according to it delete a record.

This works and removes the record but it reloads the page every time.

You can do this with jQuery AJAX where you need to pass the record id from AJAX which needs to delete.

In the example, I am creating the HTML table which shows the list of records with a delete button. When the button gets clicked then remove the record and also remove the HTML table row with fadeOut() effect.

How to Delete Record from MySQL Table with AJAX

Demo  Download

Contents

  1. Table structure
  2. Configuration
  3. HTML
  4. PHP
  5. jQuery
  6. Demo
  7. Conclusion

1. Table structure

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
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2. Configuration

Create a config.php file for the database connection.

Completed Code

<?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. HTML

List records from posts table in the <table >.

Here, I add the <span class='delete'> for deleting the record and store delete id in data-id variable.

Completed 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. PHP

Create a remove.php file.

Read POST id.

Check if a record exists or not in the posts table. If exists then execute the Delete query on the id and return 1 otherwise return 0.

Completed 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

Define click event on delete class.

Read delete id from data-id attribute and assign it in deleteid variable.

Send AJAX post request to 'remove.php' file. Pass deleteid as data.

On AJAX successfully callback check response.

If response == 1 then remove a row using remove() and use fadeOut() effect to add some delay.

If response != 1 then alert('Invalid ID.');.

Completed 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 explained how you can use AJAX to delete existing records from MySQL Table. I used it with HTML table in the demonstration you can also use it with <div>, <ul>, etc. elements.

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

49 thoughts on “How to Delete Record from MySQL Table with AJAX”

  1. Thanks for creating this post. And, not making it too long 🙂 I don’t write jquery often enough to not usually need a reference to see how the syntax should go. This is a great solution for exactly what I needed to do. It’s nice to find something relevant outside of Stack Overflow.

    Reply

    • Hi Liam,
      Here, is the short explain –
      On the delete button, I use the posts table “id” value to set id attribute e.g. del_1, del_2,etc.
      On the button click split the id by “_” to get to delete record id (deleteid = splitid[1]) and pass in the $.ajax request. In PHP file execute DELETE query on the POST id.

      Reply

  5. Thanks for this tutorial. If I want to delete from multiple tables, will the other delete queries go to remove.php? And how, please?

    Reply

  6. Thanks for this amazing tutorial.

    I would like to clear one thing though, while deleting works perfectly, I was wondering if we could make the Sl no change as well instantly. It takes a refresh to update Sl no.

    Reply

    • To update the numbering of S.no after removing record you can either send AJAX request to fetch all records and update the table or loop on the table row using jQuery to update the S.no column value.

      Reply

  16. Yes Yogesh, i have renamed the script.js. file in “Ajax request url:’removedp.php’ “.
    it was called before ‘removed.php’.
    What else to do?
    Thank!

    Reply

  18. Yogesh: A line I have added and now it works.
    I share it with you.
    // AJAX Request
    $.ajax({
    url: ‘removeproducto.php’,
    cache: false, <– This is a line.
    type: 'POST',

    best regards

    Reply

    • Hi Priyanshu,
      You need to update the HTML and PHP script –
      In HTML –

      <span class='delete' id='del_<?php echo $row['sno']; ?>'>Delete</span>

      In PHP –

      $checkRecord = mysqli_query($con,"SELECT * FROM posts WHERE sno=".$id);
      Reply

Leave a Comment

© 2016-2023 Makitweb, All rights reserved