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.

Deleting record only using PHP

If you have never used AJAX for deleting before then you either passing the id with $_GET or $_POST methods. Deleting the record when the value is get set.

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

In this tutorial, I show you How to remove the record from MySQL Table with jQuery AJAX.

What in the demonstration ?

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 removing record and 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,
  `link` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

2. Configuration

Create a new PHP file (config.php) for setting the connection to the tutorial database.

Completed Code

<?php

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

$con = mysql_connect($host, $user, $password) or die("Unable to connect");

// selecting database
$db = mysql_select_db($dbname, $con) or die("Database not found");

3. HTML

List records from MySQL in HTML table.

Here, I add the <span class='delete'> for deleting the record and within its id attribute, I also store the Table record id.

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 = mysql_query($query);

  $count = 1;
  while($row = mysql_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 new PHP file – remove.php from their execute delete query on the $_POST['id'].

Completed Code

<?php 
include "config.php";

$id = $_POST['id'];

// Delete record
$query = "DELETE FROM posts WHERE id=".$id;
mysql_query($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.


6. 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.

Spread the love

Related Post

Be First to Comment

Leave a Reply

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