Will overriding the variable holding a prepared statement close the statement?

Question

Some people recommends calling close() on a prepared statement when I am done with the result of a query.

I often reuse the same variable name for the prepared statement, so I was wondering if overriding the variable automatically calls close() on the prepared statement ?

Example of how I currently do it (The SQL is made up for the examples):

// Fetch the requested user
$stmt = $mysqli->prepare("SELECT * FROM user WHERE id = ?");
$stmt->bind_param("i", $_GET['userid']);
$stmt->execute();
$user = $stmt->get_result()->fetch_assoc();

// Fetch all posts associated with the user
$stmt = $mysqli->prepare("SELECT * FROM posts WHERE user_id = ?");
$stmt->bind_param("i", $user['id']);
$stmt->execute();
$result = $stmt->get_result();

$posts = array();
while ($row = $result->fetch_assoc()) {
    $posts[] = $row;
}

Should I call $stmt->close(); between fetching the user and fetching the posts or is it done when I override $stmt by calling $stmt = $mysqli->prepare(...); ?

https://www.php.net/manual/en/mysqli-stmt.close.php says "If the current statement has pending or unread results, this function cancels them so that the next query can be executed". AFAIK it has no other purpose. So if that's not an issue (which in your example it doesn't appear to be) then I don't think you need to bother with it. But no, overwriting the variable would not explicitly call close() - you can't call a method on an object which doesn't exist anymore! — ADyson, Jul 03 '20 at 10:48
As a general good practice though I'd recommend not re-using the same variable name for different purposes in the same scope, it can lead to confusion during debugging and maintenance, and unexpected bugs if not managed very carefully. Better to avoid the risk by simply using different names each time. — ADyson, Jul 03 '20 at 10:49
Does this answer your question? [When to close Prepared Statement](https://stackoverflow.com/questions/6631364/when-to-close-prepared-statement) — Cid, Jul 03 '20 at 10:49
Thanks for your replies @ADyson, I know I can't call `close()` on an object that doesn't exist, but I was wondering if there was like a destructor that would call it for me when the variable was being overridden with a new object :-) In regards to reusing the variable name, I don't see the harm when the only time that I interact with the `$stmt` variable, is in the same small section, in order to get a result. If I were to use a prepared statement multiple places in the same code, I would of course give it a better suited name. — Anders Olsen, Jul 03 '20 at 10:53
You could maybe check if such a thing exists by looking at the source code... https://github.com/php/php-src/tree/master/ext/mysqli — ADyson, Jul 03 '20 at 10:55

Dharman · Accepted Answer · 2020-07-03T11:13:36.070

Yes, most of the time, because PHP will try to clean up objects which have no reference as soon as possible. That means that once you overwrite it, there will be no more references to the old object and PHP will clean it up. Part of cleaning up mysqli_stmt object involves closing the statement.

But the reason why some people recommend calling $stmt->close() explicitely is to avoid errors such as this:

mysqli_sql_exception: Commands out of sync; you can't run this command now in ...

This error happens when you have not fetched all results from MySQL and you try to create a new statement by calling prepare or query. MySQL will not let you execute anything else until you fetch all remaining rows. This is usually achieved with get_result() or store_result(). If you always fetch the results in their entirety then you really don't need to worry much about when exactly the statement gets closed. Let PHP take care of it for you.

The best course of action is to avoid using mysqli functions directly. You should write some simple function which will encapsulate all the mysqli functionality so that you never have to worry about this low-level stuff. A sample function could look like this:

function safeQuery(\mysqli $db, string $sql, array $params = []): ?array {
    $stmt = $db->prepare($sql);
    if ($params) {
        $stmt->bind_param(str_repeat("s", count($params)), ...$params);
    }
    $stmt->execute();
    if ($result = $stmt->get_result()) {
        return $result->fetch_all(MYSQLI_BOTH);
    }
    return null;
}

$result = safeQuery($mysqli, 'SELECT * FROM user WHERE id = ?', [$_GET['userid']]);
if ($result) {
    $user = $result[0];
    $posts = safeQuery($mysqli, 'SELECT * FROM posts WHERE user_id = ?', [$user['id']]);

    foreach ($posts as $post) {
    }
}

Will overriding the variable holding a prepared statement close the statement?

1 Answers1