CodeIgniter Forums

Full Version: Pagination, offset issue
You're currently viewing a stripped down version of our content. View the full version with proper formatting.

El Forum

[eluser]1cookie1[/eluser]
hi
I'm having problems with a pagination script. Ive searched the forums and found a lot of uesful stuff but alas i can't get my script to work. Smile I found this

article came close and I adopted some their code - but still my pagination links don't work correctly.

OK, my code:

Code:
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');


class Books_model extends Model
{
  function __construct() {
    parent::Model();
  }

  function get_books($num, $offset) {
    $query = $this->db->get('books', $num, $offset);    
    return $query;
  }
  
}


/* End of file books_model.php */
/* Location: ./system/application/models/books_model.php */


Code:
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Books extends Controller
{
    function __construct() {
            
        parent::Controller();
        $this->load->helper('url');
        $this->load->database();
    }


    function index($page = NULL) {
        
        //load pagination class
        $this->load->library('pagination');
        $config['base_url'] = base_url().'index.php/books/index/';
        $config['total_rows'] = $this->db->count_all('books');            //returns 17
        $config['per_page'] = '5';
        $config['full_tag_open'] = '<p>';
        $config['full_tag_close'] = '</p>';
        
        if (isset($page) && is_numeric($page))
            {
              $offset = ($page-1) * $config['per_page'];
            }
            else
            {
              $offset = 0;
            }

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

        //load the model and get results
        $this->load->model('books_model');
        
        $data['results'] = $this->books_model->get_books($config['per_page'], $offset);
        
        //load the HTML Table class
        $this->load->library('table');
        $this->table->set_heading('Book Id','Auth Id','Title','Company','Description','Isbn');
        
        //load the view
        $this->load->view('books_view',$data);
    }

}


/* End of file books.php */
/* Location: ./system/application/controllers/books.php */


Code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
&lt;html &gt;
&lt;head&gt;
&lt;meta http-equiv="content-type" content="text/html; charset=utf-8" /&gt;
&lt;link rel="stylesheet" href='&lt;?php echo base_url(); ?&gt;css/main.css' type="text/css" media="screen, projection" /&gt;
&lt;title&gt;CodeIgniter Pagination Tutorial&lt;/title&gt;
&lt;/head&gt;
&lt;body&gt;
<h1>Book search</h1>
&lt;?php echo $this->table->generate($results); ?&gt;
&lt;?php echo $this->pagination->create_links(); ?&gt;
&lt;/body&gt;
&lt;/html&gt;

There are 17 rows in total in my db, table = 'books'
My test URIs are:

http://localhost/CIgniter/index.php/books/index/1
http://localhost/CIgniter/index.php/books/index/2
http://localhost/CIgniter/index.php/books/index/3
http://localhost/CIgniter/index.php/books/index/4

If I type these manually in my address bar in my browser I get correct results; i.e. the script pulls records:

1 - 5
6 - 10
11 - 15

& lastly records

16 & 17, displaying these to the screen. (please see attached ss)

My links display like:

1 2 3 > Last


Clicking on link 2 displays:

http://localhost/index.php/books/index/5, in my browser address bar and in the page body I get a


404 error
The requested URL /index.php/books/index/5 was not found on this server.


Clicking on link 3 displays:

http://localhost/index.php/books/index/10, followed by

404 error
The requested URL /index.php/books/index/10 was not found on this server. And so on.

Click on Last and get:

http://localhost/index.php/books/index/15

404! Not found.


It's not the uri segment number as I have confirmed that this is correct by manually typing the uri s above.

For a relatively simple concept such as offset and limit, I'm struggling a bit.

Help please. Smile

El Forum

[eluser]Unknown[/eluser]
Hi

I am also new to codeigniter but If you try

Quote:$config['query_string_segment'] = 'offset';
$config['page_query_string'] = TRUE;

as

Code:
$this->load->library('pagination');
$config['base_url'] = base_url().'index.php/books/index/';
$config['total_rows'] = $this->db->count_all('books');            //returns 17
$config['per_page'] = '5';
$config['full_tag_open'] = '<p>';
$config['full_tag_close'] = '</p>';
        
$config['query_string_segment'] = 'offset';
$config['page_query_string'] = TRUE;

and then you will get offset in your url
and may be you don't need following code
Code:
if (isset($page) && is_numeric($page))
            {
              $offset = ($page-1) * $config['per_page'];
            }
            else
            {
              $offset = 0;
            }


