[solved] help with syntax for result set

[eluser]Dave Rau[/eluser]
I don't know how to properly reference the next_id and prev_id rows that mysql is returning. Check the view code below; that's where my syntax is busted

If my result is contained inside $data['nextprev'], how do I reference the first (and only) result set for prev_id and next_id fields in my database?

Code:

// view
// this isn't right, but it's close I hope!
echo $nextprev->result([0])['prev_id'];

// model
// this works and returns the correct result
    function get_nextprev($album, $id) {
    $album_t = $this->app->config->item('gallery_table','simple_gallery');
        $query = "SELECT id, title, @a := id, ( SELECT id FROM {$this->app->config->item('gallery_table','simple_gallery')} WHERE album = '$album' having id < @a ORDER BY title DESC LIMIT 1 ) AS prev_id, ( SELECT id FROM gallery WHERE album = '$album' having id > @a ORDER BY title ASC LIMIT 1 ) AS next_id from {$this->app->config->item('gallery_table','simple_gallery')} where album = '$album' and id = '$id'";
        $results = $this->db->query($query);
        return $results;
    }


// controller
    function image()    {
        $this->db->where('id', $this->uri->segment(4));
        $data['query'] = $this->db->get('gallery');
        $data['nextprev'] = $this->simple_gallery->get_nextprev($this->uri->segment(3), $this->uri->segment(4));
        $this->load->view('view_photos/image', $data);
}

[eluser]iainco[/eluser]
Model:

Code:

function get_nextprev($album, $id) {
    $album_t = $this->app->config->item('gallery_table','simple_gallery');
        $query = "<omitted>";
        return $this->db->query($query)->result();
    }

View:

Code:

foreach($nextprev as $item):
        echo $item->title;
endforeach;

[eluser]Dave Rau[/eluser]
I ended up with this:

controller:

Code:

$data['nextprev'] = $this->simple_gallery->get_nextprev($this->uri->segment(3), $this->uri->segment(4));

view:

Code:

$next_id = $nextprev->row('next_id');
$prev_id = $nextprev->row('prev_id');

If someone is making a photo gallery here's a GREAT query for next/previous links in an specific order. I have albums and I'm sorting by ID, so the newest images are first in the list and you go backwards in time to see other images in the album. Here's the query:

Code:

SELECT id, title, @a := id, ( SELECT id FROM gallery WHERE album = '$album' AND id < @a ORDER BY id DESC LIMIT 1 ) AS prev_id, ( SELECT id FROM gallery WHERE album = '$album' AND id > @a ORDER BY id ASC LIMIT 1 ) AS next_id FROM gallery WHERE album = '$album' and id = '$id' order by id