How to close the result set in mysqli prepared statement, but keep the statement - without buffering entire result?

Question

I'm trying to use multiple prepared statements with the mysqli driver in PHP and getting the infamous error:

Commands out of sync; you can't run this command now

Although I think this database API is atrociously brain-damaged, I do understand why I'm getting the error, as described by this question.

Several solutions are offered in this and various other answers I've found laying around, the only ones I've seen that seem to work involve

$stmt->store_result()

which buffers the entire result set.

However, I don't want to buffer the entire result set - I want to discard it without using (a large amount of) memory to store it!

All I want to do, is create a prepared statement, use bind_param() and bind_result() and fetch() to get some of the results, and then somehow close the results so that I can reuse the statment later and run other queries until then.

I've tried just about every way of getting the actual result object being used from the statement and explicitly closing that, and I've tried calling next_result() until it returns null. Neither of those solutions prevents that error from appearing.

So, how can I close a result set and execute other prepared statements after a fetch(), without trashing the executed statement and having to re-parse the query?

$q = $dbc->prepare("SELECT id, typename, storageclass, tablename FROM _dbtype WHERE typename=?");
$q->bind_param("s", $typeName);
$q->bind_result($id,$typeName,$storageClass,$tableName);
$q->execute();
$q->fetch();  // ie.  only once
...  <something that does not destroy $q> ...
$q2 = $dbc->prepare("SELECT id, name FROM _storagetype");

Answer 1

see mysqli_stmt::free_result()
(and http://dev.mysql.com/doc/refman/5.7/en/mysql-stmt-free-result.html for the hint that "If there is a cursor open for the statement, mysql_stmt_free_result() closes it.")

<?php
mysqli_report(MYSQLI_REPORT_STRICT|MYSQLI_REPORT_ALL);
$mysqli = new mysqli('localhost', 'localonly', 'localonly', 'test');
if ($mysqli->connect_errno) {
    trigger_error( sprintf('mysqli connect error (%d) %s', $mysqli->connect_errno, $mysqli->connect_error), E_USER_ERROR);
    die;
}
$mysqli->query('CREATE TEMPORARY TABLE sofoo ( id int auto_increment, primary key(id))');
$mysqli->query('INSERT INTO sofoo VALUES (),(),(),(),(),()');


$stmtA = $mysqli->prepare('SELECT id FROM sofoo WHERE id>1');
$stmtB = $mysqli->prepare('SELECT id FROM sofoo WHERE id<10 ORDER BY id DESC');


$stmtA->execute();
$stmtA->bind_result($id);
$stmtA->fetch(); echo $id, PHP_EOL;
$stmtA->free_result(); // without this the script ends with "Fatal error: Uncaught mysqli_sql_exception: Commands out of sync;"

$stmtB->execute();
$stmtB->bind_result($id);
$stmtB->fetch(); echo $id, PHP_EOL;

If there are multiple result sets (i.e. if you have to use next_result()) you must call free_result()for each result set separately.