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?

Answer 1

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.