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?
Best Answer
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 usemysqli_num_rows()
.If dealing with a mixture, this might get you started:
Outputs:
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). Usingmysqli_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.