MySqli update is taking a while to load/timing out completely after submitting

Question

I'm allowing the logged in user to change the rsvp status via a dropdown on their profile page. When they submit the change, it's taking a while to update the field and sometimes times out. I suspect that I'm causing this with the way it's coded to do the database update but can't figure it out.

<?php
    include_once 'header.php';
    require_once 'includes/dbh.inc.php';
    require_once 'includes/functions.inc.php';

    if(isset($_SESSION["emailAddress"])) {
      $sql = "SELECT * FROM users WHERE email='$_SESSION[emailAddress]'";
      $stmt = mysqli_prepare($conn, $sql);
      mysqli_stmt_execute($stmt);
      $result = mysqli_stmt_get_result($stmt);
      $row = mysqli_fetch_assoc($result);
      mysqli_stmt_close($stmt);
      $inGet = "SELECT * FROM users WHERE rsvp='in';";
      $inData = mysqli_query($conn, $inGet);
      $inTotal = mysqli_num_rows($inData);
        if(isset($_POST['apply'])) {
        $rsvp = $_POST['status'];
        $email = $_SESSION['emailAddress'];
        $firstName = $row['firstName'];
        $lastName = $row['lastName'];
        do {
        $sql2 = "UPDATE users SET rsvp='$rsvp' WHERE email='$_SESSION[emailAddress]';";
        $stmt2 = mysqli_prepare($conn, $sql2);
        mysqli_stmt_execute($stmt2);
        mysqli_stmt_close($stmt2);
        } while ($inTotal <= 8);
        if (($inTotal == 9 && $rsvp == "in")) {
          $sql3 = "UPDATE users SET rsvp='waitlist' WHERE email='$_SESSION[emailAddress]';";
          $stmt3 = mysqli_prepare($conn, $sql3);  
          mysqli_stmt_execute($stmt3); 
          mysqli_stmt_close($stmt3);


        header("Location: dashboard.php");
        exit();
        }
      }
    }
?>

I've tried to call and close statements to avoid multiple statements being open at the same time.

I'm expecting the changes to be rather instant in the update of the database to reflect on user's profile and the main dashboard.

And what is supposed to be the point of that loop in the first place? You are performing the exact same UPDATE statement in each loop iteration, as far as I can see - same $rsvp value, same email address. What made you think do the exact same thing x times here, made more sense than doing it once? — CBroe, Nov 02 '22 at 13:43
If you wan to set the RSVP status for the current user based on how many RSVPs you already got - then you should perform a query that _counts_ those first of all. — CBroe, Nov 02 '22 at 13:48
The SQL is half way to a prepared statement. [MySQLi](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php). — Jason K, Nov 02 '22 at 13:50

David · Answer 1 · 2022-11-02T14:22:30.067

This is an infinite loop:

do {
    $sql2 = "[your UPDATE statement...]";
    $stmt2 = mysqli_prepare($conn, $sql2);
    mysqli_stmt_execute($stmt2);
    mysqli_stmt_close($stmt2);
} while ($inTotal <= 8);

Because the loop will continue until $inTotal <= 8 is no longer true. But since nothing ever changes $inTotal within the loop, if it's true once then it will always be true.

Taking a step back... Why is this even a loop in the first place? You're just repeatedly executing the same SQL statement. If it succeeds once then it succeeded. Remove the loop and just execute the statement once:

$sql2 = "[your UPDATE statement...]";
$stmt2 = mysqli_prepare($conn, $sql2);
mysqli_stmt_execute($stmt2);
mysqli_stmt_close($stmt2);

Important Note: Notice how I removed the actual UPDATE statement above. That was just to demonstrate the structure of the code after removing the loop. (And to explicitly avoid anybody blindly copying/pasting this answer's content into actual code.) But you will also want to correct a SQL injection vulnerability here (and anywhere else in your code):

$sql2 = "UPDATE users SET rsvp=? WHERE email=?";
$stmt2 = mysqli_prepare($conn, $sql2);
mysqli_stmt_bind_param($stmt2, 'ss', $rsvp, $_SESSION['emailAddress']);
mysqli_stmt_execute($stmt2);
mysqli_stmt_close($stmt2);

MySqli update is taking a while to load/timing out completely after submitting

1 Answers1