Adding a delete button in PHP on each row of a MySQL table

Question

I am trying to add a delete button on each row so that I can delete a record when the button is pressed. I am new to PHP and MySQL and Stack Overflow.

Below is my table which extract information from my MySQL database and that works.

       <table class="table" >
       <tr>
       <th> Staff ID </th>
       <th> Staff Name </th>
       <th> Class </th>
       <th> Action </th>

       </tr>   

       <?php

       while($book = mysqli_fetch_assoc($records)){

       echo "<tr>";
       echo "<td>".$book['Staff_ID']."</td>";
       echo "<td>".$book['Staff_Name']."</td>";
       echo "<td>".$book['Class']."</td>";
       echo "</tr>";
       }// end while loop

http://stackoverflow.com/questions/43281598/how-to-delete-a-specific-row-in-a-table-using-javascript#comment73631786_43281598 — Matt, Apr 07 '17 at 20:13

BetaDev · Accepted Answer · 2017-04-07T22:06:39.550

8

Simply using PHP as follows (You can use JS)

while($book = mysqli_fetch_assoc($records)){

echo "<tr>";
echo "<td>".$book['Staff_ID']."</td>";
echo "<td>".$book['Staff_Name']."</td>";
echo "<td>".$book['Class']."</td>";
echo "<td><a href='delete.php?id=".$book['Staff_ID']."'></a></td>"; //if you want to delete based on staff_id
echo "</tr>";
}// end while loop

In your delete.php file,

$id = $_GET['id'];
//Connect DB
//Create query based on the ID passed from you table
//query : delete where Staff_id = $id
// on success delete : redirect the page to original page using header() method
$dbname = "your_dbname";
$conn = mysqli_connect("localhost", "usernname", "password", $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// sql to delete a record
$sql = "DELETE FROM Bookings WHERE Staff_ID = $id"; 

if (mysqli_query($conn, $sql)) {
    mysqli_close($conn);
    header('Location: book.php'); //If book.php is your main page where you list your all records
    exit;
} else {
    echo "Error deleting record";
}

edited Apr 07 '17 at 22:06

answered Apr 07 '17 at 20:19

BetaDev

4,516
3
21
47

1

Thank you very much, this has worked :) Wish mu university tutors respond like this! cheers:) – Hamzah Amir Apr 07 '17 at 20:27
@webDev am I doing something wrong here? sorry about this – Hamzah Amir Apr 07 '17 at 21:59
see updated code and you dont need to require book.php @HamzahAmir – BetaDev Apr 07 '17 at 22:08
1

THANK YOU AGIAN SOLVED IT!:) – Hamzah Amir Apr 07 '17 at 23:14
1

This is simple but powerful!! – Wachaga Mwaura Jul 01 '17 at 02:56
is it safe? what if someone put "delete.php?id=xx" in url? isn't it going to be sql injection friendly? – judy Feb 22 '18 at 12:55
yes you are right @judy but as poster seems like in beginner stage thats next step is to explore about prepared statements and input filtering (escaping) etc. – BetaDev Feb 22 '18 at 17:41

score 2 · Answer 2 · edited Apr 07 '17 at 20:21

Create a delete.php file that receives a $_GET['id'], then runs sql to delete that record when they go to that page. Done via two ways: an anchor tag like I've shown below,

Or make a button instead of an anchor runs ajax (through jquery) sending that id and running the the delete.php script from above I mentioned.

table class="table" >
       <tr>
       <th> Staff ID </th>
       <th> Staff Name </th>
       <th> Class </th>
       <th> Action </th>

       </tr>   

       <?php

       while($book = mysqli_fetch_assoc($records)){

       echo "<tr>";
       echo "<td>".$book['Staff_ID']."</td>";
       echo "<td>".$book['Staff_Name']."</td>";
       echo "<td>".$book['Class']."</td>";
       echo "<td><a href='delete.php?id=".$book['Staff_ID']."'>Delete</a></td>";
       echo "</tr>";
       }// end while loop

score 0 · Answer 3 · edited Apr 09 '17 at 08:18

I would recommend you to use Ajax to make a call, something like @webDev but instead of calling the PHP, call a Javascript with an AjaxCall, and then, use JS to hide/delete the row in question, that way, the user didn't have to reload the whole page.

You can complement the answer you select as correct with this code instead of the href:

echo   '<td><button onclick="deleteRow('.$book['Staff_ID'].')">Delete</button></td>';

And add the following function in the <head> section:

<script>
    function deleteRow(StaffID){
        var xhttp = new XMLHttpRequest();
        xhttp.onreadystatechange = function() {                     

        if (xhttp.readyState == 4 && xhttp.status == 200) {
                  alert("Deleted!");
            }
        };
        document.getElementById("table").deleteRow(x);
        xhttp.open("GET", "delete.php", true);
        xhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        xhttp.send("id="+StaffID);
        }       
</script>

Yes I know, but the user who posted the question is beginner, and for beginner its better to have simple answer as much as we can. While learning its better to understand using PHP first — BetaDev, Apr 07 '17 at 20:24

Adding a delete button in PHP on each row of a MySQL table

3 Answers3

Linked