See if this helps.

El Forum

[eluser]1cookie1[/eluser]
[quote author="Gurudutt" date="1263413786"]Hi

I am also new to codeigniter but If you try

$config['query_string_segment'] = 'offset';
$config['page_query_string'] = TRUE;[/quote]

hi Gurudutt

the code above produces a php notice: undefined constant 'query_string_segment' and 'page_query_string'.

One way to get it working is as follows:

forget about the books_model class completely; bin it.

In the 'new' controller:
Code:
&lt;?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Books extends Controller
{
    function __construct() {
            
        parent::Controller();
        $this->load->helper('url');
        $this->load->database();
        $this->load->library('pagination');
    }


    function index() {
        
        $data['title'] = 'Books Entries Listing';        
        $data['result'] = $this->db->get('books','5', $this->uri->segment(3));
                                //produces SELECT * FROM books LIMIT offset, num rows to return    
                                // SELECT * FROM books LIMIT $this->uri->segment(3), 3

        //set pagination parameters
        $config['base_url'] = 'http://localhost/CIgniter/books/index/';
        $config['total_rows'] = $this->db->count_all('books');            //returns 17
        $config['per_page'] = '5';
        $config['full_tag_open'] = '<p>';
        $config['full_tag_close'] = '</p>';
        $this->pagination->initialize($config);

        // create pagination links
        $data['links'] = $this->pagination->create_links();
        
        //load the view
        $this->load->view('books_view', $data);
    }

}


/* End of file books.php */
/* Location: ./system/application/controllers/books.php */


and in the 'new' books_view file:

Code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
&lt;html &gt;
&lt;head&gt;
&lt;meta http-equiv="content-type" content="text/html; charset=utf-8" /&gt;
&lt;link rel="stylesheet" href='&lt;?php echo base_url(); ?&gt;css/main.css' type="text/css" media="screen, projection" /&gt;
&lt;title&gt;CodeIgniter Pagination Tutorial&lt;/title&gt;
&lt;/head&gt;
&lt;body&gt;
<h1>Book search</h1>
<table>
&lt;?php foreach($result->result_array() as $book): ?&gt;

<tr><td>&lt;?php echo $book['bookID'];?&gt;</td><td>&lt;?php echo $book['aid'] ?&gt;</td><td>&lt;?php echo $book['title']?&gt;</td><td>&lt;?php echo $book['company']?&gt;</td><td>&lt;?php echo $book['typedesc']?&gt;</td><td>&lt;?php echo $book['isbn']; ?&gt;</td>

&lt;?php endforeach; ?&gt;
</table>
<p>&lt;?php echo $links; ?&gt;</p>
&lt;/body&gt;
&lt;/html&gt;

It takes away the model->view->controller aspect turning the script into controller->view; which is a little disapointing, but hey, it works a charm over here!

The offset clause in the MySQL query takes a bit of getting used to (fiddling with)!


hope this helps

best wishes :-)

El Forum

[eluser]mcr_rm[/eluser]
I am confused a little by this.

Firstly the pagination class always passes increments of your per page value in the uri right? how have you got it to pass 1/2/3/4 when in per pages of five it should be passing nothing followed by /5/10/15.

Secondly you are delaring $page=null in the index function however I don't see anywhere where $page is being set to the uri segment. And I don't know why you need it anyway as the offset is actually the bit passed in the uri usually i.e. 5/10/15 and so you just pass $this->uri->segment(3) to the database call for the offset surely?

I think the only bit that is really confusing for me is how you got manually typing it in the browser to work? as at no point can I see the setting of $page from the uri to multiply the per page for the offset (which is needless anyways).

Are yousurw when you type the recors it is infact pulling the right records in sequence?

El Forum

[eluser]maria clara[/eluser]
hi to all,

i am also new in CI. i also encountered that pagination problem. try this in your model. these scripts i used for my pagination and it works. hope you can get an idea with this.

Code:
$page = $this->input->post('page');
$limit = $this->input->post('rows'); // get how many rows we want to have into the grid
$sidx = $this->input->post('sidx'); // get index row - i.e. user click to sort
$sord = $this->input->post('sord'); // get the direction

if (!$sidx) $sidx = 'role_id'; //the primary key
if (!$sord) $sord = 'asc';
                
if (!$page) $page = 1;
if (!$limit) $limit = 25;
        
$start = (($page-1) * $limit);
        
$this->db->start_cache();

/* some code here for your database query*/

