I simply tried using "mysqli_multi_query" function in my php code (Queries are separated by semi-colon) and it inserts data correctly.
I'm not getting any error after "mysqli_multi_query" is called. But the php code below the "mysqli_multi_query" statement (simple echo to test) are not reached.
When I replace "mysqli_multi_query" with "mysqli_query" inside a loop, total php code works without problem.
Is this a known behaviour or is there something else I need to know about "mysqli_multi_query"? Is mysqli executes multiple queries asynchronously?
I'm sorry my code is at the home pc and unable to post it here until next week end. I checked all the queries by manually running on the workbench and they works perfectly.
I still can fix this by running each query separately in a for loop, but hope to reduce the number of database hits and increase the performance with "mysqli_multi_query".
I saw some similar posts here but none of them properly addressed my question. Any support would be highly appreciated.
The code was something like this..
<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql1 = "insert into test (testCol) values(1);";
$sql2 = "insert into test (testCol) values(2);";
mysqli_multi_query($con, $sql1.$sql2 ); /* data inserted properly in test table. No error. */
/* Any code below here is not reached. But when I used below commented queries instead of above (mysqli_multi_query), echo is called.
mysqli_query($con, $sql1);
mysqli_query($con, $sql2);
*/
echo "Success";
mysqli_close($con);
?>
Yes it's mostly something what you guess. if you mix mysqli_multi_query and mysqli_query, the latter(s) won't be executed!
problamatic code:
$mysqli->multi_query(" Many SQL queries ; "); //works
$mysqli->query(" SQL statement #1 ; ") // not works!
The only way to do this correctly is:
$mysqli->multi_query(" Many SQL queries ; "); // works
while ($mysqli->next_result()) {;} // flush multi_queries
$mysqli->query(" SQL statement #1 ; ") // works!
You just need to use/flush results to make the rest of php code to work.