How to create a delete button for each row in the table?

Question

I'm trying to write a PHP code that connects and get data cells from mySQL and also create a delete button for each row as it's increming as in mySQL database.

Please help me to figure out what am I doing wrong?!!

[where my table name is "vf" and its structure is like that]

ID     Password1     Password2

[And where PHP variables I used is]

$Connect=mysqli_connect($host,$user,$Pass,$db);
$QueryVF = mysqli_query($Connect,'SELECT * from vf');
$ID =$_POST['ID'];
$DelVF = mysqli_query($Connect,'DELETE from vf where ID =$ID');

/* ALSO TRIED This:  $DelVF = mysqli_query($Connect,'TRUNCATE vf'); */

[MY HTML & PHP code]

       
<html>
    <body>             
        <form method="POST">
            <table border="1" class=".input">              <!--vf Table-->
                        <tr>
                                <th>ID</th>
                                <th>Password 1</th>
                                <th>Password 2</th>
                                <th>Drop data</th>
                        </tr>
                            <?php
                                while ( $row = mysqli_fetch_array($QueryVF)){
                                echo'<tr>';
                                    echo'<td>'.$row['ID'].'</td>';
                                    echo'<td>'.$row['Password1'].'</td>';
                                    echo'<td>'.$row['Password2'].'</td>';
                                    echo "<td><button type=\"submit\" name=\"Delete\" oneclick=\"$DelVF\">Drop Data</button></td>";
                                        if($_POST['Delete']){
                                            while ($DelVF = mysqli_query($Connect,"'DELETE from vf where ID =$ID'") )
                                            $DelVF;
                                            echo "VF table's row is successfully deleted";
                                                                }
                                    echo'</tr>';
                              }
                             ?>
                        </table>
        </form>
    </body>
</html>

phpMyAdmin is not a database. You're getting data from mySQL, not phpMyAdmin. phpMyAdmin is just an application (one of several available) you can use to manage a mySQL server. — ADyson, Jun 26 '21 at 06:51
Anyway your code appears to try and delete the whole table, not a specific row. Also it's liable to run multiple times, not once, since you're looping it. Move that code which handles the delete outside the loop, and also make sure the form submits the ID of the row you want to delete so you can write a DELETE query with a suitable WHERE clause and pass the row ID as a parameter. Pretty sure you could find lots of examples of the basic concept of this online already if you search, it's a common requirement — ADyson, Jun 26 '21 at 06:55
P.p.s please read tag descriptions before you use them - the "processing" tag refers to a specific tool, not general processing. I'll remove that and the phpMyAdmin tag for you. Please also take the tour so you understand fully how the site works - https://stackoverflow.com/tour — ADyson, Jun 26 '21 at 06:56
As a side note, your code is vulnerable to SQL Injections because user variables are not escaped properly. Never test your code in production. Read more: https://www.php.net/manual/en/security.database.sql-injection.php — Valerio Bozz, Jun 26 '21 at 09:40

score 2 · Answer 1 · answered Jun 26 '21 at 08:45

As @ADyson shared in their comments, there are a few things to address apart from the confusion between mysql and phpmyadmin.

To delete a row, you need to use the DELETE mysql statement. TRUNCATE is wrong as it deletes all rows from a table. Read the official documentation for more info.
Move the management of POST data outside the while loop, otherwise you will run that code multiple times
You're not passing the ID via POST in any way. A simple way to do this, is to add the value attribute to the submit button, like this:

echo '<button type="submit" name="Delete" value="'.$row['ID'].'">Drop Data</button>';

Overall please see below the full code with comments to guide you to understand why the changes were necessary:

// Manage the POST data at the top of your file
if (isset($_POST['Delete'])) {
    // If you receive the Delete post data, delete it from your table
    $delete = 'DELETE FROM vf WHERE ID = ?';
    $stmt = $Connect->prepare($delete);
    $stmt->bind_param("i", $_POST['Delete']);
    $stmt->execute();
}
// Run your select AFTER the DELETE, so that you will get the updated table
$QueryVF = mysqli_query($Connect, 'SELECT * from vf');

?>

<html>
    <body>             
        <form method="POST">
            <table border="1">              
                <tr>
                        <th>ID</th>
                        <th>Password 1</th>
                        <th>Password 2</th>
                        <th>Drop data</th>
                </tr>
                    <?php
                    while ($row = mysqli_fetch_array($QueryVF)) {
                        echo'<tr>';
                        echo'<td>'.$row['ID'].'</td>';
                        echo'<td>'.$row['Password1'].'</td>';
                        echo'<td>'.$row['Password2'].'</td>';
                        // Add the ID to the value attribute so that it gets passed via POST
                        echo '<td><button type="submit" name="Delete" value="'.$row['ID'].'">Drop Data</button></td>';
                        echo'</tr>';
                    }
                    ?>
            </table>
        </form>
    </body>
</html>

Thanks a lot, it works but I'm still trying to learn how it works. — ubuntu-user, Jun 26 '21 at 09:50
It works perfectly. However `mysqli_query($Connect, "delete from vf where ID='$_POST[Delete]'") or die(mysqli_error($Connect));` also works. — Sadikul Haque Sadi, Mar 20 '22 at 11:55
@SadikulHaqueSadi no it doesn't. That code makes your application widely open to SQL Injection. [Read here](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Andrea Olivato, Mar 21 '22 at 01:16

How to create a delete button for each row in the table?

1 Answers1