How to Delete Record from MySQL Table with AJAX

There are many things you can do with AJAX to improve the user experience, for example, Add, edit or delete the record and update 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 it delete 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 need to delete.

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

How to Delete Record from MySQL Table with AJAX


Contents

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

 

1. Table structure

I am using posts Table.

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 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 MySQL in HTML table.

Here, I add the <span class='delete'> for deleting the record and use the record id the define value of id attribute.

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'><?php echo $count; ?></td>
   <td><a href='<?php echo $link; ?>'><?php echo $title; ?></a></td>
   <td align='center'>
    <span class='delete' id='del_<?php echo $id; ?>'>Delete</span>
   </td>
  </tr>
  <?php
   $count++;
  }
 ?>
 </table>
</div>

4. PHP

Create a remove.php file.

Delete posts table record by id by executing delete query.

Completed Code

<?php 
include "config.php";

$id = $_POST['id'];

// Delete record
$query = "DELETE FROM posts WHERE id=".$id;
mysqli_query($con,$query);

echo 1;

5. jQuery

Performing the delete action when the <span class='delete'> gets clicked.

Get delete id by splitting the element id and sending this in AJAX request. On AJAX successfully callback removing a row with fadeOut() effect.

Completed Code

$(document).ready(function(){

 // Delete 
 $('.delete').click(function(){
  var el = this;
  var id = this.id;
  var splitid = id.split("_");

  // Delete id
  var deleteid = splitid[1];
 
  // AJAX Request
  $.ajax({
   url: 'remove.php',
   type: 'POST',
   data: { id:deleteid },
   success: function(response){

    // Removing row from HTML Table
    $(el).closest('tr').css('background','tomato');
    $(el).closest('tr').fadeOut(800, function(){ 
     $(this).remove();
    });

   }
  });

 });

});

6. Demo

Click on the Delete button.


7. Conclusion

In this post, I explained you the use of 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.

Related Post

Spread the love
  •  
  •  
  •  
  •  
  • 1
  •  
  •  

8 Comments

  1. Darren said:

    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.

    January 10, 2018
    Reply
  2. Liam said:

    I can’t make … Delete clickable. Can you please explain how you make it possible?

    January 14, 2018
    Reply
    • Yogesh Singh said:

      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.

      January 15, 2018
      Reply
  3. Panther said:

    thanks a lot.

    January 27, 2018
    Reply
  4. rishikesh said:

    Hey, I can delete the row from html table but not from mysql DB. What do i do ?

    February 18, 2018
    Reply
    • Yogesh Singh said:

      Hi Rishikesh,
      If you are properly removing a row from HTML table means your AJAX request working fine.

      Check your AJAX php file.

      February 18, 2018
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *