MySQLi_Recordset: blending SPL and Statement/Query results

Question

I've made a SPL-based class named "Recordset" that wraps both MySQLi_STMT and MySQLi_Result objects and allows treating either as a 3-dimensional array. It requires PHP5.3+.

I'm pretty bummed about the slow foreach loop over my Recordset object, by the slower internal seek and fetch speeds within Recordset, and by the fact that nothing can even compare to the old while ($MySQLI_STMT->fetch()) {}. I've already cached certain result information, such as num_rows and class name, and try to avoid chaining internal function calls, which speeds things up considerably. Unfortunately, this cuts down on proper internal data validation checks. The constant data_seek calls do slow things a little. I'd like for this to work better. Any suggestions or insight how to speed up foreach loops and internal row fetching?

Here's excerpts of the important parts...

class Recordset implements Iterator, ArrayAccess, Countable {

    private $MySQLi_set; // MySQLi_STMT or MySQLi_Result object
    private $MySQLi_set_type; // cache of MySQLi_STMT or MySQLi_Result object type to avoid repeat instanceof calls
    private $field_metadata; // array of field info objects returned by query
    private $num_rows; // cache row count to avoid repeat mysqli_x->num_rows call
    private $bind_row; // binding array for MySQLi_STMT results
    private $pointer; // for tracking iterations over records in query


    function __construct($sql) {
        $bind_params = func_get_args();
        array_shift($bind_params);
        try {
            if (count($bind_params) === 0) {
                // expecting MYSQLI_Result obj returned, replace code as necessary
                $this->MySQLi_set = DB_Main::query($sql);  
                }
            else {
                // expecting MySQLi_STMT obj returned, replace code as necessary
                $this->MySQLi_set = DB_Main::statement($sql, $bind_params);  
                }
            }
        catch (Exception $e) {
            throw new RuntimeException(((count($bind_params)) ? 'Statement' : 'Query') . ' failed:  ' . $e->getMessage(), $e->getCode(), $e);
            }
        // cache name of object to avoid repeat instanceof calls
        $this->MySQLi_set_type = get_class($this->MySQLi_set);
        if ($this->MySQLi_set_type === 'mysqli_result') {
            // set $this->field_metadata to array of field info objects
            $this->field_metadata = $this->MySQLi_set->fetch_fields();
            }
        else if ($this->MySQLi_set_type === 'mysqli_stmt') {
            // Set up special handling for MySQLi_STMT objects
            // set $this->field_metadata to array of field info objects used later for result bindings for MySQLi_STMT object
            $result_metadata = $this->MySQLi_set->result_metadata();
            $this->field_metadata = $result_metadata->fetch_fields();   
            $result_metadata->free_result();
            unset($result_metadata);
            // store results to allow object to act as a countable, seekable array
            if (!$this->MySQLi_set->store_result())
                throw new RuntimeException('MySQLi_STMT Result failed to store.  Error: ' . $this->MySQLi_set->error);
            // because bind_result() cannot bind to an object or array, use the 'call_user_func_array()' technique instead
            $this->bind_row = array();
            $bind_row2 = array();
            foreach($this->field_metadata as $field)
                 $bind_row2[$field->name] =& $this->bind_row[$field->name];
            if (!call_user_func_array(array($this->MySQLi_set, 'bind_result'), $this->bind_row))
                throw new RuntimeException($this->MySQLi_set->error);
            if ($this->MySQLi_set->error)
                throw new RuntimeException($this->MySQLi_set->error);
            }
        else {
            throw new RuntimeException('No MySQLi_STMT or MySQLi_Result object to work with.  Class: ' . $this->MySQLi_set_type . ' attempted.');
            }
        // cache row count to avoid constant calls to MySQLi_x->num_rows
        $this->num_rows = $this->MySQLi_set->num_rows;
        // if rows were returned, set internal pointer to 0
        if ($this->num_rows > 0) $this->pointer = 0;
        }


    function fetchRow($offset = null) {
        // private function internally using regular fetch() methods to progress through records
        // sets $this->bind_row to an associative array of the next record for MySQLi_STMT wrapped objects
        $this->MySQLi_set->data_seek((($offset !== null) ? (int) $offset : $this->pointer));
        if ($this->MySQLi_set_type === 'mysqli_result')
            return $this->MySQLi_set->fetch_assoc();
        // else this is a MySQLi_STMT object
        $fetch_status = $this->MySQLi_set->fetch();
        if ($fetch_status === true) return $this->bind_row;
        else if ($fetch_status === null) return null; // no more rows
        // MySQLi_STMT row fetch failed
        throw new RuntimeException('Error fetching row: . ' . $this->MySQLi_set->error);
        }


