Help with Prefetching Next Record in DB

[eluser]Unknown[/eluser]
Hi all, I'm new to CodeIgniter and need some help with pre-fetching the next record in database from a result.

Essentially it's a list of destinations, I need to pre-fetch the next destination in the list.

Is there an easy way to do this?

My current model contains:

Code:

class Times_model extends Model{

  function __construct(){
    parent::Model();
  }

  function get_records($limit, $offset){
    $query = $this->db->select('*')
             ->from('times')
             ->join('vehicles', 'vehicles.vehicle_id = times.vehicle_id')
             ->join('locations', 'locations.location_id = times.location_id')
             ->where('complete', 0)
             ->where('view', 1)
             ->order_by('time, type', 'asc')
             ->limit($limit, $offset);

    $data = $query->get()->result();

    return $data;
  }
}

Any and all help is appreciated.

PS

[eluser]Cristian Gilè[/eluser]
Hi PurpleSmurf,
you can achieve this using $limit and $offset. Set $limit to 1 (you want only the next one record) and change $offset value during the fetch operation.

For example in your controller:

Code:

$this->load->model('times_model');
for($i=0;$i<10;$i++)
{
$next_record = $this->times_model->get_records(1,$i);
}

In your model change the get_records function to return a single row:

Code:

function get_records($limit, $offset){
    $this->db->select('*')
             ->from('times')
             ->join('vehicles', 'vehicles.vehicle_id = times.vehicle_id')
             ->join('locations', 'locations.location_id = times.location_id')
             ->where('complete', 0)
             ->where('view', 1)
             ->order_by('time, type', 'asc')
             ->limit($limit, $offset);

    return $this->db->get()->row(); //or row_array() if you want to return an array instead of an object
  }

Cristian Gilè

[eluser]pickupman[/eluser]
I used something like this before (similar to links at the bottom of the page for the forums. The part I don't like about my solution is having to return the query resource and not the result object.

Code:

//In model
return $query->get();

//In Controller
$destinations = $this->times_model->get_records($limit, $offset);

$id = $this->uri->segment(3,0); //Current ID
$i  = 0;
$marker = $destinations; //Copy result set

foreach($destinations->result() as $destination){

   if($destination->id == $id){
      $next     = $marker->row($i+1);
      $previous = $marker->row($i-1);
   }
   $i++;
}

[eluser]Unknown[/eluser]
Awesome! Thanks guys, much appreciated.

PS