What Does mysqli_store_result() Actually Do?

Question

I want to understand that What mysqli_store_result Actually does? When I visited the PHP Manual of mysqli_store_result, I found the definiton

mysqli_store_result — Transfers a result set from the last query

The Question is Where it transfers the result set? Actually I was getting the error "Commands out of sync; you can't run this command now" after executing mysqli_multi_query But When I used the following method, the Error gone.

 mysqli_multi_query($connection,$query);

do
{
    mysqli_store_result($connection);
}
while(mysqli_next_result($connection));

Now, Should I use this mysqli_store_result($connection) and mysqli_next_result($connection) after each mysqli_query or just after mysqli_multi_query Because I have read in PHP Manaul that

"Although it is always good practice to free the memory used by the result of a query using the mysqli_free_result() function, when transferring large result sets using the mysqli_store_result() this becomes particularly important."

Source: PHP: mysqli_store_result

One More Question Arises When I executed the above mentioned mysqli_multi_query($connection,$query); I put a statement echo 'storing result <br />' like below

do
{
    echo 'storing result <br />
    mysqli_store_result($connection);
}
while(mysqli_next_result($connection));

Although There were only Two INSERT queries in the $query but It gave the following output

storing result
storing result
storing result
storing result

It means there were four result sets that were transferred. I can't understand this situation. One Last Question. Does the above mentioned do while process will effect the performance?

Please post your `$query` string that produces the unexpected result. — mickmackusa, Dec 05 '17 at 03:42

mickmackusa · Answer 1 · 2018-04-22T23:35:18.067

Previous comments have stated that mysqli_store_result() is not to be used with INSERT statements, but no one has mentioned the actual appropriate function: mysqli_affected_rows(). If your statement returns a record set and you want to check it numerically, then use mysqli_num_rows().

If dealing with a mixture, this might get you started:

$queries[] = "INSERT INTO TestTable (Column1) VALUES ('TEST1')";
$queries[] = "SELECT * FROM TestTable WHERE Column1 LIKE 'TEST%'";
$queries[] = "INSERT INTO TestTable (Column1) VALUES ('TEST2')";
$queries[] = "SELECT * FROM TestTable WHERE Column1 LIKE 'TEST%'";
$queries[] = "DELETE FROM TestTable WHERE Column1 LIKE 'TEST%'";

if(mysqli_multi_query($con, implode(';', $queries))){
    do{
        if($result = mysqli_store_result($con)){
            echo "Selected rows = " . mysqli_num_rows($result) . "<br><br>";
            mysqli_free_result($result);
        }else{
            $cumulative_rows += $aff_rows = mysqli_affected_rows($con);
            echo "Current Query's Affected Rows = $aff_rows, Cumulative Rows = $cumulative_rows<br><br>";
        }
    } while(mysqli_more_results($con) && mysqli_next_result($con));
}

Outputs:

Current Query's Affected Rows = 1, Cumulative Affected Rows = 1

Selected rows = 1

Current Query's Affected Rows = 1, Cumulative Affected Rows = 2

Selected rows = 2

Current Query's Affected Rows = 2, Cumulative Affected Rows = 4

An important note to anyone new to the topic of database querying: If you are using user-supplied / externally-sourced / untrustworthy data, then you should be using prepared statements with placeholders for security/stability (mysqli_multi_query() DOES NOT AFFORD THIS). Using mysqli_multi_query() seems like a cool, concise way to send a batch of queries, but there are not many compelling reasons/scenarios to use this function over sending queries one-at-a-time in a secure manner.

@Munib if my answer satisfies your question please award it the green tick. If something is still unclear, leave me a comment and I'll try to fix up my answer. Let's get a resolution on this question. — mickmackusa, Apr 01 '17 at 21:08

score 0 · Answer 2 · answered May 03 '13 at 09:23

0

It actually will fetch the whole resultset from the MySQL. You can then mysqli_data_seek() to move to a particular row within the set. So that means 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

answered May 03 '13 at 09:23

chandresh_cool

11,753
3
30
45

But What Result Set could be after an INSERT INTO Statement. Another Question, It is performance effective? – Munib May 03 '13 at 09:26
If you have several queries to execute with an unknown number of rows, you will experience a definite and important increase in efficiency, as well as a lowering of the total workload on the server - particularly if you are doing all of this on a single box. – chandresh_cool May 03 '13 at 09:30
For insert I don't think you need to store. You can store results which are very constant like in case of a simple implementation list of countries which you want from db, this result you can store in store_result variable and then use it so that you don't need to make calls – chandresh_cool May 03 '13 at 09:36
Is there any way to check the result set after using `mysqli_store_result`. I mean can I check that what is the size of the result set that is stored. – Munib May 03 '13 at 09:39
use mysql_seek_set instead to check – chandresh_cool May 03 '13 at 09:41

What Does mysqli_store_result() Actually Do?

2 Answers2