Ga direct naar


Syntactic Sugar for MySQLi Results using SPL Iterators

Tuesday 12 May 2009 09:26

Ever wondered why you can't use foreach() on MySQLi Results, and instead have to write less convenient while() loops with fetch_row? Actually, you can use foreach() on MySQLi Results. All it takes is some SPL Iterator magic.

By Werner Segers

Why would we want foreach()?

Well, just take a look at the code examples below and judge for yourself.

$DB = new mysqli('localhost', 'user', 'password', 'db');

// standard approach using a while() loop with fetch_row
$Result = $DB->query($query);
while ($row = $Result->fetch_row()) {
  // do something with $row
}
$Result->free();

// same loop with foreach
foreach($DB->query($query) as $row) {
  // do something with $row
}

// foreach loop on a query that selects 2 columns
foreach ($DB->query($query) as $key => $value) {
  // do something with $key and $value
}

As you can see the foreach loops can be written in a more compact form. Another benefit is that we can't accidentally forget to free() our $Result objects this way, which could lead to unnecessary memory usage.

Whereas the PDO database interface does support foreach() on PDOStatements natively, MySQLi doesn't support foreach() on it's MySQLi_Results.

Keep reading to find out how we can add foreach() support to your MySQLi Results ourselves.

The Iterator Interface

The Standard PHP Library (SPL) contains a set of useful classes, among which we find the Iterator interface. Any class that implements the Iterator interface can be iterated using a foreach() loop.

If we want to use a foreach() loop on our query results we need to write our own ResultIterator class that implements the Iterator interface.

interface Iterator extends Traversable
{
  function rewind();
  function current();
  function key();
  function next();
  function valid();
}

Implementing the Iterator interface means providing implementations for each of the 5 methods in the interface. A nice way to show what each of these methods should do, is using the iterator interface in a standard for() loop.

for ($Iterator->rewind(); $Iterator->valid(); $Iterator->next()) {
  $value = $Iterator->current();
  $key = $Iterator->key();
}

As you can see the loop will start with rewind() and will then continue to call next() untill valid() returns false. The Iterator needs to maintain an internal pointer for the current position and return false from the valid() method as soon as the position has reached the end. Inside each iteration the key() and current() methods are called to get the $key and $value for the current position.

An Iterator for MySQLi_Result objects

The next example shows how you can create an Iterator for MySQLi results.

Once you know what each of the functions should do, implementing them for a MySQLi result is really straightforward. The only tricky part is that the mysqli_fetch_* methods advance the MySQLi_Results' internal pointer, while the current() method on our Iterator shouldn't. We can solve this by calling $Result->fetch_array() in rewind() and next() instead and prefetching the current row.

class ResultIterator implements Iterator
{
  protected $Result;
  protected $fetchMode;
  protected $position;
  protected $currentRow;

  /**
   * Constructor
   * @param MySQLi_Result $Result
   * @param constant $fetchMode (MYSQLI_ASSOC, MYSQLI_NUM, MYSQLI_BOTH)
   */
  public function __construct($Result, $fetchMode = MYSQLI_ASSOC)
  {
    $this->Result = $Result;
    $this->fetchMode = $fetchMode;
  }

  /**
   * Destructor
   * Frees the Result object
   */
  public function __destruct()
  {
    $this->Result->free();
  }

  /**
   * Rewinds the internal pointer
   */
  public function rewind()
  {
    // data_seek moves the Results internal pointer
    $this->Result->data_seek($this->position = 0);

    // prefetch the current row
    // note that this advances the Results internal pointer.
    $this->currentRow = $this->Result->fetch_array($this->fetchMode);
  }

  /**
   * Moves the internal pointer one step forward
   */
  public function next()
  {
    // prefetch the current row
    $this->currentRow = $this->Result->fetch_array($this->fetchMode);

    // and increment internal pointer
    ++$this->position;
  }

  /**
   * Returns true if the current position is valid, false otherwise.
   * @return bool
   */
  public function valid()
  {
    return $this->position < $this->Result->num_rows;
  }

  /**
   * Returns the row that matches the current position
   * @return array
   */
  public function current()
  {
    return $this->currentRow;
  }

  /**
   * Returns the current position
   * @return int
   */
  public function key()
  {
    return $this->position;
  }
}

At this point we have a fully functional ResultIterator that can be used like this:

foreach (new ResultIterator($DB->query($query) as $row)) {
  // do something with $row
}

While this is already quite nice it isn't perfect. We still need to create our own ResultIterator object, which - as i will show in the next part - isn't really necessary.

Extending MySQLi and MySQLi_Result

To avoid having to create our own ResultIterator object we can use the simple IteratorAggregate interface.

IteratorAggregate extends Traversable
{
  function getIterator();
}

As soon as foreach() is called on an object that implements this interface the getIterator() method is called and can then create a new Iterator for the object. We can apply this interface to our MySQLi_Result objects.

To do so, we will have to extend both MySQLi_Result and MySQLi. Let's start by showing how to extend the MySQLi_Result class for this purpose.

class QueryResult extends MySQLi_Result implements IteratorAggregate
{
  public function getIterator()
  {
    return new ResultIterator($this);
  }
}

As you can see this is really simple.

There is just one small problem. Even though we provided an extension for the MySQLi_Result class, the MySQLi::query() method still returns a standard MySQLi_Result object. To solve this problem we need to override the query() method, which can be done like this:

class DB extends MySQLi
{
  public function query($query)
  {
    // query succeeded
    if ($this->real_query($query)) {

      if ($this->field_count) {

        // SELECT, SHOW, DESCRIBE
        return new QueryResult($this)

      } else {

        // INSERT, UPDATE, DELETE
        return true;

      }
    }
  }

This one is a bit tricky. First we use the real_query() method to run the query. Then if there was no error in the query, we use field_count to find out if we just did a select query. If so we create our own QueryResult object and return it, otherwise we just return true to indicate the query was succesful. The trick here is that by creating our own QueryResult after calling real_query() we are basically doing the same as the store_result method does.

At this point we can finally enjoy the results of our work

$DB = new DB('localhost', 'root', '', 'test');
$query = 'SELECT ... ';

foreach ($DB->query($query) as $row) {
  // do something with $row
}

Taking it one step further

At the beginning of this article i showed the following example:

// foreach loop on a query that selects 2 columns
foreach ($DB->query($query) as $key => $value) {
  // do something with $key and $value
}

If we select 2 columns and want the first column as $key and the second as $value all we need is a specialized version of our ResultIterator.

class KeyValueResultIterator extends ResultIterator
{
  public function __construct($Result)
  {
    $this->Result = $Result;
    $this->fetchMode = MYSQLI_NUM;
  }

  public function current()
  {
    return $this->currentRow[1];
  }

  public function key()
  {
    return $this->currentRow[0];
  }
}

Likewise, when we are selecting only 1 column we don't need to return an array from the current method(). For this case we can create a third version of our ResultIterator.

class SingleColumnResultIterator extends ResultIterator
{
  public function __construct($Result)
  {
    $this->Result = $Result;
    $this->fetchMode = MYSQLI_NUM;
  }

  public function current()
  {
    return $this->currentRow[0];
  }
}

The downside of these 2 specialized ResultIterators is that we would have to create them manually again if we wanted to use them. Unless ofcourse, we alter our getIterator() method like so:

public function getIterator()
{
  switch ($this->field_count) {
    case 1:    return new SingleColumnResultIterator($this);
    case 2:    return new KeyValueResultIterator($this);
    default:   return new ResultIterator($this);
  }
}

Conclusion

By creating our own Iterator for MySQLi_Results we can now easily iterate over them using foreach(). We also solved the special cases when selecting only 1 or 2 columns. In the process we saw some of the new things we can do with the SPL.

But what about the performance?

Well, as was to be expected, it's a bit slower than a while() loop using fetch_row() directly. As we saw in the example for() loop, the Iterator interface has the overhead of 3 or 4 functions calls per iteration (depending on whether key() is called or not).
I included some source code with a few benchmarks at the bottom of this article if you need more specifics.

Further improvements

If you want to further improve the QueryResult class or try some other SPL interfaces, you could take a look at the SeekableIterator and Countable interfaces.

Or download the [source code] used for this article to play around with.

« Back

Reactions on "Syntactic Sugar for MySQLi Results using SPL Iterators"

ellisgl
Placed on: 05-12-2009 14:16
Yeah - I was going to say the performance is going to be a bit slower. Also from my previous tests from while vs. do..while, do while was 12% faster than a while statement.
Johannes
Placed on: 05-15-2009 19:39
If you take the code from http://bazaar.launchpad....ead%3A/php5/ext/mysqli/ you already get Iterator support directly from MySQLi. But be warned: This is not tested code and currently missing some bug fixes which are in PHP's CVS, the plan is to merge it back to PHP's CVS once it's more tested.
Werner
Placed on: 05-15-2009 21:39
Werner Segers
Thanks for the heads-up Johannes. Would make a nice addition to have the Iterator interface supported natively.

Log in to comment on news articles.

Procurios zoekt PHP webdevelopers. Werk aan het Procurios Webplatform en klantprojecten! Zie http://www.slimmerwerkenbijprocurios.nl/.


Hello!

We are employees at Procurios, a full-service webdevelopment company located in the Netherlands. We are experts at building portals, websites, intranets and extranets, based on an in-house developed framework. You can find out more about Procurios and our products, might you be interested.

This weblog is built and maintained by us. We love to share our ideas, thoughts and interests with you through our weblog. If you want to contact us, please feel free to use the contact form!


Showcase

  • Klantcase: Bestseller
  • Klantcase: de ChristenUnie
  • Klantcase: Evangelische Omroep
  • Klantcase: de Keurslager
  • Klantcase: New York Pizza
  • Klantcase: Verhage

Snelkoppelingen