How to add delete button to each row of php results table

Question

I have found several threads on stackOverflow that address how to add a 'delete' button to PHP results tables, but none seem to address my specific use case.

I have a PHP results table pulled from a MySql database. I have added a 'delete' link to each row of the table, but when I click the 'delete' link on a given row it deletes all rows in the table.

I suspect that I am implementing the 'delete' link improperly, but I am not adept enough with PHP to figure out what exactly is wrong.

Below is the code that generates the table. Note the 'delete' link is echoed in the endChildren() function.

        <?php
        echo "<table style='border: solid 1px black;'>";
        echo "<tr><th>Id</th><th>Name</th><th>Number</th><th>Part A</th><th>Part B</th><th>Full Name</th><th>Address</th><th>Apt.</th><th>City</th><th>State</th><th>Zip</th><th>Remove</th></tr>";

        class TableRows extends RecursiveIteratorIterator { 
            function __construct($it) { 
                parent::__construct($it, self::LEAVES_ONLY); 
            }

            function current() {
                return "<td>" . parent::current(). "</td>";
            }

            function beginChildren() { 
                echo "<tr>"; 
            } 

            function endChildren() { 
                echo "<td><a href='delete.php?id=".$row['id']."'>Delete></a></td>";
                echo "</tr>" . "\n";
            } 
        } 


        $servername = "localhost:3306";
        $username = "xxxxxxxxxx";
        $password = "xxxxxxxx";
        $dbname = "xxxxxxxxx";

        try {
            $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $stmt = $conn->prepare("SELECT id, Name, Number, PartA, PartB, Full_Name, Address, Apt, City, State, Zip FROM Medicard"); 
            $stmt->execute();

            // set the resulting array to associative
            $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); 

            foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) { 
                echo $v;
            }

        }
        catch(PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
        $conn = null;
        echo "</table>";
        ?> 

Here is the delete.php script:

    <?php
    $servername = "localhost:3306";
    $username = "xxxxxxxx";
    $password = "xxxxxxxx";
    $dbname = "xxxxxxxx";

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

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

        // use exec() because no results are returned
        $conn->exec($sql);
        echo "Record deleted successfully";
        }
    catch(PDOException $e)
        {
        echo $sql . "<br>" . $e->getMessage();
        }

    $conn = null;
    ?>            

When I click on the delete link in a given row it deletes all rows in the table. I am not sure if this is because of where I added the 'delete' link, or if my delete.php code is incorrect.

I am relatively new to PHP and MySql. Any guidance would be appreciated. Thanks.

Answer 1

You delete all rows where the column id matches the column id - which is of course the case for all rows.

$sql = "DELETE FROM Medicard WHERE id=id";

You need to read the ID from the GET parameter and adjust your query:

  <?php
    $servername = "localhost:3306";
    $username = "xxxxxxxx";
    $password = "xxxxxxxx";
    $dbname = "xxxxxxxx";

    $id = $_GET["id"];

    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        // sql to delete a record
        $sql = $conn->prepare("DELETE FROM Medicard WHERE id = ?");
        $success = $sql->execute(array($id));

        echo $success ? "Record deleted successfully" : "Record not deleted";
        }
    catch(PDOException $e)
        {
        echo $e->getMessage();
        }

    $conn = null;
    ?>    

The table generation part (I replaced the iterator you implemented by a simple loop)

<?php
        echo "<table style='border: solid 1px black;'>";
        echo "<tr><th>Id</th><th>Name</th><th>Number</th><th>Part A</th><th>Part B</th><th>Full Name</th><th>Address</th><th>Apt.</th><th>City</th><th>State</th><th>Zip</th><th>Remove</th></tr>";

        $servername = "localhost:3306";
        $username = "xxxxxxxxxx";
        $password = "xxxxxxxx";
        $dbname = "xxxxxxxxx";

        try {
            $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
            $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $stmt = $conn->prepare("SELECT id, Name, Number, PartA, PartB, Full_Name, Address, Apt, City, State, Zip FROM Medicard"); 
            $stmt->execute();

            // set the resulting array to associative
            $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

            foreach ($stmt->fetchAll() as $row) {
                echo "<tr>"; 
                foreach ($row as $val) {
                    echo "<td>" . $val . "</td>";
                }
                echo "<td><a href='delete.php?id=".$row['id']."'>Delete></a></td>";
                echo "</tr>";
            } 

        }
        catch(PDOException $e) {
            echo "Error: " . $e->getMessage();
        }
        $conn = null;
        echo "</table>";
?>