    function count() {return $this->num_rows;}

    function current() {return $this->fetchRow();}

    function key() {return $this->pointer;}

    function next() {$this->pointer++;}

    function rewind() {$this->pointer = 0;}

    function valid() {return $this->offsetExists($this->pointer);}


    function offsetExists($offset) {
        // Part of the ArrayAccess interface.  Used to determine if the selected record in the set actually exists or is out of bounds.
        // Example: 'isset($Recordset[22])' returns true if there were 23 or more records returned from the query.
        return ((int) $offset < $this->num_rows AND (int) $offset >= 0);
        }


    function offsetGet($offset) {
        // Part of the ArrayAccess interface.  Used to retrieve the assoc array of the chosen record in line.
        // allow index referencing without altering "internal array pointer"
        // Example: 'print_r($Recordset[22])' prints the 23rd record returned by the query.
        if (!((int) $offset < $this->num_rows AND (int) $offset >= 0))
            throw new OutOfBoundsException('Out of range of result set.');
        return $this->fetchRow($offset);
        }

    }

The code is roughly 200 lines long, so instead of making a giant post, here's a pastebin link... http://pastebin.com/n17MuEzf

Use...

Objects are created by passing in an sql query string. For handling prepared statements, simply pass in additional arguments as the prepared binding parameters. In those cases, there is a behind-the-scenes bind_param going on generating the MySQLi_STMT object to be wrapped. That part isn't included here, so for the purposes of this post, pretend it's magic.

For example to automatically handle the query as a prepared statement and output rows:

$recordset = new Recordset("SELECT * FROM tblComments WHERE(parentBlogID = ?)", 2);
if (count($recordset) > 0) {
    foreach ($recordset as $record)
        echo $record['commentText'] . '<br>';
    }
else echo "No records returned.";

Advantages...

• Allows accessing result rows by array key (echo $Recordset[295]['title'];) or by foreach loops (foreach ($Recordsetas $rec) {echo $rec['title'];}).
• Allows you to get a row by index number without interfering with the internal result pointer. In other words, right in the middle of a foreach loop, you can do $x = $Recordset[259]['ID']; without causing the internal result row pointer to jump out of order.
• Allows exchanging this class out for regular 3-dimensional arrays of result dumps.
• Avoids duplication of result binding code for every statement throughout an application.
• Lets you still access functions and properties of the wrapped MySQLi object directly ($Recordset->attr_get(1)).
• Would allow plugging in automatic output filters or template handling.

Tests...

Here's some speed tests iterating over 10000 records from a localhost table with 6 columns. This test was done with prepared statement results either wrapped in Recordset or not. Timing is measured starting before and ending after each entire loop call..

With Recordset...

• foreach($Recordset as $rec) {$x = $rec;} ...
  0.1033s
• for ($i = 0, $count = count($Recordset); $i < $count; $i++) {$x = $Recordset[$i];} ...
  0.0445s
• for ($i = 0, $count = count($Recordset); $i < $count; $i++) {$x = $Recordset->fetchRow($i);} ...
  0.0344 s
• while ($x = $Recordset[$i]) {$i++; if (!isset($Recordset[$i])) break;} ...
  0.0542 s

Without Recordset, but still using the "bind to array" technique and pre-storing results...

• for ($i=0, $count=MySQLI_STMT->num_rows; $i < $count; $i++)) {$MySQLI_STMT->fetch(); $x = $bind_row;} ...
  0.0414 s
• while ($MySQLI_STMT->fetch()) {$x = $bind_row;} ...
  0.009 s

Answer 1

bob-the-destroyer withdrew the question in a comment above:

taking from a comment on PHP's man page on Mysqli classes, apparently you can extend them. Who knew? So, I've scrapped this idea and extended Mysqli_stmt to auto-handle param and result binding on construct. So I now have a simple old-school query-like api for parametrized statements with a simple fetch_assoc method. Array interface doesn't seem so important anymore.

(just posting this so this question doesn't show up as "unaswered" any more