Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysqli_store_result() vs. mysqli_use_result()

Tags:

php

mysqli

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:

  • mysqli_store_result — Transfers a result set from the last query
  • mysqli_use_result — Initiate a result set retrieval

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);.

like image 353
Avaq Avatar asked Mar 26 '12 17:03

Avaq


People also ask

What is Mysqli_query () used for?

Definition and Usage. The query() / mysqli_query() function performs a query against a database.

How run multiple MySQL queries in PHP?

Multiple statements or multi queries must be executed with mysqli::multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.

Is query () an object method of mysqli class?

This is an object representing a connection to MySQL Server. This is a string value representing the query to be executed. This is a integer value representing the result mode. You can pass MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT as values to this parameter.

What is the mysqli function to check if there any more results from a multi query?

To check if there are more results, use mysqli_more_results(). For queries which produce a result set, such as SELECT, SHOW, DESCRIBE or EXPLAIN , mysqli_use_result() or mysqli_store_result() can be used to retrieve the result set.


2 Answers

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.

like image 122
ThiefMaster Avatar answered Oct 04 '22 06:10

ThiefMaster


use_result returns an unbuffered result.

store_result returns a buffered result.

like image 25
Naftali Avatar answered Oct 04 '22 06:10

Naftali