prepared delete statement is not doing anything

DeckDekk · May 28, 2020

Hello there!

I've got this code:

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
error_reporting(E_ALL);

if (isset($_POST["id"]) && !empty($_POST["id"])) {

    require_once "login.php";

    $stmt = $conn->prepare("DELETE FROM teamlid WHERE lidnummer = ?");
    $stmt->bind_param("i", $param_id);
    $param_id = $_POST["id"];
    $stmt->execute();

        if ($stmt) {
            header("location: teamsTabel.php");
            exit();
        } else {
            echo "Oeps, er is iets verkeerds gegaan. Probeer het later nog een keer.";
        }

    mysqli_close($conn);
}

?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>Gegevens inzien</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
    <style type="text/css">
        .wrapper {
            width: 500px;
            margin: 0 auto;
        }
    </style>
</head>

<body>
    <div class="wrapper">
        <div class="container-fluid">
            <div class="row">
                <div class="col-md-12">
                    <div class="page-header">
                        <h1>Gegevens verwijderen</h1>
                    </div>
                    <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
                        <div class="alert alert-danger fade in">
                            <input type="hidden" name="id" value="<?php echo trim($_POST["id"]); ?>" />
                            <p>Weet je zeker dat je deze gegevens wilt verwijderen?</p><br>
                            <p>
                                <input type="submit" name="submitDeleteBtn" value="Ja" class="btn btn-danger">
                                <a href="teamsTabel.php" class="btn btn-default">Nee</a>
                            </p>
                        </div>
                    </form>
                </div>
            </div>
        </div>
    </div>
</body>

</html>

Another PHP script I tried didn't work either:

<?php

if (isset($_POST["id"]) && !empty($_POST["id"])) {

    require_once "login.php";

    // DELETE query om op basis van lid.lidnummer en postcode.postcode de tabellen te legen. 
    $sql = "DELETE lid, postcode FROM lid INNER JOIN postcode WHERE lid.postcode = postcode.postcode AND lid.lidnummer = ?";

    if ($stmt = mysqli_prepare($conn, $sql)) {
        mysqli_stmt_bind_param($stmt, "s", $param_id);

        $param_id = trim($_POST["id"]);

        if (mysqli_stmt_execute($stmt)) {
            header("location: read.php");
            exit();
        } else {
            echo "Oeps, er is iets verkeerds gegaan. Probeer het later nog een keer.";
        }
    }

    mysqli_stmt_close($stmt);

    mysqli_close($conn);
} else {
    if (empty(trim($_GET["id"]))) {
        header("location: error.php");
        exit();
    }
}
?>

But they're both not doing what I want: deleting the rows in the "teamlid" table where the id matches the column "lidnummer".

I'm not getting any errors. When using the delete statement directly in MySQL using a hardcoded "lidnummer" it works. But it's not using the lidnummer here to delete the corresponding columns in the table.

I can't get my head around it. Do you guys have any ideas? Thanks!

Edited May 28, 2020 by DeckDekk

Barand · May 28, 2020

Can you post the structures of your "lid" and "postcode" tables for us.

DeckDekk · May 28, 2020

Thanks! This is the structure of "lid" and "postcode":

