Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Łukasz Czuliński
Łukasz Czuliński
8,646 Points
Posted by Łukasz Czuliński
Łukasz Czuliński
8,646 Points

Deleting individual SQL table rows via PHP.

I'm working on a single-page web application which populates and displays a database via form.

I'm trying to figure out the best way to create a delete button on each table row which the user can click to delete the selected row in SQL.

Do I use some variation of DELETE FROM ___ WHERE here? I'm not sure how to utilize that in this spot (or if there's a better method). I'm stumped on how to assign the selected row in PHP to the same row in SQL.

Here's how I'm displaying the table, with the desired delete button as the final <td>. Would something like creating a form for each <tr> work? I tried doing that with the intention of matching the <td> values of the form/<tr> on the process page with the same row in SQL, but for some reason the form wasn't being created. I also have the feeling that's not the most efficient way of doing this.

<?php
        $query = "SELECT * FROM tally ORDER BY tdate DESC";
        $tabledata = mysqli_query($con, $query);
        while($row = mysqli_fetch_array($tabledata)){
          echo '<tr>
            <td class="tdate">'.$row['tdate'].'</td>
            <td class="player">'.$row['player'].'</td>
            <td class="tinfo">'.$row['tinfo'].'</td>
            <td class="stake">'.$row['entry'].'</td>
            <td class="result">'.$row['result'].'</td>
            <td><button class="deletedata">Del</button></td>
          </tr>';
        } ?>

3 Answers

Robert Bojor
PLUS
Robert Bojor
Courses Plus Student 29,439 Points
Robert Bojor
Robert Bojor
Courses Plus Student 29,439 Points

Hi Lukasz! Here's the AJAX implementation of your problem.

Please keep in mind that this implementation is assuming you already have jQuery loaded in the document and also is using the same document as the controller and the view at the same time. I usually have the PHP logic in the controller, the view has the table with data and a separate JavaScript file for whatever JS I need in the document.

// Delete logic
if (isset($_POST['action']) && $_POST['action'] == 'deleteEntry') {
    $id = isset($_POST['id']) ? intval($_POST['id']) : 0;
    if ($id > 0) {
        $query = "DELETE FROM tally WHERE id=".$id." LIMIT 1";
        $result = mysqli_query($con, $query);
        echo 'ok';
    } else {
        echo 'err';
    }
    exit; // finish execution since we only need the "ok" or "err" answers from the server.
}


// Your initial logic
$query = "SELECT * FROM tally ORDER BY tdate DESC";
$tabledata = mysqli_query($con, $query);
while($row = mysqli_fetch_array($tabledata)){
  echo '<tr>
    <td class="tdate">'.$row['tdate'].'</td>
    <td class="player">'.$row['player'].'</td>
    <td class="tinfo">'.$row['tinfo'].'</td>
    <td class="stake">'.$row['entry'].'</td>
    <td class="result">'.$row['result'].'</td>
    <td><button class="deletedata" data-id="'.$row['id'].'">Del</button></td>
  </tr>';
}

and the JavaScript that handles the clicks for the buttons in the table

var currentRow,
    id;
$(document).on('click','.deletedata',function(){
    id = $(this).attr('data-id'); // Get the clicked id for deletion 
    currentRow = $(this).closest('tr'); // Get a reference to the row that has the button we clicked
    $.ajax({
        type:'post',
        url:location.pathname, // sending the request to the same page we're on right now
        data:{'action':'deleteEntry','id':id},
        success:function(response){
            if (response == 'ok') {
                // Hide the row nicely and remove it from the DOM once the animation is finished.
                currentRow.slideUp(500,function(){
                    currentRow.remove();
                })
            } else {
                // throw an error modally to let the user know there was an error
            }
        }
    })
})

I would also like to recommend you use some sort of confirmation before executing the $.ajax bit. This way a user can reconsider before deleting a row.

Łukasz Czuliński
Łukasz Czuliński
8,646 Points
Łukasz Czuliński
Łukasz Czuliński
8,646 Points

Thanks Robert! Amazing timing since you just inadvertently answered the question I asked in Mike's answer (getting the SQL key id to apply to the current row).

AJAX is a bit above my limited knowledge at this point, but I appreciate the excellent answer and help.

Mike Baxter
Mike Baxter
4,442 Points
Mike Baxter
Mike Baxter
4,442 Points

Nice code, Robert! AJAX is a much nicer solution. (I've been putting off learning AJAX in favor of focusing on iOS stuff, but this looks so simple that I think I'll go back and study your code.)

Łukasz Czuliński
Łukasz Czuliński
8,646 Points
Łukasz Czuliński
Łukasz Czuliński
8,646 Points

2 months down the road and I'm now using AJAX. Thanks Robert! I've implemented your solution in my app and actually understand it now.

Mike Baxter
Mike Baxter
4,442 Points
Mike Baxter
Mike Baxter
4,442 Points

You either need to wrap each delete button inside of a form, or do something tricky with JavaScript or AJAX loading.

Assuming you want the simple forms, you can recall that there's a "hidden" input type you can use:

<form action="whatever-the-file-is-to-process-this.php" method="post">
    <input type="hidden" value="13" name="row-id">
    <input type="submit" value="Delete">
</form>

Your PHP is going to look something like this:

<?php

if ($_SERVER['REQUEST_METHOD'] == 'POST') {

    if (isset($_POST['row-id'])) {

        $rowToDelete = intval($_POST['row-id']);

        $query = "DELETE FROM tally WHERE id=" . $rowToDelete . " LIMIT 1"; // Or whatever your primary key is for the row, in my case "id". LIMIT 1 kind of gives added assurance that it won't delete tons of stuff if you make a mistake.

        $result = mysqli_query($con, $query);

        // Send the user back to the first page so they don't have that annoying pop-up if they hit the refresh button after deleting something.
        header('Location: http://teamtreehouse.com'); // Obviously, replace with the location of the page that you need it to redirect to.
    }
}
?>

Be sure to sanitize any input you get from your forms, even if it's a hidden input.

Łukasz Czuliński
Łukasz Czuliński
8,646 Points
Łukasz Czuliński
Łukasz Czuliński
8,646 Points

Thanks for the reply. This looks to be exactly like what I'm looking for.

I'm a but unsure of the name = row-id on my hidden input, though. How do I make this value always equal the primary key of my SQL table?

Rodger Voelkel
Rodger Voelkel
21,736 Points
Rodger Voelkel
Rodger Voelkel
21,736 Points

Your already defining that in your statement.

$row = mysqli_fetch_array($tabledata)

This is setting a $row variable for each record. You would just need to create this form in the PHP script that table is being generated on and it will create a form for each row with an ID value matching the record ID in your database. Something like...

<?php
        $query = "SELECT * FROM tally ORDER BY tdate DESC";
        $tabledata = mysqli_query($con, $query);
        while($row = mysqli_fetch_array($tabledata)){
          echo '<tr>
            <td class="tdate">'.$row['tdate'].'</td>
            <td class="player">'.$row['player'].'</td>
            <td class="tinfo">'.$row['tinfo'].'</td>
            <td class="stake">'.$row['entry'].'</td>
            <td class="result">'.$row['result'].'</td>
            <td>
                 <form action="whatever-the-file-is-to-process-this.php" method="post">
                      <input type="hidden" value="'.$row['id'].'" name="id">
                      <input type="submit" value="Delete">
                 </form>
            </td>
          </tr>';
        } ?>
``