PHP how to delete a specific row from database with html table button

Question

I am trying to delete a specific entry from the database with a button. I know this has already been asked several times, unfortunately the solutions don't really work for me. The goal would be, if I click on the button in the 3rd row, that this line is deleted. I have the problem that I always only delete the last ids or all ids at once.

Maybe someone can help me, thank you.

admin.php

<?php
include('connection.php');
include('read.php');
?>

<form action="admin.php" method="post">
            <div class="table-wrapper">
                <div class="table-scroll">
                    <table id="myTable">
                        <tr>
                            <th>ID</th>
                            <th>Kartentyp</th>
                            <th>Absender</th>
                            <th>Empfänger</th>
                            <th>Sendedatum</th>
                            <th id="smallCol">Verschickt</th>
                            <th id="smallCol">Bestätigung</th>
                            <th>Edit</th>
                        </tr>

                        <?php
                        foreach ($result as $row) {
                            if ($row['Dispatched'] == 0) {
                                $dispatched = 'Pending';
                            } else {
                                $dispatched = 'Versendet';
                            }
                            ?>
                            <tr class="Alle <?php echo $row['Category']; ?> <?php echo $row['Dispatched']; ?>">
                                <td><?php echo $row['ID']; ?></td>
                                <td><?php echo $row['Category']; ?></td>
                                <td><?php echo $row['Sender']; ?></td>
                                <td><?php echo $row['Receiver']; ?></td>
                                <td><?php echo $row['SendDate']; ?></td>
                                <td><?php echo $dispatched; ?></td>
                                <td>Placeholder</td>
                                <td><input type="submit" name="delete" value="delete" >
                                    <button data-target="modal1" class="modal-trigger">Modal</button>
                                </td>
                            </tr>
                            <?php

                        }
                        if (isset($_POST['delete'])) {
                            echo $row['ID'];

                            $deleteQuery = "DELETE FROM card WHERE id = " . $row['ID'];
                            $statement = $pdo->prepare($deleteQuery);
                            $statement->execute();
                        }
                        ?>
                    </table>
                </div>
            </div>
        </form>

read.php

<?php
include('connection.php');

$statement = $pdo->prepare("SELECT * FROM card ORDER BY ID ASC");
$statement->execute();
$result = $statement->fetchAll();
if ($statement->rowCount() > 0) {
    foreach ($statement->fetchAll() as $row) {
        $id = $row['ID'];
        $imagePath = $row["ImagePath"];
        $sender = $row["Sender"];
        $senderEmail = $row["SenderEmail"];
        $receiver = $row["Receiver"];
        $receiverEmail = $row["ReceiverEmail"];
        $subject = $row["Subject"];
        $text = $row["Text"];
        $sendDate = $row["SendDate"];
        $dispatched = $row["Dispatched"];
        $category = $row['Category'];

    }
}
?>

Answer 1

You can modify your code like that :

<?php
include('connection.php');
include('read.php');

if( !empty($_POST) ){

    foreach( $_POST as $key_post => $value_post ){


        if (preg_match('/delete_/i',$key_post) ) {

            $id_to_delete = (integer) str_replace('delete_','', $key_post);

            $deleteQuery = "DELETE FROM card WHERE id = " . $id_to_delete;
            $statement = $pdo->prepare($deleteQuery);
            $statement->execute();
        }
    }
}   

?>

<form action="admin.php" method="post">
            <div class="table-wrapper">
                <div class="table-scroll">
                    <table id="myTable">
                        <tr>
                            <th>ID</th>
                            <th>Kartentyp</th>
                            <th>Absender</th>
                            <th>Empfänger</th>
                            <th>Sendedatum</th>
                            <th id="smallCol">Verschickt</th>
                            <th id="smallCol">Bestätigung</th>
                            <th>Edit</th>
                        </tr>

                        <?php
                        foreach ($result as $row) {
                            if ($row['Dispatched'] == 0) {
                                $dispatched = 'Pending';
                            } else {
                                $dispatched = 'Versendet';
                            }
                            ?>
                            <tr class="Alle <?php echo $row['Category']; ?> <?php echo $row['Dispatched']; ?>">
                                <td><?php echo $row['ID']; ?></td>
                                <td><?php echo $row['Category']; ?></td>
                                <td><?php echo $row['Sender']; ?></td>
                                <td><?php echo $row['Receiver']; ?></td>
                                <td><?php echo $row['SendDate']; ?></td>
                                <td><?php echo $dispatched; ?></td>
                                <td>Placeholder</td>
                                <td><input type="submit" name="delete_<?php echo $row['ID']; ?>" value="delete" >
                                    <button data-target="modal1" class="modal-trigger">Modal</button>
                                </td>
                            </tr>
                            <?php

                        }
                        ?>
                    </table>
                </div>
            </div>
        </form>

Your error was you don't used the POST value to delete the row but the last ID store in the row variable that come from your query reading.

Becarfull too you make a wrong usage of the prepare function in PDO.

Answer 2

You can archive this by using get request without posting whole data to server.

    if( !empty($_GET['id']) ){
          $deleteQuery = "DELETE FROM card WHERE id = " . $id_to_delete;
          $statement = $pdo->prepare($deleteQuery);
          $statement->execute();
          header('location:youfilename.php');
          exit;

    }   

?>
                <div class="table-wrapper">
                    <div class="table-scroll">
                        <table id="myTable">
                            <tr>
                                <th>ID</th>
                                <th>Kartentyp</th>
                                <th>Absender</th>
                                <th>Empfänger</th>
                                <th>Sendedatum</th>
                                <th id="smallCol">Verschickt</th>
                                <th id="smallCol">Bestätigung</th>
                                <th>Edit</th>
                            </tr>

                            <?php
                            foreach ($result as $row) {
                                if ($row['Dispatched'] == 0) {
                                    $dispatched = 'Pending';
                                } else {
                                    $dispatched = 'Versendet';
                                }
                                ?>
                                <tr class="Alle <?php echo $row['Category']; ?> <?php echo $row['Dispatched']; ?>">
                                    <td><?php echo $row['ID']; ?></td>
                                    <td><?php echo $row['Category']; ?></td>
                                    <td><?php echo $row['Sender']; ?></td>
                                    <td><?php echo $row['Receiver']; ?></td>
                                    <td><?php echo $row['SendDate']; ?></td>
                                    <td><?php echo $dispatched; ?></td>
                                    <td>Placeholder</td>
                                    <td><a href="yourfilename.php?id=<?php echo $row['ID']; ?>">Delete</a>
                                        <button data-target="modal1" class="modal-trigger">Modal</button>
                                    </td>
                                </tr>
                                <?php

                            }
                            ?>
                        </table>
                    </div>
                </div>
            </form>