Calculate the range of items shown on each page using codeigniter pagination

[eluser]Andy78[/eluser]
I am building a website that will have a lot of paginated pages on it, with hundreds to thousands of items on it. I want to show the range of items shown on the current page like so:

115-130 out of 300

I have tried this but it does not work correctly as the formula is not right

Code:

echo "Showing ".( $page == 1 ? 1 : ($page -1) * $config["per_page"] +1 )." to ".($page *  $config["per_page"])." item of ".$config["total_rows"];

The current code in my controller is:

Code:

class Home extends CI_Controller {

function __construct()
{
    parent::__construct();
    $this->load->library("pagination");
}

public function index()
{
    $this->load->model('status_model');//load the status model

    //pagination config
    $config = array();
    $config["base_url"] = site_url() . "/home/index";
    $config["total_rows"] = $this->status_model->count_active_entries();//Get the total number of rows
    $config["per_page"] = 1;
    $config["uri_segment"] = 3;
    $config["anchor_class"] = 'class="normal" ';
    $config['full_tag_open'] = '<ul>';
    $config['full_tag_close'] = '</ul>';
    $config['first_tag_open'] = '<li>';
    $config['first_tag_close'] = '</li>';
    $config['next_link'] = 'Next';
    $config['next_tag_open'] = '<li>';
    $config['next_tag_close'] = '</li>';
    $config['last_tag_open'] = '<li>';
    $config['last_tag_close'] = '</li>';
    $config['prev_link'] = 'Prev';
    $config['prev_tag_open'] = '<li>';
    $config['prev_tag_close'] = '</li>';
    $config['cur_tag_open'] = '<li class="num_active">';
    $config['cur_tag_close'] = '</li>';
    $config['num_tag_open'] = '<li>';
    $config['num_tag_close'] = '</li>';


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

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

    $statuses = $this->status_model->get_entries($config["per_page"], $page); //call the method to get all the active statuses

    if($statuses !== false) {

        $content_data['results'] = $statuses;
    }
    else { // If no results are returned display the following error message

        $content_data['error'] = 'Error no status updates are being returned from the database. Please contact an administrator.';
    }

    echo "Showing ".( $page == 1 ? 1 : ($page -1) * $config["per_page"] +1 )." to ".($page * $config["per_page"])." item of ".$config["total_rows"];


    $content_data["links"] = $this->pagination->create_links();
    $data['title'] = ' Status';                           //Browser page title
    $data['page_title'] = 'The Latest Status Updates';//The page H1 tag
    $data['content'] = $this->load->view('results', $content_data, TRUE);//the page content

    $this->load->view('home', $data);                  

}

[eluser]Harold Villacorte[/eluser]
I have no way to test this and I do not know what your queries look like but you might be looking to do something like this:

Code:

<?php
class Home extends CI_Controller {

function __construct()
{
    parent::__construct();
    $this->load->library("pagination");
}

public function index($page = NULL)
{
    $this->load->model('status_model');//load the status model
    // Set the per page.
    $per_page = 1;
    // Set the total count.
    $total_count = $this->status_model->count_active_entries();
    // Set the offset.
    $offset = ($page) ? $page : 0 ;    

    //pagination config
    $config = array();
    $config["base_url"] = site_url() . "/home/index";
    $config["total_rows"] = $total_count;
    $config["per_page"] = $per_page;
    //$config["uri_segment"] = 3;  This is not necessary.
    $config["anchor_class"] = 'class="normal" ';
    $config['full_tag_open'] = '<ul>';
    $config['full_tag_close'] = '</ul>';
    $config['first_tag_open'] = '<li>';
    $config['first_tag_close'] = '</li>';
    $config['next_link'] = 'Next';
    $config['next_tag_open'] = '<li>';
    $config['next_tag_close'] = '</li>';
    $config['last_tag_open'] = '<li>';
    $config['last_tag_close'] = '</li>';
    $config['prev_link'] = 'Prev';
    $config['prev_tag_open'] = '<li>';
    $config['prev_tag_close'] = '</li>';
    $config['cur_tag_open'] = '<li class="num_active">';
    $config['cur_tag_close'] = '</li>';
    $config['num_tag_open'] = '<li>';
    $config['num_tag_close'] = '</li>';

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

    // Skip all this.
    //$page = ($this->uri->segment(3)) ? $this->uri->segment(3) : 0;

    //call the method to get all the active statuses
    $statuses = $this->status_model->get_entries($per_page, $offset);
    
    if($statuses !== false) {

        $content_data['results'] = $statuses;
    }
    else { // If no results are returned display the following error message

        $content_data['error'] = 'Error no status updates are being returned from the database. Please contact an administrator.';
    }

    // This normally works for me.
    $first_record = $offset + 1;
    $last_record = $page + count($statuses);
    echo "Showing " . $first_record . " to " . $last_record . " item of " . $total_count;

    $content_data["links"] = $this->pagination->create_links();
    $data['title'] = ' Status';                           //Browser page title
    $data['page_title'] = 'The Latest Status Updates';//The page H1 tag
    $data['content'] = $this->load->view('results', $content_data, TRUE);//the page content

    $this->load->view('home', $data);
    }
}

[eluser]Harold Villacorte[/eluser]
I just realized that I overlooked the fact that you were in the index method which does not take a parameter so you would have to grab the $page from the uri segment like you were doing. cheers.

[eluser]Andy78[/eluser]
Thanks!! that seems to be working! However im not the best at maths so can you explain the formula as you see it? just so I make sure I understand it for future use.

[eluser]Harold Villacorte[/eluser]
You have a pretty good idea of what is going on. You were very close but you were doing certain things in an odd sort of way. Since you say you got it working I am assuming you understand most of it. I will just explain a few highlights.

You were already trying to do something similar. Here we are taking the page number generated by the pagination class and using it as the offset for the database query. That is it's main purpose. If the page is blank than the offset is 0. We also set this value as a variable so we can reuse it.:

Code:

$offset = ($page) ? $page : 0 ;

Remember that $offset is either 0 or the page number. We have to add 1 either way because the index start at zero, remember?

Code:

$first_record = $offset + 1;

You already had the result from the database query to generate the content so all we are doing is using PHP's built in count() function to count the result. You could have done this in the model with num_rows() but this way is fine. So we calculated the first record to either be 0 + 1 for the first page or $page +1 for subsequent pages, so now we just add the count() of the query to get the last record number. That's it. No complicated math, just find the simplest way of doing something.

Code:

$last_record = $page + count($statuses);

You were already getting the total count as a separate query which is correct. Here we just assigned it to a variable that we could reuse.

Code:

$total_count = $this->status_model->count_active_entries();

[eluser]Andy78[/eluser]
Thanks for that, it makes sense.

I was wandering do we need to escape parameters sent via the Uri segment ? What happens is somebody tries and sql injection attack via the pagination links? My site is very likely to come under attack so I need to make sure its super secure. here is a function from my model. Iv added mysql_real_escape_string round the params but is this pointless?

Code:

function get_entries($limit, $start)
    {
        $limit = mysql_real_escape_string($limit);
        $start = mysql_real_escape_string($start);
        
        $this->db->select('statuses.id, title, status, posted_by, posted_by_email, created, name');
        $this->db->from('statuses');
        $this->db->join('categories', 'categories.id = statuses.category_id');
        $this->db->where('active', 1);
        $this->db->limit($limit, $start);
        $query = $this->db->get();
        
        if ($query->num_rows() > 0) {
            
            return $query->result();  
        }
        
        return false;
    }

[eluser]Harold Villacorte[/eluser]
If you are using Active Record that should not be necessary. The escaping would be done in the database driver.

[eluser]Aken[/eluser]
[quote author="Harold Villacorte" date="1359660098"]If you are using Active Record that should not be necessary. The escaping would be done in the database driver.[/quote]

You should still check the format of the URI segment before running the query. Never trust user input.

[eluser]Harold Villacorte[/eluser]
Yeah it's not a bad idea to validate it first. I would not discourage anyone from taking extra security measures. The database drivers do a good job of sanitizing though in my opinion and the abstraction layer does validate the offset and limit values.

[eluser]Aken[/eluser]
It doesn't validate, it type casts the value to int.