codeigniter's pagination offset not working

Question

In CI's pagination, the data index should follow the offset. For example : if the limit is 10 then the 2nd index should have 10 offset, which means the index will start from 11 to 20.

I followed some tutorials, but i still cant get this offset thing works correctly. My index always reseted to 1 each time i click the differet pagination index.

This is my pagination code, note that i have tried to echo the $offset and the value is true (in 2nd index = 10, in 3rd index = 20, with $limit = 10) so i dont know why its not working:

public function index($offset = 0) {
        //check authorization
        if(!isset($_SESSION['username']))
            redirect('backend_umat/login');

            // the $offset value is true, but the index is still reseted to 1   
        echo $offset;
        $limit = 10;
        $result = $this->umat_m->get_umat($limit, $offset);

        //pagination
        $config['base_url'] = site_url('/backend_umat/index');
        $config['total_rows'] = $result['num_rows'];
        $config['per_page'] = $limit; 
        $config['uri_segment'] = 3;
        $config['full_tag_open'] = '<div id="pagination">';
        $config['full_tag_close'] = '</div>';

        $this->pagination->initialize($config); 

        $data['pagination'] = $this->pagination->create_links();

And this is my model :

public function get_umat($limit, $offset) {
        $this->db->select('*')->from('msumat')->limit($limit, $offset)->
        join('mskelas', 'msumat.kelas_id = mskelas.kelas_id');

$q = $this->db->get();
            $result['rows'] = $q->result();

            $result['num_rows'] = $this->db->select('*')->from('msumat')->
                                  join('mskelas', 'msumat.kelas_id = mskelas.kelas_id')
                                  ->get()->num_rows();

return $result;

Thanks for your help :D

Answer 1

    public function index() {
        //check authorization
        if(!isset($_SESSION['username']))
            redirect('backend_umat/login');

        // the $offset value is true, but the index is still reseted to 1   


    //pagination
    $config['base_url'] = base_url().'backend_umat/index';
    // basically you need a separate query to return only numrows
    $config['total_rows'] = ?;
    $config['per_page'] = 10; 
    $config['uri_segment'] = 3;
    $config['full_tag_open'] = '<div id="pagination">';
    $config['full_tag_close'] = '</div>';

    $this->pagination->initialize($config); 
    $offset = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;

    $result = $this->umat_m->get_umat( $config['per_page'], $offset);

    $data['pagination'] = $this->pagination->create_links();

Here try this, i changed some code hope this works. Basically i intitialized first the pagination config, before calling anything from the model. Just do a separate query to get the numrows.

Answer 2

Controller site.com/<controller>/index/<page>

public function index($offset = 0) {
    //check authorization
    if(!isset($_SESSION['username']))
        redirect('backend_umat/login');

    $limit   = 10;
    $offset = (int) $offset;
    $result  = $this->umat_m->get_umat($limit, $offset);

    //pagination
    $config['base_url']       = site_url('/backend_umat/index');
    $config['total_rows']     = $result['num_rows'];
    $config['per_page']       = $limit; 
    $config['uri_segment']    = 3;
    $config['full_tag_open']  = '<div id="pagination">';
    $config['full_tag_close'] = '</div>';

    $this->pagination->initialize($config); 

    $data['pagination'] = $this->pagination->create_links();

Model

public function get_umat($limit, $offset) {
    $result['rows'] = $this->db
        ->select('SQL_CALC_FOUND_ROWS, msumat.*, mskelas.*', FALSE)
        ->limit($limit, $offset == 1 ? 0 : $offset)
        ->join('mskelas', 'msumat.kelas_id = mskelas.kelas_id')
        ->get('msumat')
        ->result();

    $req = $this->db->query('SELECT FOUND_ROWS()')->row_array(); 
    $result['num_rows'] = $req['FOUND_ROWS()']; 

    return $result;
}

To not have to rewrite a second sql req, you can use SQL_CALC_FOUND_ROWS to retrieve the total if the request did not contain a LIMIT statement. But this can be slower than two requests.

I used FALSE as second arguments in the select() in order that CI not tries to protect field or table names with backticks.

->select('*'): is useless if you want all fields, CI will do it by default if select method was not called.

->get()->num_rows(): use instead ->count_all_results([table]).

Answer 3

First, thanks to tomexsans and lighta for their help. Sorry, i made a "stupid" mistake - which took about 2-3 hours to realize -, the index is not following the $offset because i FORGET to use that variable. I use an integer that i reset to 1 everytime the page load, so the index will reset to 1 forever :P