Mysqli_Store_Result() VS. MySQLi_Use_Result()

mysqli_store_result() vs. mysqli_use_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.

difference between mysqli_query and mysqli_real_query

Look at the documentation of mysqli_query():

Functionally, using this function is identical to calling
mysqli_real_query() followed either by mysqli_use_result() or
mysqli_store_result().

From what I understand real_query actually executes the query, and use/store_result initiates the process of retrieving a result set for the query.
query() does both.

What does the 'type' property in the mysqli_result object represent?

Okay, after a bit of research I found that

[type] => 0 represents MYSQLI_STORE_RESULT constant

[type] => 1 represents MYSQLI_USE_RESULT constant

for more information about the behaviour of these constants check this answer.

In contrast to Obsidian Age's answer the number doesn't represent the type of the array and whether it's associative or numeric but the resultmode of the query() method

mysqli_use_result()

About the article you've quoted. It just means, that you should not do this:

mysqli_query($link, $query);
mysqli_use_result($link);

// lots of 'client processing'
// table is blocked for updates during this
sleep(10) 
mysqli_fetch_* .....

In such situtations you are adviced to do so:

mysqli_query($link, $query);
mysqli_store_result($link);

// lots of 'client processing' 
// table is NOT blocked for updates during this
sleep(10) 
mysqli_fetch_* .....

The article further says, that if a second query will be issued - after calling mysql_use_result() and before fetching the results from the query it will fail. This is meant per connection - per script. So other user's queries won't fail during this.

---

while the php scripts retrieves the result, the other users will not be able to get results, is that right?

No this is not right. MySQL supports as many parallel connections as you have configured in my.ini max_connections. Concurrent reads are handled by the mysql server. Client code has not to worry about that unless the max connection limit is reached and mysqli_connect() would fail. If your application reaches a point where this happens frequently you'll in most cases first try to tweak your mysql config so that mysql allows more parrallel connections. If a threshold is reached you'll use an attempt like replication or mysql cluster.

---

Also using the same situation above, when user1 fully received it's result, an 'UPDATE set view_count = INC(1)' query is sent and the table is locked I suppose, and this same query will fail for the other users?

When there are concurrent reads and writes this is of course a performance issue. But the MySQL server handle this for you, meaning the client code has not worry about it as long as connecting to mysql works. If you have really high load you'll mostly use master slave Replication or MySQL cluster.

PHP `mysqli_multi_query` cannot return a value

Don't use mysqli_multi_query(). Each query should be sent separately to the server.

Do it like this:

$response1 = mysqli_query($connection, "SET @a = 'foo'");
$response2 = mysqli_query($connection, "SET @b = 'bar'");
$response3 = mysqli_query($connection, "SELECT @a AS A, @b AS B");
$row = mysqli_fetch_assoc($response3);

---

Related Topics