Over at phpdeveloper.org I was pointed to a blog post talking about MySQLi and stored procedures. That reminded me about a small thing I recently did: When using MySQLi's multi_query to send queries which return multiple result sets you have to use a rather unintuitive API which can certainly be improved.

Recently I sat down and cooked up a small improvement for that, being an iterator fan I, of course, had to use an iterator for that and implemented the following class:

<?php
class MySQLi_ResultsetIterator implements Iterator {
    private $mysqli;
    private $counter = 0;
    private $current = null;
    private $rewinded = false;

    public function __construct(mysqli $mysqli) {
        $this->mysqli = $mysqli;
    }
    private function freeCurrent() {
        if ($this->current) {
            $this->current->free();
            $this->current = null;
        }
    }
    public function rewind() {
        if ($this->rewinded) {
            throw new Exception("Already rewinded, rewinding multiple times is not allowed!");
        }
        $this->freeCurrent();
        $this->counter = 0;
        $this->rewinded = true;
    }
    public function valid() {
        $this->current = $this->mysqli->store_result();
        return (bool)$this->current;
    }
    public function next() {
        $this->freeCurrent();
        $this->counter++;
        $this->mysqli->next_result();
    }
    public function key() {
        return $this->counter;
    }
    public function current() {
        if (!$this->current) {
            throw new Exception("valid() not called");
        }
?>

This iterator is wrapping all that's needed an then can be used like that:

<php
$mysqli = new MySQLi("localhost", "root", "", "test");

$query  = "SELECT 1,2 UNION SELECT 3, 4;".
          "SELECT 'hi world' UNION SELECT 'foobar'";
if ($mysqli->multi_query($query)) {
    foreach (new MySQLi_ResultsetIterator($mysqli) as $key => $result) {
        echo 'MySQL Resultset #'.(1+$key).":\n";
        while ($row = $result->fetch_row()) {
            printf("    %s\n", $row[0]);
        }
    }
}
?>

The output will be something like

MySQL Resultset #1:
    1
    3
MySQL Resultset #2:
    hi world
    foobar

And is, in my opinion, way nicer than the classical way, which you can see on the multi_query docs page.

That code is the first revision of that idea, I'll try to improve it and port it over to C so that some future version of PHP will include it. As a disclaimer: If you plan on using this class be aware that a future PHP might bundle a class having that exact name so use your own name ;-) Feedback welcome.