CodeIgniter 3 pagination not working

0
votes

I've revisited CodeIgniter's pagination class.

I've viewed many tutorials online as the documentation lacks details.

So I copy pasted the tutorial and changed some relevant details to suit a random table I have.

Controller:

public function sample_pagination($offset = 0){
    $num_rows=$this->db->count_all("genre");
    $config['base_url'] = base_url().'pages/sample_pagination/';
    $config['total_rows'] = $num_rows;
    $config['per_page'] = 5;
    $config['num_links'] = $num_rows;
    $config['use_page_numbers'] = TRUE;
    $this->pagination->initialize($config);

    $data['records']=$this->db->get('genre', $config['per_page'],$offset);// take record of the table
    $header = array('genre_Id','name'); // create table header
    $this->table->set_heading($header);// apply a heading with a header that was created
    $this->load->view('pages/sample',$data); // load content view with data taken from the users table
    }

The view:

<body>

<div id="container">
<h1>Welcome to CodeIgniter Pagination System!</h1>

<div id="body">



<?php echo $this->table->generate($records); ?>
<?php echo $this->pagination->create_links(); ?>
</div>

<p class="footer">Page rendered in <strong>{elapsed_time}</strong> seconds</p>
</div>

</body>

It's working fine, the only problem i have is that, the database table genre i have has 14 entries and the results on the view vary depending on the per_page settings.

say for example above, i set the per_page to 5. It will show 5 rows per page of the pagination with 3 pagination links < 1 2 3 >

the problem is that the details only showed up to the 8th row of the table which had 14 rows and it repeated.

First page is

1 2 3 4 5

second page is

3 4 5 6 7

third page is

4 5 6 7 8

I'm referring to the genre_id at the above. My table genre has genre_id as PK and genre_name as a col. so it only has 2 columns.

Anyway, if i set the Per_page to 1, it shows all the table rows correctly from genre_id 1 to 14.

I'm confused how this is happening? I learn easily via doing it myself so i tried doing it by copying tutorials online so i can see how it works but im getting confused by this.

4
phpcodeigniterpagination
sample_pagination is the controller right? and you are passing the page number as an offset? that's not how you do it. – Spoody
Its from a tutorial i found online. w3programmers.com/… and the 'pages' is the controller whilst 'sample_pagination' is the method. – Chief Makii
I think im beginning to understand why it shows that way. In codeigniter active records there's an offset/limit to the records you retrieve and the way this tutorial is using the offset must have messed with the db query? like for example $this->db->get('table', 1); will only retrieve 1 row. i never connected the two because i didn't know if the offset was correct or wrong but seeing as it was a tutorial i just assumed it was correct. – Chief Makii
I think so, I can't read the tutorial now, but I'm going to write in an answer with the code I use – Spoody

4 Answers

0
votes

Here is how I do it:

class Example extends CI_Controller
{
    public function index($page = 1){
        // Select the table
        $this->db->from('table_name');

        // Total rows
        // NULL for the table name we already specified it
        // FALSE to not clear any filters if you used some before (e.g.: WHERE clause)
        $total_rows = $this->db->count_all_results(NULL, FALSE);

        // Load the pagination
        $config['base_url'] = base_url('example');
        $config['total_rows'] = $total_rows;
        $config['per_page'] = 1;
        $config['reuse_query_string'] = TRUE;
        $config['use_page_numbers'] = TRUE;

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

        // Prepare the limit to use with the query
        $limit = array(
                $config['per_page'],
                ($page - 1) * $config['per_page']
        );

        // Set the limit
        $this->db->limit($limit[0], $limit[1]);

        // NULL as we already specified the table before
        $query = $this->db->get(NULL);
        // Get the results
        $result = $query->result_array();

        // Create pagination links
        $pagination = $this->pagination->create_links();

        // Prepare data to pass it to the view page
        $view = array(
            'rows' => $result,
            'pagination' => $pagination
        );

        // Pass data to the view
        $this->load->view('page', $view):
    }
}

I think the tutorial you used is wrong, that's not how the offset works.

Documentation:

$this->db->from()

$this->db->count_all_results()

$this->db->limit()

$this->db->get()

0
votes

I simply removed this: or set it to FALSE.

$config['use_page_numbers'] = TRUE;

After reading the 2 answers i've come to understand what was messing with my pagination was the offset. Since i set use page numbers to true, everytime i click on the next page it sent the next page number as the value(2 for 2nd page) so the offset will be set to 2 therefore the second page started at the 3rd row.

0
votes

Do like this

function sample_pagination()
{
    $num_rows=$this->db->count_all("genre");
    $config['base_url'] = base_url().'pages/sample_pagination/';
    $config['total_rows'] = $num_rows;
    $config['per_page'] = 5;
    $config['uri_segment'] = 2; # added - 
    $config['num_links'] = $num_rows;
    $this->pagination->initialize($config);

    $page = ($this->uri->segment(2)) ? $this->uri->segment(2) : 0;

    $data['records']=$this->db->get('genre', $config['per_page'],$page);

    /*No Tested below this*/
    $header = array('genre_Id','name');
    $this->table->set_heading($header);
    $this->load->view('pages/sample',$data);
}

Make sure page loads without index.php and segment is correct.

0
votes

In Controller file:

public function favourite()
{
  $this->load->library('pagination'); // load pagination library
  $limit_per_page = 5;
  $offset = ($this->input->get('offset')) ? $this->input->get('offset'):0 ;  // actually codeigniter return offset not page 
  $total_records = $this->users->count_favorites($id);
   if ($total_records > 0)
    {
      $data['favorites']=$this->users->favoritesProducts($id,$limit_per_page, $offset);
      $config['base_url'] = base_url() . 'user/favourite';
        $config['total_rows'] = $total_records;
        $config['per_page'] = $limit_per_page;
        $config['enable_query_strings']=TRUE;
        $config['query_string_segment'] = 'offset';
        $config['page_query_string'] = TRUE;

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

        // build paging links
        $data["links"] = $this->pagination->create_links();
       }
       $data['viewfile'] = 'user/favourite';
      $this->load->view('template', $data);

    }

In view favourite.php file simple display links variable Like

<?php
      if(isset($links)) {
     echo $links; 
    }
   ?>

In model File Users.php 

       public function count_favorites($user_id){  
          $this->db->from('favorites f');   
         $this->db->where("user_id",$user_id);
        $this->db->order_by("f.id","DESC");
        $result=$this->db->count_all_results();
        return $result;      
       } 


      public function favoritesProducts($user_id,$limit=2, $start=0){
         $this->db->select('p.name,f.id,p.product_id,p.sku,p.small_image');    
        $this->db->from('favorites f'); 
        $this->db->join('products p', 'f.product_id=p.product_id');
        $this->db->where("f.user_id",$user_id);
        $this->db->order_by("f.id","DESC");
        //$this->db->where('site_id',SITE_ID);
           $this->db->limit($limit, $start);
        $query = $this->db->get();
        $result=$query->result();
        return $result;
       }

and it's working perfect! Url like : http://localhost//user/favourite?offset=5