I have been reading up on mysqli_store_result and I don't understand why this is an opt-in and not an opt-out. According to the manual I should not use this for memory-hefty operations, but given I only fetch maybe 50-100 rows of text at a time (usually), I come to the conclusion that I should always use it, yet I must explicitly call it for every single query. This seems weird to me. I say this because it indicates to me that I have misunderstood something.
As far as I understand; if you read 100 rows and use while ($stmt->fetch()) you are actually sending 100 read-next-requests to MySQL over the network (assuming web and DB are on different machines). If you use mysqli_store_result you send only one request and loop the result set on the webserver. Why would I not always do this? What am I missing?
And a subquestion: Does this have something to do with my MySQL status variable "handler read next" being very high, and would store_result help?
Actually, you may-or-may-not be issuing "100 requests." PHP is talking to a MySQL interface-layer which, in turn, is talking to the database. (But you can observe the traffic, say by using "WireShark" or other equally-wonderous TCP/IP-sniffing tools ...)
However, having said that, what I wound up doing ... after encountering some "frankly, very strange" peculiarities ... was to fetch the entire result-set into an array, then close the statement-handle. My logic then doles-out the rows from that array. "Less filling, tastes great."
I had ... issues ... with mysqli_store_result, which I never did fully figure out. I therefore abandoned it, found an equally satisfactory way that did work (namely: "store it into a PHP array ..."), and moved on.
why this is an opt-in and not an opt-out.
Well, I can try to answer (or, at least, to speculate) why it is so.
Mysqli, like most of PHP, is just a thin wrapper over C functions. So thin that it doesn't add anything on its own. And the underlying C API, in turn, provides only basic functionality, leaving all the improvements on the implementation. And as you cannot "undo" getting the resultset on the client side, it seems logical that this is an opt-in by default.
It is interesting, though, why for mysqli_query() the buffered mode is turned on by default, but for prepare/execute it is not.
yet I must explicitly call it for every single query.
Come on! is it the only thing you have to call for the every single query? What about calling bind_param, bind_result, execute()? You see, mysqli is all about calling the most basic API functions. So, in a way, calling store_result() is rather logical.
Given all that, I think that the only conclusion you can come to is that you are supposed to write your wrapper on top of mysqli, or you can use PDO, which is indeed a wrapper, with almost all required functionality already implemented.