• 8
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

mysqli_store_result() vs. mysqli_use_result()

The question

What is the difference between mysqli::store_result() and mysqli::use_result()?

The story

Vague documentation

The documentation on PHP.net seems very vague about the difference between the two. The mysqli::use_result()-page does not offer any code-samples, and links you to the mysqli::multi_query()-page to look for them. In that page the following code-sample is given (see the page for the full code):

/* store first result set */
if ($result = $mysqli->store_result()) {
    while ($row = $result->fetch_row()) {
        printf("%s\n", $row[0]);
    }
    $result->free();
}
/* print divider */
if ($mysqli->more_results()) {
    printf("-----------------\n");
}

The mysqli::store_result()-page uses exactly the same code-sample, with one exception:

/* store first result set */
if ($result = $mysqli->use_result()) {

Yeah... store_result became use_result. Note that even the comment above is still saying "store".

Even more confusing

Having seen the code samples, I thought; "all right, so it's an alias". But wait! The documentation gives the following descriptions:

They seem like two different things, and are not brought like aliases at all. Taking a closer look I found out that there was yet another exception in the code-sample of the mysqli::use_result()-page: $result->free(); became $result->close();. However my hopes for finding out the truth were soon after shattered, when I found that on that same page in the second code sample (the procedural equivalent), mysqli_free_result($result); was used, and not the expected mysqli_close_result($result);.

mysqli::store_result() will fetch the whole resultset from the MySQL server while mysqli::use_result() will fetch the rows one by one.

This is also mentioned in the mysqli::use_result docs you linked to:

The mysqli_use_result() function does not transfer the entire result set from the database and hence cannot be used functions such as mysqli_data_seek() to move to a particular row within the set. To use this functionality, the result set must be stored using mysqli_store_result(). One should not use mysqli_use_result() if a lot of processing on the client side is performed, since this will tie up the server and prevent other threads from updating any tables from which the data is being fetched.

You can usually always use mysqli::store_result() unless you have a good reason for not reading all rows from the server at once.

  • 37
Reply Report
    • Ah, so basically doing subsequent use_result calls is a lot more heavy because php will have to query the server for every result in the resultset, while with store_result all of the results will be stored on the php-side after the first call, and subsequent calls will just request the results from php?
    • I just love the example on php.net with the comment If we have to retrieve large amount of data we use MYSQLI_USE_RESULT what is classified as a large amount of data? This term is very relative A small amount of data from say Amazon's database would be larger than my entire 5 Mb database.
    • @amaster507 just found interesting Note at mysqli::store_result() page at Return Values section: ... returning FALSE after successful call to mysqli_query() can be too large result set (memory for it cannot be allocated) which means if you have not enough memory to buffer all of the expected data you should use mysqli::use_result()

use_result returns an unbuffered result.

store_result returns a buffered result.

  • 15
Reply Report
    • What is PHP's default method? For instance, if I do not execute store_result() or use_result() before execute() and fetch() on a prepared statement. I'm assuming use_result(), since there is no use_result() on a mysqli_stmt object.

I think this elaboration might help people getting here from languages that support retrieving either via arrays or enumerators. Because it is just that difference.

For .NET, it would be the same difference as GetDirectories (store_results) vs EnumerateDirectories (use_results) (and the File/DbResult/CSV parsing/XML Parsing equivalents).

The enumerator variant uses less (PHP) client memory and might fetch the next row asynchronously in the background while the current row is processing, making it both more memory and processing time effective on the client side. At the cost of taking up (SQL) Server resources for longer (as the result set has not been fully transmitted).

In turn the get/array way will retrieve the data in one blocking operation, taking up more client side memory for freeing DB memory earlier.

Personally I would default for the non-Enumerator Way, unless you actually got (client) memory issues.

In .NET you have to use the Enumerator way, if you run into the 2/3 GiB limit for x32 processes. Particular EnumerateLines for files is important here. With DB results it should not happen to begin with. A 2 GiB DB result is either way under-filtered (do more filtering in Query) or contains a lot of Blobs (which you should load piecemeal or even let the browser retrieve via a http handler if possible).

  • 0
Reply Report

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions