6.3 MySQL Query Iterator

Iterators are for more than just directories. You can also apply them to database queries. In Chapter 3, you saw MySQL's new mysqli_multi_query( ) function, which is used to send multiple queries at once. However, processing involves a minimum of four functions and a do/while loop. Yikes!

Despite these complexities, the essential action is iteration: in this case, you're iterating through database results. The iteration requires more steps than a normal query, but in its essence, it's just iteration.

This is the perfect task to encapsulate inside an Iterator . The MySQLiQueryIterator takes a nasty string of code:

 if (mysqli_multi_query($db, $query)) {         do {             if ($result = mysqli_store_result($db)) {                 while ($row = mysqli_fetch_row($result)) {                     print "$row[0]\n";             }             mysqli_free_result($result);         }     } while (mysqli_next_result($db)); } 

and transforms it into something clear and simple:

 foreach (new MySQLiQueryIterator($db, $query) as $result) {         if ($result) {                 while ($row = mysqli_fetch_row($result)) {                     print "$row[0]\n";                 }         } } 

Example 6-2 demonstrates the code for MySQLiQueryIterator .

Example 6-2. Implementing a MySQL multi-query iterator

 class MySQLiQueryIterator implements Iterator {         protected $link;         protected $query;         protected $key;         protected $valid;         protected $result;         public function _ _construct($link, $query) {                 $this->link = $link;                 $this->query = $query;         }         public function rewind( ) {                 $this->key = 0;                 if (mysqli_multi_query($this->link, $this->query)) {                         $this->result =  mysqli_store_result($this->link);                         $this->valid = true;                 } else {                         $this->result = false;                         $this->valid = false;                 }         }                  public function valid( ) {                 // mysqli_more_results( ) is one ahead of Iterator valid( )                 $valid = $this->valid;                 $this->valid = mysqli_more_results($this->link);                 return $valid;         }                  public function key( ) {                 return $this->key;         }                  public function current( ) {                 return $this->result;         }                  public function next( ) {                 if ($this->result) { mysqli_free_result($this->result); }                 $this->key++;                 if (mysqli_next_result($this->link)) {                         $this->result = mysqli_store_result($this->link);                 } else {                         $this->result = false;                 }         } } 

This iterator takes the MySQL multi-query methods and redistributes them throughout the different interface methods.

The constructor stores the database handle and the SQL query. Unlike MyDirectoryIterator , this iterator assumes you already have an open connection to the database because it's common to make many database queries in a single page.

The rewind( ) method sets the key to and executes the query using mysqli_multi_query( ) . If the query succeeds, the method saves the output of mysqli_store_result( ) in the result property and sets the valid property to true ; if it fails, then both properties are set to false .

Next, the valid( ) method returns the result from mysqli_more_results( ) , but there's a catch. The Iterator interface wants to know if the current element is valid, but mysqli_more_results( ) tells you if the next element is valid.

The solution is to return one value "behind" mysqli_more_results( ) by storing the old value in a temporary variable, fetching the new results, and then returning the old value. This allows you to fetch new data without causing an off-by-one error.

The key( ) and current( ) methods aren't very exciting, as they only return the values stored in their properties. These methods should never alter the iterator's state, because it's legal to call them multiple times inside the block.

Finally, the next( ) method fetches an additional row using mysqli_store_result( ) . However, before it does this, it releases the old result data and increments the key.

Here's an example of the iterator in action:

 $db = mysqli_connect('db.example.org'); $query = >>>_SQL_ DROP TABLE IF EXISTS users; CREATE TABLE users(username VARCHAR(50) UNIQUE,                    password VARCHAR(50)); INSERT INTO users VALUES('rasmus', 'z.8cMpdFbNAPw'); INSERT INTO users VALUES('zeev',   'asd34.23NNDeq'); SELECT username FROM users; _SQL_; foreach (new MySQLiQueryIterator($db, $query) as $result) {         if ($result) {                 while ($row = mysqli_fetch_row($result)) {                     print "$row[0]\n";                 }         } }  rasmus   zeev  

The SQL creates a new users table and populates it with two rows, one for rasmus and another for zeev . This SQL and the database connection are then passed to MySQLiQueryIterator .

The iterator executes the query and returns the results bit by bit. When $result is false , it means that line of the query didn't return any rows. This is common because some queries ” CREATE , DROP , and INSERT , for instance ”don't return rows.

You could modify the iterator to automatically consume these results and only return data that's fetchable. However, the example's design allows you to track every single query. This is useful when, for example, you're doing a bulk INSERT of data and want to track your progress by printing out every 10th row. This would be impossible if the iterator didn't return all results.

When $result is true, you can operate on the result handle using any MySQL retrieval function you want. This example fetches each row and prints out the first column, which contains usernames.