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

Question

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?

Can you write how your query should be? I am pretty sure the query is wrong the way it is created. — Nikos, Jan 12 '15 at 10:19
When `mysqli_mylti_query()` fails, you should `echo mysqli_error()` to see the reason. — Barmar, Jan 12 '15 at 10:35
@Nikos : the first and second query above are working, database have changed as well as the query tell, the problem is after mysqli_multi_query() the query in $qryarray variable — Edison, Jan 12 '15 at 11:05
@Barmar : mysqli_multi_array() works fine and the result is like what I want.. The problem is after mysqli_multi_array() function... — Edison, Jan 12 '15 at 11:07
@Fred-ii- : sure I start the session, if I don't the query above won't work at all... — Edison, Jan 12 '15 at 11:08
it doesn't give any error comment in the page.. it just a blank screen like nothing happens after mysqli_multi_query() — Edison, Jan 12 '15 at 11:13
Add `or die(mysqli_error($connect))` to `mysqli_query()` if you haven't already, and error reporting to the top of your file(s) right after your opening ` — Funk Forty Niner, Jan 12 '15 at 11:18
@Fred-ii-: thanks now the error show up, it says `Commands out of sync; you can't run this command now` — Edison, Jan 12 '15 at 11:28

score 20 · Accepted Answer · edited Feb 20 '21 at 13:33

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)){;}

MySQL spits error: .... mysqli::next_result(): There is no next result set. Please, call mysqli_more_results()/mysqli::more_results() to check whether to call this function/method .... in if I use your method with while ($mysqli->next_result()) — Thanasis, Jan 01 '19 at 13:03
@Thanasis This worked for me: while ($mysqli->more_results()) {$mysqli->next_result();} — Egemenk, Feb 21 '19 at 03:35

score 10 · Answer 2 · answered May 22 '17 at 08:29

10

As addition to Edison's answer:

while(mysqli_next_result($connect)){;}

I would recommend this code. This avoids exceptions.

while(mysqli_more_results($con))
{
   mysqli_next_result($con);
}

answered May 22 '17 at 08:29

Oliver Becker

161
1
8

while(mysqli_more_results($mysqli)){mysqli_next_result($mysqli);} – Hayden Thring Aug 24 '19 at 07:38

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

2 Answers2

Linked

Related