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?
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.