$query->next_row() usability

[eluser]Drew J[/eluser]
Hey guys,

I have a simple example:

Code:

foreach($query->result() as $row):
                
    // Get the next row for reference
    $next_row = $query->next_row();
    // Get the previous row for reference
    $prev_row = $query->previous_row();

endforeach;

My expected result is that on the first iteration through the loop:
- $row would be row #1
- $next_row would be row #2
- $prev_row might be NULL or return row #1
Then the next loop:
- $row would be row #2
- $next_row would be row #3
- $prev_row would be row #1

Instead, I just keep getting the same row for $next_row and $prev_row. Like the pointer isn't changing. $row works as expected of course.

Can anyone shed some light on this?

I've poked through the DB_result library for about 30 minutes trying to find a problem with no luck.

[eluser]Drew J[/eluser]
Ok, think I answered my own question (and just minutes after I post no less!)... but I still would really appreciate some feedback.

It appears that when you call next_row(), it moves an internal pointer forward. But when you call previous_row() right after it, it just moves the pointer back.

I can't say this is expected behavior for me obviously. Would this expected or unexpected to anyone else?

I feel like submitting a small bug report, but if this is really expected and desired behavior then I guess it just doesn't do what I want.

[eluser]sophistry[/eluser]
hi Drew J and welcome to CI.

i would stay away from those next_row and previous_row functions. they are hogs (performance-wise) when you have a large result set. look at the code that implements them and you will see it is very wasteful and will bog down on large result sets. this is because the code calls $this->result($type) which loops over the whole array, rebuilding it anew every time just to get the adjacent rows. :-(

Code:

/**
     * Query result.  "array" version.
     *
     * @access    public
     * @return    array
     */    
    function result_array()
    {
        if (count($this->result_array) > 0)
        {
            return $this->result_array;
        }

        // In the event that query caching is on the result_id variable
        // will return FALSE since there isn't a valid SQL resource so
        // we'll simply return an empty array.
        if ($this->result_id === FALSE OR $this->num_rows() == 0)
        {
            return array();
        }

        $this->_data_seek(0);
        while ($row = $this->_fetch_assoc())
        {
            $this->result_array[] = $row;
        }
        
        return $this->result_array;
    }

[eluser]Drew J[/eluser]
Thanks sophistry! Very good advice.

I'm a long time CI user, just new to the forums.

Thanks again

[eluser]sophistry[/eluser]
sure...

i should really file a bug report on that code! it is a trap for unsuspecting users who think that because *most* things are fast and well-designed in CI that *all* things are fast and well-designed in CI.

[eluser]Pascal Kriete[/eluser]
[quote author="sophistry" date="1239829584"]this is because the code calls $this->result($type) which loops over the whole array, rebuilding it anew every time just to get the adjacent rows. :-( [/quote]

It's true that it gets copied every time as it doesn't operate on a reference, but result_object as well as result_array are cached internally (that's what the first if in the respective functions is for). So they do not loop over the result again - that would be silly.

As for the original question - the next and previous functions could be looked at as an iterator of sorts. They have no reference to where your foreach loop is, as they two operate on separate copies of the result.

If you wanted to access the next and previous result, you're best off keeping track of the key:

Code:

$result = $query->result();
foreach($result as $key => $row):
                
    // Get the next row for reference
    $next_row = isset($result[$key + 1]) ? $result[$key + 1] : FALSE;
    // Get the previous row for reference
    $prev_row = isset($result[$key - 1]) ? $result[$key - 1] : FALSE;

endforeach;

[eluser]sophistry[/eluser]
@pascal...

i've seen it die on large result sets so the code may perform differently in practice than in theory.

anyhow, i've posted it as a bug: http://codeigniter.com/bug_tracker/bug/7389/ so feel free to test it and debunk the submission as bogus.

cheers.

EDIT: that's the way i do iteration too - don't rely on CI to do it, just keep keys. don't forget you have to track the ends of the array too.

[eluser]Pascal Kriete[/eluser]
You're quite correct in saying that it grows, I just wanted to get the reason straightened out (picky, I know). So the bug report is confirmed .

I don't get to say it often enough, but this kind of stuff is truly appreciated. Community suggestions and bug reports keep CI as awesome as it is, even if they aren't always replied to - I read them all.

Drew J, sorry for totally taking this off on a tangent. Hopefully you have a working solution. Welcome to CodeIgniter.

[eluser]Drew J[/eluser]
No worries! You both have great feedback that offers more information for better decision making.

I typically use keys, similar to the example you provided. So I just moved back to doing that. The idea of CodeIgniter offering a "native" way/function to do it sounded nice though!

Thanks guys!