500 Internal Server Error Given even though code successfully completes

Question

I have the following PHP script that does a SELECT query, binds results to variables, runs logic based on the value of the bound variables and does another UPDATE to the table depending on the logic. This code runs successfully (I can see the appropriate column updated with the correct value in my table) but it takes about 10 seconds to get the response back from the server and the response is the following:

"The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator at ['private contact'] to inform them of the time this error occurred, and the actions you performed just before this error. More information about this error may be available in the server error log.

Additionally, a 500 Internal Server Error error was encountered while trying to use an ErrorDocument to handle the request."

The following is my script:

include("../../include/session.php");

include('inc.php');

if ((isset($_POST['lotnumberinput'])) AND (isset($_POST['sgref'])) ) {


$sgref = $_POST['sgref'];
$lotnumber = $_POST['lotnumberinput'];

$conn = new mysqli($servername, $username, $password, $dbname);

    if ($conn->connect_errno) {
        echo "There was a problem connecting to MySQL: (" . $conn->connect_errno . ") " . $conn->connect_error;
    }

    if (!($sqlget = $conn->prepare("SELECT lotnumber, lotnumber2, lotnumber3 FROM invoicesJan2016 WHERE id = ?"))) {
        echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
    }

    if (!$sqlget->bind_param("s", $sgref)) {
        echo "Binding parameters failed: (" . $sqlget->errno . ") " . $sqlget->error;
    }

    if (!$sqlget->execute()) {
        echo "Execute failed: (" . $sqlget->errno . ") " . $sqlget->error;
    }


    $sqlget->bind_result($lotnumber1, $lotnumber2, $lotnumber3);

    $res = $sqlget->fetch();

    $sqlget->free_result();

if ($res) {

    while ($res) {


        if ($lotnumber1 == "") {


            if (!($sql = $conn->prepare("UPDATE invoicesJan2016 SET lotnumber = ? WHERE id = ?"))) {
                echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
            }

            if (!$sql->bind_param("ss", $lotnumber, $sgref)) {
                echo "Binding parameters failed: (" . $sql->errno . ") " . $sql->error;
            }


            if (!$sql->execute()) {
                    echo "Execute failed: (" . $sql->errno . ") " . $sql->error;

            } else {

                echo "SG Successfully Added!";
            }


        } else if (($lotnumber1 !== "") AND ($lotnumber2 == "")) {


            if (!($sql = $conn->prepare("UPDATE invoicesJan2016 SET lotnumber2 = ? WHERE id = ?"))) {
                echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
            }


            if (!$sql->bind_param("ss", $lotnumber, $sgref)) {
                echo "Binding parameters failed: (" . $sql->errno . ") " . $sql->error;
            }


            if (!$sql->execute()) {
                    echo "Execute failed: (" . $sql->errno . ") " . $sql->error;

            } else {

                echo "SG Successfully Added!";
            }


        } else if (($lotnumber1 !== "") AND ($lotnumber2 !== "") AND ($lotnumber3 == "")) {


            if (!($sql = $conn->prepare("UPDATE invoicesJan2016 SET lotnumber3 = ? WHERE id = ?"))) {
                echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
            }


            if (!$sql->bind_param("ss", $lotnumber, $sgref)) {
                echo "Binding parameters failed: (" . $sql->errno . ") " . $sql->error;
            }


            if (!$sql->execute()) {
                    echo "Execute failed: (" . $sql->errno . ") " . $sql->error;

            } else {

                echo "SG Successfully Added!";
            }


        } else {

            echo "There are too many lot numbers associated with this SG. Please call IT to request additional space.";

        }

    } // End While.

}//end if


} else {

  echo "SG Number did not save. Please try again.";

}

$sqlget->close();
$sql->close();
$conn->close();

All help is appreciated as I am not sure why its taking so long to get a response.

Thank you!

Answer 1

Thank you for the responses. I was able to confirm the 500 Internal Server Error was caused by a response size of 4 MBs...!!! This was caused by the while loop for the logic. By removing the while and restructuring the logic components of the page, the response is significantly smaller and functioning perfectly. Below is the updated version of the code that is functioning as it should. Thank you to everyone for the help!!!

MY CODE:

<?php

include("../../include/session.php");
include('inc.php');

