I know that closing prepared statements is a suggested thing to do.
But I have a PHP script like this:
$sql = "SELECT * FROM `mytable` WHERE ..."; $stmt = $dbh->stmt_init(); if($stmt->prepare($sql)) { $stmt->bind_param("s", $user); if($stmt->execute()) { $result = $stmt->get_result(); $stmt->close(); } else header("Location: .?error=unknown"); } else header("Location: .?error=conn");
The statement gets closed if everything is ok, but when something fails during the execution it doesn’t get closed.
Should I write
else { $stmt->close(); header("Location: .?error=unknown"); }
and
else { $stmt->close(); header("Location: .?error=conn"); }
or, since an error occurred, I shouldn’t worry about closing the statement?
Or could I even write:
$sql = "SELECT * FROM `mytable` WHERE ..."; $stmt = $dbh->stmt_init(); if($stmt->prepare($sql)) { $stmt->bind_param("s", $user); if($stmt->execute()) { $result = $stmt->get_result(); } else header("Location: .?error=unknown"); } else header("Location: .?error=conn"); /*some other code*/ $stmt->close; //close every statement at the very end of the script
or is it better to close the prepared statements right after I have finished using them to avoid any sort of bugs?
Advertisement
Answer
The problem with your code is not the use of close()
, but the error reporting, which is not enabled. You must enable mysqli error reporting and then you can simplify your code.
Your DB operations should ideally be wrapped in a function or a method and then you do not need to worry about closing anything. It will be closed for you automatically.
$sql = "SELECT * FROM `mytable` WHERE ..."; $stmt = $dbh->prepare($sql); $stmt->bind_param("s", $user); $stmt->execute(); $result = $stmt->get_result(); $stmt->close();
If you were to wrap it in a function, then there is no need for close()
.
function getResults(mysqli $dbh, string $sql, string $types, array $params): array { $stmt = $dbh->prepare($sql); $stmt->bind_param($types, ...$params); $stmt->execute(); return $stmt->get_result()->fetch_all(MYSQLI_ASSOC); }
Then if you have an exception you should create a generic error handler, which will redirect the user to 500 page by generating HTTP 500 response code when an error happens, and will log all the exception details into a file on the server.