phpcrashmysqli-multi-query

What may be the cause to my php code does not run after mysqli_multi_query is called?


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);
?>

Solution

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