if ((isset($_POST['lotnumberinput'])) AND (isset($_POST['sgref'])) ) {

  $sgref = $_POST['sgref'];
  $lotnumber = $_POST['lotnumberinput'];

  $conn = new mysqli($servername, $username, $password, $dbname);

  if ($conn->connect_errno) {
        echo "There was a problem connecting to MySQL: (" . $conn->connect_errno . ") " . $conn->connect_error;
  }

  if (!($sqlget = $conn->prepare("SELECT lotnumber, lotnumber2, lotnumber3 FROM invoicesJan2016 WHERE id = ?"))) {
        echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
  }

  if (!$sqlget->bind_param("s", $sgref)) {
        echo "Binding parameters failed: (" . $sqlget->errno . ") " . $sqlget->error;
  }

  if (!$sqlget->execute()) {
    echo "Execute failed: (" . $sqlget->errno . ") " . $sqlget->error;
  }

  $sqlget->bind_result($lotnumber1, $lotnumber2, $lotnumber3);

  $res = $sqlget->fetch();

  $sqlget->free_result();

  if ($res) {

    if ($lotnumber1 == "") {

      if (!($sql = $conn->prepare("UPDATE invoicesJan2016 SET lotnumber = ? WHERE id = ?"))) {
        echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
      }

    } else if (($lotnumber1 !== "") AND ($lotnumber2 == "")) {

      if (!($sql = $conn->prepare("UPDATE invoicesJan2016 SET lotnumber2 = ? WHERE id = ?"))) {
        echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
      }

    } else if (($lotnumber1 !== "") AND ($lotnumber2 !== "") AND ($lotnumber3 == "")) {

      if (!($sql = $conn->prepare("UPDATE invoicesJan2016 SET lotnumber3 = ? WHERE id = ?"))) {
        echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
      }

    } else {

      $sql = "";

    }

    if (!($sql == "")) {

      if (!$sql->bind_param("ss", $lotnumber, $sgref)) {

        echo "Binding parameters failed: (" . $sql->errno . ") " . $sql->error;
      }

      if (!$sql->execute()) {

        echo "Execute failed: (" . $sql->errno . ") " . $sql->error;

      } else {

        echo "SG Successfully Added!";
      }

    } else {

      echo "There are too many lot numbers associated with this SG. Please call IT to request additional space.";

    }

  }//end if

} else {

  echo "SG Number did not save. Please try again.";

}

$sqlget->close();
$sql->close();
$conn->close();

?>

Answer 2

(Edited answer) After trying to read through your code, which is not properly indented and makes reading really difficult, I'd suggest to check whether your variables ($sqlget, $sql, $conn) still hold an instance of the classes so you can actually close anything.

<?php

include("../../include/session.php");

include('inc.php');

if ((isset($_POST['lotnumberinput'])) AND (isset($_POST['sgref'])) ) {

    $sgref = $_POST['sgref'];
    $lotnumber = $_POST['lotnumberinput'];
    $conn = new mysqli($servername, $username, $password, $dbname);

    if ($conn->connect_errno) {
        echo "There was a problem connecting to MySQL: (" . $conn->connect_errno . ") " . $conn->connect_error;
    }

    if (!($sqlget = $conn->prepare("SELECT lotnumber, lotnumber2, lotnumber3 FROM invoicesJan2016 WHERE id = ?"))) {
        echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
    }

    if (!$sqlget->bind_param("s", $sgref)) {
        echo "Binding parameters failed: (" . $sqlget->errno . ") " . $sqlget->error;
    }

    if (!$sqlget->execute()) {
        echo "Execute failed: (" . $sqlget->errno . ") " . $sqlget->error;
    }

    $sqlget->bind_result($lotnumber1, $lotnumber2, $lotnumber3);
    $res = $sqlget->fetch();
    $sqlget->free_result();

    if ($res) {

        while ($res) {

            if ($lotnumber1 == "") {

                if (!($sql = $conn->prepare("UPDATE invoicesJan2016 SET lotnumber = ? WHERE id = ?"))) {
                    echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
                }

                if (!$sql->bind_param("ss", $lotnumber, $sgref)) {
                    echo "Binding parameters failed: (" . $sql->errno . ") " . $sql->error;
                }

                if (!$sql->execute()) {
                    echo "Execute failed: (" . $sql->errno . ") " . $sql->error;
                } else {
                    echo "SG Successfully Added!";
                }

            } else if (($lotnumber1 !== "") AND ($lotnumber2 == "")) {

                if (!($sql = $conn->prepare("UPDATE invoicesJan2016 SET lotnumber2 = ? WHERE id = ?"))) {
                    echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
                }

                if (!$sql->bind_param("ss", $lotnumber, $sgref)) {
                    echo "Binding parameters failed: (" . $sql->errno . ") " . $sql->error;
                }

                if (!$sql->execute()) {
                    echo "Execute failed: (" . $sql->errno . ") " . $sql->error;
                } else {
                    echo "SG Successfully Added!";
                }

            } else if (($lotnumber1 !== "") AND ($lotnumber2 !== "") AND ($lotnumber3 == "")) {

                if (!($sql = $conn->prepare("UPDATE invoicesJan2016 SET lotnumber3 = ? WHERE id = ?"))) {
                    echo "Prepare failed: (" . $conn->errno . ") " . $conn->error;
                }

                if (!$sql->bind_param("ss", $lotnumber, $sgref)) {
                    echo "Binding parameters failed: (" . $sql->errno . ") " . $sql->error;
                }

                if (!$sql->execute()) {
                    echo "Execute failed: (" . $sql->errno . ") " . $sql->error;
                } else {
                    echo "SG Successfully Added!";
                }

            } else {
                echo "There are too many lot numbers associated with this SG. Please call IT to request additional space.";
            }

        } // End While.

    }//end if

    if($sqlget != null){$sqlget->close();} 
    if($sql != null){$sql->close();}
    if($conn != null) {$conn->close();}

} else {
    echo "SG Number did not save. Please try again.";
}

?>