Johannes Schlüter

For the last few months I had quite a few MySQL blog posts and didn't have anything from my "new features in PHP [trunk|5.4]" series. This article is a bridge between those two. The PHP 5.4 NEWS file has a small entry:

MySQLi: Added iterator support in MySQLi. mysqli_result implements Traversable. (Andrey, Johannes)

From the outside it is a really small change and easy to miss. The mentioned class, mysqli_result, implements an interface which adds no new methods. What once can't see is that this relates to some internal C-level functions which can be called by the engine for doing a foreach iteration on objects of this class. So with PHP 5.4 you don't have to use an "ugly" while construct anymore to fetch rows from a mysqli result but can simply do a foreach:

mysqli_report(MYSQLI_REPORT_STRICT);
try {
    $mysqli = new mysqli(/* ... */);
    foreach ($myslqi->query("SELECT a, b, c FROM t") as $row) {
        /* Process $row which is an associative array */
    }
} catch (mysqli_sql_exception $e) {
    /* an error happened ... */
}

I'm configuring mysqli in a way to throw exceptions on error. This is useful in this case as mysqli::query() might return false in the case of an error. Passing false to a foreach will give a fatal error, so I'd need a temporary variable and a check in front of the foreach loop, with exceptions I simply do the error handling in the catch block.

One thing to note is that mysqli is using buffered results ("store result") by default. If you want to use unbuffered result sets ("use result") you can easily do that by setting the flag accordingly:

foreach ($myslqi->query("SELECT a, b, c FROM t", MYSQLI_USE_RESULT) as $row) {
    /* ... */
}

People who are advanced with iterators in PHP might ask "Why did you implement Traversable only, not Iterator?" - the main reason is that we simply didn't want to. The mysqli_result class already has quite a few methods and we didn't want to make the interface confusing. If you need an Iterator class for some purpose you can simply wrap mysqli_result in an IteratoIterator.

PHP 5.3 is released and after the release stress is over my mind is open for new ideas. While relaxing yesterday I thought about many things, among them was the Resultset iterator I recently discussed.

Now I wondered where to go next with this and had the idea that an individual Resultset is a child of the whole result and this might be wrapped in an Recursive Iterator. For doing so we don't implement the Iterator interface but RecursiveIterator. RecursiveIterator extends a typical Iterator with two methods: hasChildren() and getChildren(). But now we have a problem: The Iterator returned by getChildren() has to be a RecursiveIterator, too, which makes sense, in general. But I want to return a MySQLi Resultset which isn't recursive - so making this a RecursiveIterator is wrong. My solution now is to introduce yet another Iterator which goes by the name of MySQLi_PseudoRecursiveResultIterator and is implemented by extending IteratorIterator which will wrap the MySQLi_Result and implements RecursiveIterator telling the caller that there are no children.

As a sidenote: In our experimental tree Andrey made MySQLi_Result an iterator but that's not yet in php.net's CVS (might need some more testing, and probably we might change the design there...) so I'm emulating this with MySQLi_Result::fetch_all() combined with an ArrayIterator, using the experimental code the constructor can be dropped.

So let's finally look at the code of these two classes:

<?php
class MySQLi_ResultsetIterator implements RecursiveIterator
{
    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");
        }
        $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");
        }
        return $this->current;
    }

    public function hasChildren() {
        return true;
    }
    public function getChildren() {
        return new MySQLi_PseudoRecursiveResultIterator($this->current);
    }
}

class MySQLi_PseudoRecursiveResultIterator
     extends IteratorIterator
     implements RecursiveIterator
{
    public function __construct(MySQLi_Result $result) {
        // This ctor can be dropped with the experimental bzr sources
        // as IteratorIterator::__construct() directly works with
        // MySQLi_Result
        parent::__construct(new ArrayIterator($result->fetch_all()));
    }
    public function hasChildren() {
        return false;
    }
    public function getChildren() {
        throw new Exception("This should never be called");
    }    
}
?>

Now we can use this code. For properly using a RecursiveIterator one should use a RecursiveIteratorIterator (RII). To get some nice labels I'm extending the RII and then have a single foreach:

<?php
class MyRecursive_IteratorIterator
     extends RecursiveIteratorIterator
{
    public function __construct(MySQLi $mysqli, $flags = 0) {
        parent::__construct(
                new Mysqli_ResultSetIterator($mysqli),
                $flags | RecursiveIteratorIterator::LEAVES_ONLY);
    }

    public function beginChildren() {
        echo "Next ResultSet:\n";
    }
}

$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 MyRecursive_IteratorIterator($mysqli) as $key => $row) {
        printf("    %s\n", $row[0]);
    }
}
?>

Now calling this code gives us a result similar to the following:

Next ResultSet:
    1
    3
Next ResultSet:
    hi world
    foobar

Isn't that nice? - I think that's a cool API! What do you think? Do you have use cases for such an API? Should we implement this in C and bundle it with PHP? Any feedback welcome!

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.