Delete Table Row on Button Click Using Ajax

Question

I have an HTML table that has 4 columns: SKU Group, Group_ID, Edit button, and Delete button. I am working on the delete function now and want it so that whenever I press the delete button, a confirmation box pops up and then if "OK" is pressed, it deletes the row and sends a delete query which deletes it from the database.

I know I am to use Ajax and a separate PHP script for the delete query, but cannot seem to figure it out. Any help is appreciated!

HTML for Delete Button:

<td><input type="button" class="delete" name="delete" value="Delete" onclick="deleteRow(this)"></td>

JavaScript...I know this needs some work but am posting it for the sake of my question:

function deleteRow(r) {

if (confirm('Are you sure you want to delete this entry?')) {
    var i = r.parentNode.parentNode.rowIndex;
    document.getElementById("skuTable").deleteRow(i);
  }



    request = $.ajax({
      type: "POST",
      url: "delete.php",
      data: i
    });


        request.done(function (response, textStatus, jqXHR){
          if(JSON.parse(response) == true){
            console.log("row deleted");
          } else {
            console.log("row failed to delete");
          }
        });

        // Callback handler that will be called on failure
        request.fail(function (jqXHR, textStatus, errorThrown){
            // Log the error to the console
            console.error(
                "The following error occurred: "+
                textStatus, errorThrown
            );
        });

        // Callback handler that will be called regardless
        // if the request failed or succeeded
        request.always(function () {

        });


}

delete.php:

<?php

  $SKU_Group = $_POST['SKU Group'];
  $Group_ID = $_POST['Group_ID'];

  $host="xxxxxx"; 
  $dbName="xxxxxx"; 
  $dbUser="xxxxxxxxxxxxxx"; 
  $dbPass="xxxxxxxxxxx";

  $pdo = new PDO("sqlsrv:server=".$host.";Database=".$dbName, $dbUser, $dbPass);

  $delete = "DELETE FROM SKU_Group_Dim WHERE Group_ID = '$Group_ID'";

  $stmt = $pdo->prepare($delete);
  $result = $stmt->execute();
  echo json_encode($result);
  if(!$result) {
      echo json_encode(sqlsrv_errors());
  }

?>

Answer 1

JavaScript

First, I noticed you are using jQuery so why not try utilizing it to its full potential?

Start by creating an onclick event handler for your .delete buttons.

$('.delete').click(function () {
    // do something when delete button is clicked
});

You only want to delete the row after the user has confirmed AND it has been successfully deleted from the database.

if (confirm('Are you sure you want to delete this entry?')) {
    // shorter call for doing simple POST request
    $.post('delete.php', data, function (response) {
        // do something with response
    }, 'json');
    // ^ to indicate that the response will be of JSON format
}

But what data should be passed into the $.post() so that we know which record to delete? Well, probably the ID of the record that we want to delete.

HTML

As you have not posted much of the HTML, let's assume you built your table as below:

<table class="skuTable">
    <tr>
        <td>123</td><!-- sku group -->
        <td>456</td><!-- group id -->
        <td><input type="button" class="edit" name="edit" value="Edit" ... ></td>
        <td><input type="button" class="delete" name="delete" value="Delete" onclick="deleteRow(this)"></td>
    </tr>
    <!-- more similar records -->
</table>

Change it so that you can easily find and access the ID of the group such as by adding a class to your cells. (Since we have already created an onclick handler, you no longer need to use onclick attribute for your .delete buttons.)

<table class="skuTable">
    <tr>
        <td class="skuGroup">123</td>
        <td class="groupId">456</td>
        <td><input type="button" class="edit" name="edit" value="Edit" ... ></td>
        <td><input type="button" class="delete" value="Delete"></td>
    </tr>
    <!-- more similar records -->
</table>

JavaScript (again)

You can easily find the associated ID by traversing using jQuery. To put everything together now:

$('.delete').click(function () {
    var button = $(this), 
        tr = button.closest('tr');
    // find the ID stored in the .groupId cell
    var id = tr.find('td.groupId').text();
    console.log('clicked button with id', id);

    // your PHP script expects GROUP_ID so we need to pass it one
    var data = { GROUP_ID: id };

    // ask confirmation
    if (confirm('Are you sure you want to delete this entry?')) {
        console.log('sending request');
        // delete record only once user has confirmed
        $.post('delete.php', data, function (res) {
            console.log('received response', res);
            // we want to delete the table row only we received a response back saying that it worked
            if (res.status) {
                console.log('deleting TR');
                tr.remove();
            }
        }, 'json');
    }
});

PHP

One of the reasons people use prepared statements is to prevent attacks. It's good that you tried to use it, but you are not using it right (read Jay's comment). You want to bind the variable to a parameter in your SQL. You can do this by passing an array of the variables in the PDOStatement::execute() function.

When you delete a record, you check whether it worked by checking the number of records affected using PDOStatement::rowCount().

I've never had any reason to check whether execute() worked or not.

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

//$SKU_Group = $_POST['SKU Group'];

$Group_ID = $_POST['Group_ID'];

$host="xxxxxx"; 
$dbName="xxxxxx"; 
$dbUser="xxxxxxxxxxxxxx"; 
$dbPass="xxxxxxxxxxx";

$pdo = new PDO("sqlsrv:server=".$host.";Database=".$dbName, $dbUser, $dbPass);

$stmt = $pdo->prepare("DELETE FROM SKU_Group_Dim WHERE Group_ID = ?");
$stmt->execute(array($Group_ID));

// send back the number of records it affected
$status = $stmt->rowCount() > 0;

// send back a JSON 
echo json_encode(array('status' => $status));

// nothing else can be outputted after this
?>

Of course, this has not been tested so there may be few bugs. If you open up your browser's console log, you can follow the logs to see what is happening.