Jump to content

prepared delete statement is not doing anything


DeckDekk

Recommended Posts

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 by DeckDekk
Link to comment
Share on other sites

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;

Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.