I got one error"Commands out of sync; you can't run this command now" when I using mysqli_multi_query

Question

I want to excute two update sql by using $mysqli->multi_query($sql);But I just get this error info below:

PHP message: UPDATE table1 SET status=1;UPDATE table2 SET name='b'|Commands out of sync; you can't run this command now" while reading response header from upstream,

When I run the query string manually in phpmyadmin, everything works fine as it should.

there is my code:

$mysqli = new MySQLi(DB_HOST,DB_USER,DB_PASSWORD,DB_DATABASE);
$mysqli->query("set names utf8");
$sql = "UPDATE table1 SET status=1;UPDATE table2 SET name='b";

if ($mysqli->multi_query($sql))  
{  
  do {  

    $mysqli->use_result();

  } while ($mysqli->more_results() && $mysqli->next_result()); 

} else {
    error_log($sql."|".mysqli_error($mysqli));
    return false;
}

Is there any wrong with my code?

================================
I have resolve my question by using this code:

$mysqli = new MySQLi(DB_HOST,DB_USER,DB_PASSWORD,DB_DATABASE);
$mysqli->query("set names utf8");
$sql = "UPDATE table1 SET status=1;UPDATE table2 SET name='b";

if ($mysqli->multi_query($sql))  
{  
  do {  

    $res = $mysqli->use_result();  
    mysqli_free_result($res);

  } while ($mysqli->more_results() && $mysqli->next_result()); 

} else {
    error_log($sql."|".mysqli_error($mysqli));
    return false;
}

Did you checked docs? Especially [this comment](http://php.net/manual/en/mysqli.multi-query.php#110155)? I think you should clear you result queue after each request to database. — Hubert Sadecki, Mar 01 '18 at 07:34
Make sure you've cleared out the queue of results. http://php.net/manual/en/mysqli.multi-query.php#110155 — TarangP, Mar 01 '18 at 07:35

score 1 · Answer 1 · answered Mar 01 '18 at 08:07

You don't want to excute two update sql by using $mysqli->multi_query()

Execute them separately, using mysqli::query()

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new MySQLi(DB_HOST,DB_USER,DB_PASSWORD,DB_DATABASE);
$mysqli->set_charset("utf8");

$mysqli->query("UPDATE table1 SET status=1");
$mysqli->query("UPDATE table2 SET name='b");

as simple as that

Note that if these queries are not static but accept variables, then you must use prepare()/execute() instead of query()

I think I have to use $mysqli->multi_query(),because I must ensure that two updates will excute correctly Simultaneously — vincent wang, Mar 01 '18 at 08:13

I got one error"Commands out of sync; you can't run this command now" when I using mysqli_multi_query

1 Answers1