0

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());
  }

?>
15
  • Little Bobby says your script is at risk for SQL Injection Attacks.. Even escaping the string is not safe! Commented Feb 13, 2017 at 20:37
  • This looks like you're on the right track, what isn't working? Commented Feb 13, 2017 at 20:38
  • Your data variable in ajax doesn't have the parameters corresponding to your $_POST getters from the looks of things. Commented Feb 13, 2017 at 20:40
  • @LoveenDyall Yes I knew that was for sure a problem, I just didnt know what should go there Commented Feb 13, 2017 at 20:41
  • Whatever you name the $_POST parameters, you should url_encode the same parameter string. so data: "Group_ID="+i; or something similar Commented Feb 13, 2017 at 20:41

1 Answer 1

3

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.

Sign up to request clarification or add additional context in comments.

7 Comments

Thanks for the answer. I have the code in my script and on run, nothing is happening when I click the delete button. Any idea why?
I edited the first 2 lines of the JS to look like this and the confirmation box is now displayed... document.addEventListener('DOMContentLoaded', function () { document.getElementById("delete").addEventListener('click',function () { However, it now tells me that the received response status is now false
Follow the code properly. Your HTML buttons should each have a class attribute not an id attribute. The buttons share this class because they share the same onclick event handler. IDs are only meant for one element, not several. Follow the console logs to ensure that you are getting the right values at each breakpoint. If the response status is false, look at what you are passing to the AJAX.
Okay, I have followed your code exactly, however nothing happens when I click the delete button so there is nothing to look at in the logs or anything
Ensure that you have (1) included jQuery (2) wrapped your jQuery code in $(function() { ... }); (3) added class="delete" to your delete buttons (4) removed onclick attribute from your delete buttons.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.