$this->db->order_by($sidx,$sord);
$this->db->limit($limit, $start);
$query = $this->db->get("ar_sales"); //the table name you are using
$count = $this->db->count_all_results();
        
        // calculate the total pages for the query
        if( $count > 0 && $limit > 0) {
              $total_pages = ceil($count/$limit);
        } else {
              $total_pages = 0;
        }
        
        // if for some reasons the requested page is greater than the total
        // set the requested page to total page
        if ($page > $total_pages) $page=$total_pages;
        
        // calculate the starting position of the rows
        $start = $limit * $page - $limit; // do not put $limit*($page - 1)
        
        // if for some reasons start position is negative set it to 0
        // typical case is that the user type 0 for the requested page
        if($start <0) $start = 0;
        
        $this->db->flush_cache();
        
        $data['db'] = $query;
        $data['page'] = $page;
        $data['totalpages'] = $total_pages;
    $data['totalrecords']=$count;
        return $data;

hope this could help you.

regards,
maria Smile

El Forum

[eluser]1cookie1[/eluser]
[quote author="mcr_rm" date="1263442151"]I am confused a little by this.

Firstly the pagination class always passes increments of your per page value in the uri right?
[/quote]
I'm glad you point that out! I may never have thought of it that way if you hadn't! :-)

Quote:how have you got it to pass 1/2/3/4 when in per pages of five it should be passing nothing followed by /5/10/15.
I type 1/2/3/4 MANUALLY in the url i.e.

http://localhost/CIgniter/index.php/books/index/1 //pulls records 2 - 6
http://localhost/CIgniter/index.php/books/index/2 //pulls records 7 - 11
http://localhost/CIgniter/index.php/books/index/3 //pulls records 12 - 16
http://localhost/CIgniter/index.php/books/index/4 //pulls record 17

Not what I'm looking for!

My explanation probably confused you, sorry. :-) If I type:

http://localhost/CIgniter/index.php/books/index/0, i.e. an offset of 0, then the script pulls records 1 - 5, as you may or may not expect.

Quote:Secondly you are delaring $page=null in the index function however I don't see anywhere where $page is being set to the uri segment. And I don't know why you need it anyway as the offset is actually the bit passed in the uri usually i.e. 5/10/15 and so you just pass $this->uri->segment(3) to the database call for the offset surely?

I agree, NOW! This was purely something I tried out from post #1. I may have been going off at a different tangent! Smile


Quote:I think the only bit that is really confusing for me is how you got manually typing it in the browser to work? as at no point can I see the setting of $page from the uri to multiply the per page for the offset (which is needless anyways).

I confused the issue with the whole multiplying thing. My script originally started off without it and the $page=NULL variable (again from post#1). The one thing Iv'e learnt in all of this is that whatever is in the 3rd segment (or whatever file structure your using) of the uri - its effectively the offset and the query will pull records starting from that value!

hope this helps. ;-)

El Forum

[eluser]mcr_rm[/eluser]
[quote author="1cookie1" date="1263481752"]
I'm glad you point that out! I may never have thought of it that way if you hadn't! :-)
[/quote]


No Need for the sarcasm, what I was trying to say (poorly) was exactly what you are saying. Firstly CI pagination class passes the offset in the uri not a page number i.e. multples of your per page rather than the 1/2/3/4.

So to be clear the 1/2/3/4 was never working properly with the class links?

You seem to have it now but didn't before and I was confused at how you got anything working properly with that code.

Essentially forget the multiple rubbish and simply pass in the uri segment with the offset to your db query via a model or whatever is doing the db call.

El Forum

[eluser]1cookie1[/eluser]
[quote author="mcr_rm" date="1263487652"][quote author="1cookie1" date="1263481752"]


No Need for the sarcasm, what I was trying to say (poorly) was exactly what you are saying.
[/quote]
No sarcasm intended!

Quote: Firstly CI pagination class passes the offset in the uri not a page number i.e. multples of your per page rather than the 1/2/3/4.

So to be clear the 1/2/3/4 was never working properly with the class links?

Correct! The class links did not work correctly.

Quote:You seem to have it now but didn't before and I was confused at how you got anything working properly with that code.

I admit, the code was poorly constructed, but to be fair.. I'm new to CI and so am going to look foolish on first attempt? Knowlege is something you leave with, not come in with!

Quote:Essentially forget the multiple rubbish and simply pass in the uri segment with the offset to your db query via a model or whatever is doing the db call.

Correct! Smile