how to execute multiple statements in loop

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

You should either keep the stream open for the entire loop or open and close it with each iteration. Depends on the kind of transaction handling you are wanting to do with mysql. Since it doesnt see an open stream, yea it will just stop — Fallenreaper
– Fallenreaper, Commented Apr 1, 2014 at 16:36
What is $r? If $r is your handle to the mysql server you are closing it and it is no longer available — Rob M.
– Rob M., Commented Apr 1, 2014 at 16:36
$r is the resultset. It gives error even if ->close() is omitted. — RaffaeleT
– RaffaeleT, Commented Apr 1, 2014 at 17:31

Your Common Sense · Accepted Answer · 2014-04-01 18:02:54Z

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

edited Apr 1, 2014 at 18:02

answered Apr 1, 2014 at 17:49

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RaffaeleT Over a year ago

The queries are ok, but the stored procedures returning resultsets needed to be handled the way you pointed. Thank you

The Disintegrator · Accepted Answer · 2014-04-01 16:44:35Z

0

$r->close();

should be

 $res->close();

You are referencing a non existent object ($r).

The execution of multiple queries depends of a server configuration. But aside from thas. This would be better to use transactions (that depending of the effect of those stored procedures, of course)

edited Apr 1, 2014 at 16:44

answered Apr 1, 2014 at 16:37

The Disintegrator

4,1859 gold badges37 silver badges44 bronze badges

Collectives™ on Stack Overflow

how to execute multiple statements in loop

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related