如何在循环中执行多个语句

Question

Hallo I have several queries to execute, all returning independent resultsets:

select * from table;
call procedureA(par1);
call procedureB(par2);

I would like to execute them within a loop to perform other operations:

$queries = array("select * from table;", "call procedureA(par1);", "call procedureB(par2);");
foreach($queries as $query) {
  $res=$db->query($query);
  // do something here with the query result
  someFunction($res);
}

The first statement runs fine; at the second iteration, it stops stating that $res is a non object:

Call to a member function ... on a non-object

Apart from using mysqli_multi_query(), in which way could I loop execute multiple queries?

UPDATE I removed $res->close(); from the code sample since it was misleading and ininfluent for the issue.

UPDATE2 - SOLUTION For anyone's sake, here is a complete working code:

$queries = array(
            "CALL procedureA(par1)"
            ,"CALL procedureB()"
            , "Select * from tableC"
            );

// Open connection
$db = new mysqli(
  $config['host']
  ,$config['user']
  ,$config['pwd']
  ,$config['dbname']
  ); 

foreach($queries as $query) {
  if ($res instanceof mysqli_result) {
      $res->free();
    }
  $res=$db->query($query);
  // do something with the query
  echo getHtmlTable($res);

  // free current result
  $res->free();

  // free subsequent resultset (es. the one with OK/ERR sp call status)
  while ($db->next_result()) {
    //free each result.
    $res = $db->use_result();
    if ($res instanceof mysqli_result) {
      $res->free();
    }
  }
}

Answer 1

There is nothing special in running queries in a loop.

There will be no difference, either if you write two queries just one after another, or run them in a loop. So, generally speaking, a couple of queries run in a loop is no different from a couple of queries run in order just like in all our scripts.

The only possible problem a query itself. Say, a stored procedure call always return at least two resultsets. And no other query can be run until all resultsets get retrieved.

So, as a quick-and-dirty solution a line like this could be added

while ($db->next_result()) {}

at the bottom of the loop to clean up all the resultsets possibly remained in a queue after the query execution.

It qould be also highly convenient to turn on error reporting for mysqli, to make you aware of all the mysql error occurred. Having that, you would have added such error message to your question (Which is "Commands out of sync"). To do so, add this line before mysqli connect:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);