I can't delete a row / record in my database. PHP/MYSQLi

Question

I'm currently working on an hotel reservation system.

I'm specifically working on deleting a field in a test database i'm working on.

I can view (SELECT) the contents of the database and put it in a table, and I also want to delete it if I want to.

But I can't get it to work.

Code of the admin side / table.

    // get the records from the database
if ($result = $mysqli->query("SELECT * FROM guestdetails")) {
    // display records if there are records to display
    if ($result->num_rows > 0) {
    // display records in a table
    echo "<table class = \"table\">";

    // set table headers
    echo "<tr>
            <th>Guest ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Email</th>
            <th>Contact Number</th>
            <th>Extra Requests</th>
            <th>Gender</th>
            <th>Action</th>
        </tr>";

    while ($row = $result->fetch_object()) {
        // set up a row for each record
        echo "<tr>";
            echo "<td>" . $row->user_id . "</td>";
            echo "<td>" . $row->firstname . "</td>";
            echo "<td>" . $row->lastname . "</td>";
            echo "<td>" . $row->email . "</td>";
            echo "<td>" . $row->contactnumber . "</td>";
            echo "<td>" . $row->requestMessage . "</td>";
            echo "<td>" . $row->gender . "</td>";
            // edit function, under construction
            // echo "<td><a href='records.php?id=" . $row->id . "'>Edit</a></td>";
            echo "<td><a href='../guestdetails/deleteGuestDetails.php?id=" . $row->user_id . "'>Delete</a></td>";
        echo "</tr>";
    }

echo "</table>";

// if there are no records in the database, display an alert message
} else {
echo "No results to display!";
}

// show an error if there is an issue with the database query
} else {
echo "Error: " . $mysqli->error;
}

// close database connection
$mysqli->close();

This works quite well.

Here is the code for the delete.php

<?php

// connect to the database
include('dbConnect.php');

// confirm that the 'id' variable has been set
if (isset($_GET['user_id']) && is_numeric($_GET['user_id'])) {

// get the 'id' variable from the URL
$user_id = $_GET['user_id'];

// delete record from database
if ($stmt = $mysqli->prepare("DELETE FROM guestdetails WHERE user_id = ? LIMIT 1")) {
    $stmt->bind_param("i",$user_id);
    $stmt->execute();
    $stmt->close();

} else {
    echo "ERROR: could not prepare SQL statement.";
}

$mysqli->close();

// redirect user after delete is successful
header("Location: ../admin/mainADMINreserve.php");

// if the 'id' variable isn't set, redirect the user
} else {
    header("Location: ../admin/mainADMINreserve.php");
}

?>

I think the problem lies somewhere here in this code: (mainADMINreserve.php)

echo "<td><a href='../guestdetails/deleteGuestDetails.php?id=" . $row->user_id . "'>Delete</a></td>";

That it doesn't set in here: (delete.php)

if (isset($_GET['user_id']) && is_numeric($_GET['user_id'])) {

That's why I'm always redirected to the else part of the code in delete.php.

I've been studying for hours but I can't get it to work. By the way I got a scrap of this code from different websites, like this one here. It helped me a lot creating this but I'm having problems in the delete as stated. What seems to be the problem? Thank you very much in advance! ![enter image description here ] This is what it looks like in the table if anyone's curious. Sorry I had to hide the content but the point is I can view the fields I entered from my database. The problem lies in the delete button as stated in the code.

this is a typo question. You're using this GET array `$_GET['user_id']` yet using `?id` — Funk Forty Niner, Dec 11 '16 at 01:35
@noɥʇʎPʎzɐɹC thank you! :D it is a big help getting assistance in a community like this. — Ken Flake, Dec 11 '16 at 01:45
@Fred-ii- it actually is!! I edited the code and that was the problem. It seems I have so much more to learn. I also think it is because I'm working on this code for hours now and I think I'm a little bit exhausted. Thank you very much! Such a small mistake.. :) — Ken Flake, Dec 11 '16 at 01:46
It can be improved a bit - just read [mcve]. You're welcome! — noɥʇʎԀʎzɐɹƆ, Dec 11 '16 at 01:47
@Kenny error reporting http://php.net/manual/en/function.error-reporting.php would have helped ;-) — Funk Forty Niner, Dec 11 '16 at 02:07

Subramanya Vajiraya · Answer 1 · 2016-12-11T02:09:56.743

1

It should be <td><a href='../guestdetails/deleteGuestDetails.php?user_id=. parameter name and GET index name should match.

edited Dec 11 '16 at 02:09

answered Dec 11 '16 at 01:26

Subramanya Vajiraya

327
1
10

1

its perfectly valid, and often a good idea – Dec 11 '16 at 01:27
if the `user_id` is unique, there is no need for LIMIT. if it's not unique then a primary key should be used in DELETE query to avoid erratic behavior. – Subramanya Vajiraya Dec 11 '16 at 01:31
Thank you very much! `?user_id=` was the problem. :) – Ken Flake Dec 11 '16 at 01:47
@SubramanyaVajiraya Your comment about *"I don't think you're supposed to use LIMIT 1 in delete query."* should be removed from your answer; it's perfectly valid, the rest is fine. – Funk Forty Niner Dec 11 '16 at 02:08

I can't delete a row / record in my database. PHP/MYSQLi

1 Answers1