CREATE TABLE `lid` (
`lidnummer` smallint(6) NOT NULL,
`voornaam` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
`achternaam` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
`postcode` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
`huisnummer` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `postcode` (
`postcode` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
`adres` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL,
`woonplaats` varchar(128) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

requinix · May 28, 2020

if ($stmt) {

That will always be true.

Barand · May 28, 2020

~~I am wondering if the lack of primary keys in your tables is a contributing factor~~

Set up the tables and tried it - deletion worked when run manually.

mysql> insert into lid values
    -> (1, 'A', 'B','C','D'),
    -> (2, 'A', 'B','F','D');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into postcode values
    -> ('C', 'aaa', 'bbb'),
    -> ('F', 'ccc', 'ddd');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DELETE lid, postcode FROM lid INNER JOIN postcode WHERE lid.postcode = postcode.postcode AND lid.lidnummer = 2;
Query OK, 2 rows affected (0.05 sec)

mysql> select * from lid;
+-----------+----------+------------+----------+------------+
| lidnummer | voornaam | achternaam | postcode | huisnummer |
+-----------+----------+------------+----------+------------+
|         1 | A        | B          | C        | D          |
+-----------+----------+------------+----------+------------+
1 row in set (0.00 sec)

mysql> select * from postcode;
+----------+-------+------------+
| postcode | adres | woonplaats |
+----------+-------+------------+
| C        | aaa   | bbb        |
+----------+-------+------------+
1 row in set (0.00 sec)

Edited May 28, 2020 by Barand

Barand · May 28, 2020

Are you sure you have matching postcode values? No hidden whitespace.

And records that match the lidnummer?

Edited May 28, 2020 by Barand

mac_gyver · May 28, 2020

11 hours ago, DeckDekk said:

But they're both not doing what I want

what DOES the page do when you submit the form? does the page just refresh and re-display the form, produce a blank page, or does it redirect to one of - teamsTabel.php/read.php/error.php?

DeckDekk · May 29, 2020

9 hours ago, mac_gyver said:

what DOES the page do when you submit the form? does the page just refresh and re-display the form, produce a blank page, or does it redirect to one of - teamsTabel.php/read.php/error.php?

It redirects to the read.php page. There it shows all the current members of the "lid" table.

mac_gyver · May 29, 2020

that would indicate that $_POST['id'] isset() and isn't empty(), and that the php code ran through the execute() call without any error. the most likely problem is that the id value being submitted to this page of code isn't a value that matches any data. what is the code that produces the 'delete' form that submits to the posted code?

btw - is the 'confirmation' form in the posted code even being displayed? since the posted code operates on the initial submitted $_POST['id'] value from the 'delete' form, i doubt that any of the html in the posted code is even being output.

DeckDekk · May 29, 2020

42 minutes ago, mac_gyver said:

that would indicate that $_POST['id'] isset() and isn't empty(), and that the php code ran through the execute() call without any error. the most likely problem is that the id value being submitted to this page of code isn't a value that matches any data. what is the code that produces the 'delete' form that submits to the posted code?

btw - is the 'confirmation' form in the posted code even being displayed? since the posted code operates on the initial submitted $_POST['id'] value from the 'delete' form, i doubt that any of the html in the posted code is even being output.

You are probably right but from what I can tell the id value looks like the right data type ( the POST value for example is an int value like id=109). The corresponding column "lidnummer" takes smallint data values.

And the code that provides the link to this delete form is:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Boast & Drive</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.css">
    <style type="text/css">
        .wrapper{
            width: 650px;
            margin: 0 auto;
        }
        .page-header h2{
            margin-top: 0;
        }
        table tr td:last-child a{
            margin-right: 15px;
        }
    </style>
</head>
<body>
        <div class="container-fluid">
            <div class="row">
                <div class="col-md-12">
                    <div class="page-header clearfix">
                        <h2 class="pull-left">Teamleden</h2>
                        <div class="btn-toolbar">
                        <a href="read.php" class="btn btn-primary btn-lg pull-right">Terug</a>
                        </div>
                    </div>
                    
                    <?php
                    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
                    error_reporting(E_ALL);

                    //Verbinding maken met de database
                    require_once "login.php";

                    $sql = "SELECT tl.teamnaam,
                                   tl.tl_ID,
                                   tl.lidnummer,
                                   l.voornaam,
                                   l.achternaam
                            
                            FROM   teamlid tl
                            
                            JOIN    lid l   ON tl.lidnummer = l.lidnummer
                            
                            ORDER BY tl.teamnaam;";  
                    
                    if($result = mysqli_query($conn, $sql)) {
                        if(mysqli_num_rows($result) > 0) {
                            echo "<table class='table table-bordered table-striped'>";
                                echo "<thead>";
                                    echo "<tr>";
                                        echo "<th>Teamnaam</th>";
                                        echo "<th>Tl_ID</th>";
                                        echo "<th>Lidnummer</th>";
                                        echo "<th>Voornaam</th>";
                                        echo "<th>Achternaam</th>";
                                    echo "</tr>";
                                echo "</thead>";
                                echo "<tbody>";
                                while($row = mysqli_fetch_array($result)){
                                    echo "<tr>";
                                        echo "<td>" . $row['teamnaam'] . "</td>";
                                        echo "<td>" . $row['tl_ID'] . "</td>";
                                        echo "<td>" . $row['lidnummer'] . "</td>";
                                        echo "<td>" . $row['voornaam'] . "</td>";
                                        echo "<td>" . $row['achternaam'] . "</td>";
                                        echo "<td>";
                                            echo "<a href='update.php?id=". $row['lidnummer'] ."' title='Gegevens wijzigen' data-toggle='tooltip'><span class='glyphicon glyphicon-pencil'></span></a>";
                                            echo "<a href='deleteTeamLid.php?id=". $row['lidnummer'] ."' title='Lid verwijderen' data-toggle='tooltip'><span class='glyphicon glyphicon-trash'></span></a>";
                                        echo "</td>";                                        
                                    echo "</tr>";
                                }
                                echo "</tbody>";
                            echo "</table>";

                            mysqli_free_result($result);
                        } else{
                            echo "<p class='lead'><em>Er zijn geen gegevens om weer te geven.</em></p>";
                        }
                    } else{
                        echo "De volgende fout is gevonden:  " . mysqli_error($conn);
                    }
                    
                    ?>

                    
                    <form name="dropdown" method="post">
                        <div class="page-header clearfix">
                            <h2 class="pull-left">Teamlid toevoegen</h2>
                        </div>           
                        <p>Selecteer hieronder met behulp van het dropdown menu een lid welke je aan bovenstaand team wilt toevoegen</p>
                        
                        <div class="container-fluid">
                            <div class="row">

                            <?php
                            $teamnaam = '';
                            $lidnummer = '';

                            $sql = "SELECT voornaam, achternaam, lidnummer FROM lid ORDER BY achternaam";
                            $result = mysqli_query($conn, $sql);

                            echo "<select id='teamLid' name='teamLid'>";
                                echo "<option>--Selecteer Lid--</option>";
                            while ($row = mysqli_fetch_array($result)) {
                                echo "<option value='" . $row['lid'] . "'>" . $row['voornaam'] . "  " . $row['achternaam'] . " " . $row['lidnummer'] . "</option>";
                            }
                            echo "</select>";
                            
                            if 
                            (isset($_POST["id"]) && !empty($_POST["id"])) {
                                $id = $_POST["id"];

                                $stmt = $conn->prepare("INSERT INTO teamlid (teamnaam, lidnummer) VALUES (?,?)");
                                $stmt->bind_param('si', $param_teamnaam, $param_lidnummer);
                                $param_teamnaam         = $teamnaam;
                                $param_lidnummer        = $lidnummer;
                                $stmt->execute();

                            }

                            mysqli_close($conn);
                            ?>
                                <div>
                                    <input type="hidden" name="id" value="<?php echo $id; ?>" />
                                    <input type="submit" name="submit" class="btn btn-primary" value="Toevoegen">
                                </div>
                            </div>
                    </div>
                </form>
            </div>  
        </div>
</body>
</html

mac_gyver · May 29, 2020

40 minutes ago, DeckDekk said:

the code that provides the link to this delete form is

the form in the code at the top of this thread is not expecting a link to supply the value. it is expecting the value in - $_POST['id'] -

22 hours ago, DeckDekk said:

<input type="hidden" name="id" value="<?php echo trim($_POST["id"]); ?>" />

and the reason for the posted form processing code to see an isset/non-empty value is because it is probably a php error message about an undefined index.

DeckDekk · June 3, 2020

Fixed it! The only thing I did was change $_POST["id"] to $_GET["id"] in the form.

Thanks all for helping me with the thought proces : )

Sign In

prepared delete statement is not doing anything

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information