phpmysqlimysqli-multi-query

mysqli_multi_query - Commands out of sync; you can't run this command now


I have several query strings which I want to execute at once using "mysqli_multi_query". This works.

When I insert a query again to check each item in joined tables using "mysqli_query" it doesn't return any result nor any error from PHP. When I run the query string manually in phpmyadmin, everything works fine as it should.

Here's my code:

<?php

$connect   = mysqli_connect('localhost','root','','database');
$strquery  = "";
$strquery .= "1st Query";
$strquyer .= "2nd Query";
if($multi = mysqli_multi_query($connect,$strquery)){   // function mysqli_multi_query is working
     // From here it doesn't give any response
     $qryarray = mysqli_query($connect, 
                              "SELECT purchase_detail_$_SESSION[period].item_code,
                                      purchase_detail_$_SESSION[period].location_code
                               FROM   purchase_detail_$_SESSION[period] 
                               WHERE  purchase_detail_$_SESSION[period].purchase_num = '$_POST[purchase_num]' 
                               UNION
                               SELECT purchase_detail_temp.item_code,
                                      purchase_detail_temp.location_code
                               FROM   purchase_detail_temp 
                               WHERE  purchase_detail_temp.purchase_num = '$_POST[purchase_num]' AND purchase_detail_temp.username = '$_SESSION[username]'");
     while($array = mysqli_fetch_array($qryarray)){
          "Some code to process several item code in table purchase_detail_$_SESSION[period]"
     }
}

Is there anything wrong with my code?


Solution

  • I just found the answer in the PHP manual:

    WATCH OUT: if you mix $mysqli->multi_query and $mysqli->query, the latter(s) won't be executed!

    BAD CODE:

    $mysqli->multi_query(" Many SQL queries ; "); // OK
    $mysqli->query(" SQL statement #1 ; ") // not executed!
    $mysqli->query(" SQL statement #2 ; ") // not executed!
    $mysqli->query(" SQL statement #3 ; ") // not executed!
    $mysqli->query(" SQL statement #4 ; ") // not executed!
    

    The only way to do this correctly is:

    WORKING CODE:

    $mysqli->multi_query(" Many SQL queries ; "); // OK
    while ($mysqli->next_result()) {;} // flush multi_queries
    $mysqli->query(" SQL statement #1 ; ") // now executed!
    $mysqli->query(" SQL statement #2 ; ") // now executed!
    $mysqli->query(" SQL statement #3 ; ") // now executed!
    $mysqli->query(" SQL statement #4 ; ") // now executed!
    

    I just insert this code after mysqli_multi_query():

    while(mysqli_next_result($connect)){;}