My SQL Update query won't update the specified record

Question

I'm making a system where you can assign guests to certain rooms. To unassign a guest, two UPDATE queries must be executed in two different tables, but only one of these is actually executing. The one that doesn't work, doesn't put out an error message, but doesn't seem to do anything.

I've already printed out all the variables I'm using in my query to check for mistakes, but I haven't found any data in those which was wrong. I also checked if there were errors in my query using phpMyAdmin, but they properly executed there. I used the same type of method on another page, updating something else, and that worked. Could someone take a look in my code and tell me what I've done wrong?

Thanks in advance!

if(isset($_GET['view'])) {
    $viewid = $_GET['view'];
    $sql = "SELECT * FROM renterinfo LEFT JOIN apartments ON apartments.renterid = renterinfo.renterid WHERE renterinfo.renterid = $viewid";
    $xresult = mysqli_query($conn, $sql);
    $xrow = mysqli_fetch_array($xresult);
}

$freeroomquery = "SELECT * FROM apartments WHERE status = 1 ORDER BY apartmentno ASC";
$freerooms = mysqli_query($conn, $freeroomquery);

$rentername = $xrow['name'];
$renteremail = $xrow['email'];
$roomno = $xrow['apartmentno'];
$enddate = $xrow['leaveroom'];

if(isset($_POST['submit'])){    

        //------------------------------------

        $name = $_POST['newNaam'];
        $email = $_POST['newEmail'];
        $room = $_POST['newKamer'];
        $leaveroom = $_POST['newEinde'];

        $insertSql = "UPDATE renterinfo SET name = '$name', email = '$email', leaveroom = '$leaveroom'";
        $updateRoom = "UPDATE apartments SET renterid = NULL WHERE apartmentno = '$roomno'";

The second UPDATE query doesn't update the renterid. The first UPDATE query does work.





        if ($conn->query($insertSql) === TRUE) {
            $success = "Huurder <strong>&#39;" . $name . " - CobbenCampus&#39;</strong> is succesvol toegevoegd.";
        } else {
            $error = "Er is een fout opgetreden bij het toevoegen van appartement <strong>&#39;" . $apartmentno . " - CobbenCampus&#39;</strong>. Zijn alle velden ingevuld?<br><strong>Foutmelding:</strong> " . $sql . "<br>" . $conn->error;
        }

        if ($conn->query($updateRoom) === TRUE) {
            echo "Succeeded.";
        } else {
            echo "Query unsuccessfull.";
        }

        $conn->close();
}

HTML-code:


<form action="viewrenter.php" method="POST" enctype="multipart/form-data">

                <label id="first">Voor- en achternaam huurder:</label><br>
                <input type="text" name="newNaam" value="<?php echo $rentername ?>"><br><br>

                <hr class="line-black">

                <label id="first">E-mailadres van huurder:</label><br>
                <input type="text" name="newEmail" value="<?php echo $renteremail ?>"><br><br>

                <hr class="line-black">     

                <label id="first">Kamernummer:</label><br/>
                <p>Een kamer toewijzen aan een huurder, zal de kamer automatisch op bezet zetten.</p>
                <select name="newKamer">
                    <option value="<?php echo $roomno ?>"><?php echo $roomno ?></option>
                    <option value="0">Geen kamer</option>
                    <?php

                        while($rooms = mysqli_fetch_array($freerooms)) {
                            echo "<option value='" . $rooms['apartmentno'] . "'>" . $rooms['apartmentno'] . "</option>";
                        }

                    ?>

                </select><br><br>

                <hr class="line-black">

                <label id="first">Afloopdatum huurdersovereenkomst:</label><br>
                <input type="text" name="newEinde" placeholder="Als: 25-04-2019" value="<?php echo $enddate ?>"><br><br>

                <button class="spaced btn btn-primary btn-wide" type="submit" name="submit">Voeg huurder toe</button>
                <hr class="bigspacer">
            </form>

Hi ! I don't think "Please debug my code" is a good way of asking on stackoverflow. This might be a reason why you get downvotes. You should consider reading https://stackoverflow.com/help/how-to-ask and edit your question — Matthias Beaupère, May 09 '19 at 09:46
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 09 '19 at 11:27

score -1 · Answer 1 · edited May 09 '19 at 10:31

-1

UPDATE apartments SET `renterid` = NULL WHERE `apartmentno` = `$roomno`

Write query like this. I think it will work.

edited May 09 '19 at 10:31

Matthias Beaupère

1,731
2
17
44

answered May 09 '19 at 09:45

Dhanesha

1
1

Why do you think this will work? – Jay Blanchard May 09 '19 at 11:26

My SQL Update query won't update the specified record

1 Answers1