Delete record from database using AJAX

Question

I have a PHP file which displays data from a database. I'm using while() to display each of the records.

My PHP file:

<?php

    $flightTicketsSQL = "SELECT * FROM `flightbookings` WHERE username='$user' AND cancelled='no'";
    $flightTicketsQuery = $conn->query($flightTicketsSQL);

    while($flightTicketsRow = $flightTicketsQuery->fetch_assoc()) { ?>

    <tr>
        <td class="tableElementTags text-center"><?php echo $flightTicketsRow["bookingID"]; ?></td>
        <td class="tableElementTags text-center"><?php echo $flightTicketsRow["origin"]; ?></td>
        <td class="tableElementTags text-center"><?php echo $flightTicketsRow["destination"]; ?></td>
        <td class="tableElementTags text-center"><?php echo $flightTicketsRow["date"]; ?></td>
        <td class="tableElementTags text-center"><?php echo $flightTicketsRow["mode"]; ?></td>
        <td class="text-center"><span class="fa fa-remove tableElementTags pullSpan" id="deleteAccount"></span></td>
    </tr>

<?php } ?>

If the user clicks on the last <td> (the one with the Font Awesome icon), I want the record with the particular bookingID be deleted from the database.

To do this I'll need to identify the value using jQuery .val()

My JS file:

var id = $("**WHAT DO I PUT HERE ?**").val();

    $('#deleteAccount').click(function() {
        $.ajax({
            type: "POST",
            url: 'cancelTicket.php',
            data: { bookingID : id },
            success : function() {
                alert("Cancelled");
            }
        });
    });

My cancel.php file:

<?php

    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "database";

    $conn = new mysqli($servername, $username, $password, $dbname);

    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    $user = $_SESSION["username"];
    $id = $_POST["bookingID"];


    $cancelFlightBookingsSQL = "UPDATE `flightbookings` SET cancelled='yes' WHERE bookingID='$id'";
    $cancelFlightBookingsQuery = $conn->query($cancelFlightBookingsSQL);

My question is how do I make jQuery identify which booking the user wants to be cancelled? I mean how do I assign an id to the <td> so that I can retrieve its value in the JS.

I know I could not frame this question properly. SORRY ABOUT THAT.

Thanks

Answer 1

There's a couple of issues here. Firstly you need to use a class for the span instead of an id, otherwise it will be duplicated in the DOM by your PHP loop, which will result in invalid HMTL that will break your click event handler.

Once you've done that you need to set the id variable value within the click handler so that you can find the td related to the clicked element. You can do that like this:

<!-- repeated throughout your while loop -->
<td class="text-center">
  <span class="fa fa-remove tableElementTags pullSpan deleteAccount"></span>
</td>

$('.deleteAccount').click(function() {
  var id = $(this).closest('tr').find('td:first').text().trim();

  $.ajax({
    type: "POST",
    url: 'cancelTicket.php',
    data: {
      bookingID: id
    },
    success: function() {
      alert("Cancelled");
    }
  });
});