Why does mysqli_result::free_result not work on mysqli_stmt::get_result

Question

I have the following code:

$post_title = "Some dynamic POST data";
$stmt = $conn->prepare("SELECT * FROM `posts` WHERE title=? ");
$stmt->bind_param("s", $post_title);
$stmt->execute();
$rows = $stmt->get_result()->num_rows;  
$stmt->get_result()->free_result(); // throws error: commands out of sync 


$stmt = $conn->prepare("... some new condition");
$stmt->bind_param(...);
$stmt->execute();
$rows = $stmt->get_result()->num_rows;

I know I can simply use $stmt->free_result, but the php docs https://www.php.net/manual/en/mysqli-result.free.php mention you can use free_result on mysqli_result object as well so why can't we use it on mysqli_stmt->get_result(), which is a result object as well?

Does this answer your question? [Why is mysqli giving a "Commands out of sync" error?](https://stackoverflow.com/questions/3632075/why-is-mysqli-giving-a-commands-out-of-sync-error) — Progman, Dec 25 '21 at 12:34
Also check other questions like https://stackoverflow.com/questions/14554517/php-commands-out-of-sync-error — Progman, Dec 25 '21 at 12:35
You are using `$stmt->get_result()` twice. Why do you do that? — Progman, Dec 25 '21 at 12:45
@Progman Because for the first usage I need to get `num_rows` and the second time I want to use `free_result`, so that I can use my second query in `$stmt`. The docs don't mention that `get_result` flushes the result, which is what I think is happening, because not using `free_result` allows me to run my second query — user31782, Dec 25 '21 at 12:49
But why do you try to return a new `mysqli_result` object for the same query instead of reusing the first `mysqli_result` you fetched with `$stmt->get_result()`? — Progman, Dec 25 '21 at 12:51
@Progman Because as per my understanding of the php docs `$stmt->get_result()` should give the same `mysqli_result` on each execution. — user31782, Dec 25 '21 at 12:54
Does it works when you reuse the return value of the first `$stmt->get_result()` call and call the `free_result()` method on that object, instead of calling `$stmt->get_result()->free_result();`? — Progman, Dec 25 '21 at 13:12
@Progman Yes it does work that way. Moreover, I don't even have to use `free_result()` at all to get result from second `$stmt->execute()` -- I don't know why that is, probably, `get_result()` clears the buffer as well. — user31782, Dec 25 '21 at 15:10

Dharman · Answer 1 · 2021-12-25T16:52:17.503

mysqli_stmt::get_result() is not idempotent. You can only call it once. If you call it again you will get an error:

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

This error has nothing to do with free_result() which you probably should not be using in the way you showed anyway.

You need to store the result in a variable and only then you can perform all the operations you want.

$stmt = $mysqli->prepare("SELECT ? ");
$stmt->bind_param("s", $post_title);
$stmt->execute();

$result = $stmt->get_result();
$rows = $result->num_rows;  
$result->free_result();

I would also recommend that you don't ever use free_result().

Explanation:
When mysqli makes a call to MySQL server to execute a prepared statement, the server will produce a result set. This is not the outcome of the EXECUTE call, but the actual output of the SQL. By default, mysqli prepared statements are running in unbuffered mode, which means that upon execution PHP will not fetch the results from the server. You must use one of the functions to retrieve it from the MySQL server. You can do it row by row using fetch(), you can tell PHP to buffer the result set in internal memory using store_result() or you can ask PHP to buffer the result encapsulated in a mysqli_result object using get_result(). The connection line will be busy as long as there are pending rows on the MySQL server.

Once you fetch the results from MySQL, there is nothing else to read. If you try to read again, you will get OOS error mentioned above. This is why you can't call get_result() multiple times and expect the same result. Once the data is fetched to PHP, it's gone from the line, and is now stored in PHP memory. The prepared statement can of course be executed again and a new result set will be produced.

Why does mysqli_result::free_result not work on mysqli_stmt::get_result

1 Answers1