How to delete a row and display updated database without refreshing using ajax

Question

I have a challenge with a project i'm working on and have tried all i could to get it working without success. Any assistance will be appreciated please. I built an HTML/Bootstrap form whose fields value are sent to mysql database with ajax function and displays the updated database on the page without refreshing the page, the part which works fine. Each row has a delete button. However, when I delete any of the displayed rows, only the first row I click gets deleted and the updated database displays. Subsequent attempts to delete any other row doesn’t work, until the page is reloaded and the process is repeated then only the first delete operation works again. My challenge is to get the delete button delete a row, fetch and display the updated database immediately without having to refresh the page. Here is my ajax call:

var pry= $("#pry").val();
var sec  = $("#sec").val();
var coll = $("#coll ").val();
var urlId = window.location.search.substring(1);    
var pageId = urlId.split('=')[1];

//Insert into DB
$.ajax({
url: "process.php",
type: "POST",
async: false,
data:
  {
   "done": 1,   
   "pry": pry,  
   "sec": sec,                                      
   "coll": coll,
   "page_id": pageId
   },                                           
    success: function(){
         displayFromDb();   
         clearInputs();
    }
});

//delete from db
$(".my_butn").on("click", function(e){
    e.preventDefault();
    var id = $(this).attr("id");

$.ajax({
    type: "POST",
    url: "delete.php",
    async: false,
    data:
       {                                         
        "pry": pry, 
        "sec": sec,                                     
        "coll": coll,
        "page_id": pageId,
        “id”: id
       },
         success: function(){
           displayFromDb();
         }
      });
    });

//function displayFromDb
     function displayFromDb(){
       $.ajax({
          type: "POST",
          url: "process.php",
          async: false,
          data:
             {                                                                                                                                                        
              "display": 1,                                             
              "page_id": pageId
             },
          success: function(d){                                                                                                              
                $("#tab-display").fadeIn().html(d);
          }
       });
    }

//process.php file
<?php
//Insert to  sql    
  if (isset($_POST["done"])){
    $conn = mysqli_connect('localhost', 'root', '', 'educ');
    $student_id = mysqli_real_escape_string($conn, $_POST['page_id']);
    $pry = mysqli_real_escape_string($conn, $_POST["pry"]);
    $sec = mysqli_real_escape_string($conn, $_POST["sec"]);
    $coll = mysqli_real_escape_string($conn, $_POST["coll"]);

    $sql = mysqli_query($conn, "INSERT INTO students (id, student _id, pry, sec, coll) VALUES (' ', '$student_id ', '$pry', '$sec', '$coll')");
}

//display from sql  
if (isset($_POST['display'])){
    $i=1;
    $sql2 = mysqli_query($conn, "SELECT id, pry, sec, coll FROM students WHERE student_id = '$student_id");
    if ($sql2){
        echo '<table class="table table-striped table-bordered"><thead><tr><th>S/N</th><th>ID</th><th>PRY EDUC</th><th>SEC EDUC</th><th>COLL EDUC</th><th>DEL ROW</th></tr></thead>';

    while ($row = mysqli_fetch_array($sql2, MYSQLI_ASSOC)){
        $id = $row['id'];
        $pry = $row['pry']; 
        $sec = $row['sec'];
        $coll = $row['coll'];
$del = "<button type='button' class='btn btn-danger' id='$id'> X</button>";
echo '<tbody><tr><td>'.$i.'</td><td>'.$id.'</td><td>'.$pry.'</td><td>'.$sec.'</td><td>'.$coll.'</td><td>'.$del.'</td></tr></tbody>';
        $i++;
        }
        echo '</table>';
        }
    }?>

And my delete.php file here

  <?php
        $conn = mysqli_connect('localhost', 'root', '', 'educ');
        $student_id = mysqli_real_escape_string($conn, $_POST['page_id']);
        $pry = mysqli_real_escape_string($conn, $_POST["pry"]);
        $sec = mysqli_real_escape_string($conn, $_POST["sec"]);
        $coll = mysqli_real_escape_string($conn, $_POST["coll"]);
        $id =  $_POST["id"];

        $sql = mysqli_query($conn, "DELETE FROM students WHERE id = '$id' ");
    }
?>

Answer 1

A simplistic approach....

Change 1:
In delete.php, you could add the following code after executing the DELETE query:

echo mysqli_affected_rows($conn); 
die;

This means that the AJAX call would receive whatever is echoed value. Now, mysqli_affected_rows() would return 0 if no rows were deleted or -1 if there was an error, which can be checked in the success section of $.ajax() to relay the appropriate messages to the user.

Once you've checked that the number of deleted rows is > 0, the <tr> corresponding to the button can then be removed safely. Please check the following code snippet which might be of some help in achieving this. You'd just need to wrap this code inside success : function(delete_count){ .... }

Change 2:

$(".my-del-btn").on("click", function(){
  // AJAX call, success: function(delete_count)
  //if(delete_count > 0){
      $(this).parents().closest("tr").fadeOut(1000)
      .promise().done(function(){
          $(this).parents().closest("tr").remove();
      });
  //}
  //else{
  //    console.log("Error in deleting id = " + id);
  //}

});

<head>
<link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.0.0/css/bootstrap.css" 
      rel="stylesheet" type="text/css">

<link href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.5.2/animate.min.css" 
      rel="stylesheet" type="text/css">

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.0.0/js/bootstrap.min.js"></script>
</head>
<body>
<table class="table table-striped table-bordered">
  <thead>
    <tr>
      <th>ID</th>
      <th>DEL ROW</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td> 1 </td>
      <td> 
          <button type='button' class='btn btn-danger my-del-btn'>DELETE</button> 
      </td>
    </tr>
    <tr>
      <td> 2 </td>
      <td> 
          <button type='button' class='btn btn-danger my-del-btn'>DELETE</button> 
      </td>
    </tr>
    <tr>
      <td> 3 </td>
      <td> 
          <button type='button' class='btn btn-danger my-del-btn'>DELETE</button> 
      </td>
    </tr>
</table>
</body>

A few important notes:

• As the post linked in the comments above would indicate, having async: false is, in a way, detrimental to the application (besides it being deprecated in jQuery 1.8 onwards).

• Please take a look at and consider implementing MySQLi Prepared Statements to secure your PHP code from SQL Injection Attacks.