Codeigniter 2 Paging Error

Question

I made a system in IC 2.0 and detected an error in the pagination. The configuration script is as follows:

     $this->load->library('pagination');
   $query = $this->usuarioModel->obterRelacaoUsuarios($pagina);
            $config = array();
            $config['use_page_numbers'] = TRUE;
            $config['uri_segment'] = 3; // depends on how you passing your page number
            $config['base_url'] = base_url('usuario/listar');
            $config['total_rows'] = $qtdUsuario = $this->db->get_where('usuario', array('bol_excluido' => 'N', 'lotacao' => $this->session->userdata('lotacao')))->num_rows();
            $config['per_page'] = 10;
            $config['full_tag_open'] = '<ul class="pagination pagination-sm">';
            $config['full_tag_close'] = '</ul>';
            $config['first_link'] = 'Primeiro';
            $config['first_tag_open'] = '<li>';
            $config['first_tag_close'] = '</li>';
            $config['last_link'] = 'Último';
            $config['last_tag_open'] = '<li>';
            $config['last_tag_close'] = '</li>';

            $config['cur_tag_open'] = '<li class="active"><a href="#">';
            $config['cur_tag_close'] = '</a></li>';

            $config['num_tag_open'] = '<li>';
            $config['num_tag_close'] = '</li>';

            $config['next_tag_open'] = '<li>';
            $config['next_tag_close'] = '</li>';

            $config['prev_tag_open'] = '<li>';
            $config['prev_tag_close'] = '</li>';
            $this->pagination->initialize($config);

I observed that when calling Sql :

 select * from usuarios LIMIT 10 OFFSET $pagina

Instead of it paging from 10 to 10, the mounted sqls are:

select * from usuarios LIMIT 10 OFFSET 0
 select * from usuarios LIMIT 10 OFFSET 2
 select * from usuarios LIMIT 10 OFFSET 3

To solve the problem I added the following logic, before running sql:

 $pagina = ($pagina == 0) ? 0 : ($pagina * 10) - 10;

So sql becomes sql:

    select * from usuarios LIMIT 10 OFFSET 10
    select * from usuarios LIMIT 10 OFFSET 20
    select * from usuarios LIMIT 10 OFFSET 30

My question is how to fix this bug without having to add any logic and why the variable $config['per_page'] = 10; is not working?

Answer 1

In your sql in the model, limit the values sent by $this->uri->segment('segmento') of the IC and its $config['per_page'].

For easy viewing I will use:

LIMIT X,Y

<?php 
//pagina_model.php

function obterRelacaoUsuarios($pagina = 'usuario');
    return($this->db->query(
       "SELECT * FROM $pagina LIMIT ?,?", 
          array(
              (is_null($this->uri->segment("3 ou 4 ou 5"))) ? 0 : intval($this->uri->segment("3 ou 4 ou 5")),
               $config['per_page'])
          )
    );
}