Andy Hawthorne

Pagination is particularly useful when you are coding an application that interfaces with a database. A large dataset might have hundreds of possible results for one query, and pagination creates a much nicer user experience.

In this tutorial, I’ll use CodeIgniter’s pagination library to show you how you can create a paginated list of results from a MySQL database. Along the way, you’ll also see how to fix a problem with the pagination links that the library might produce.

I’ll assume you have an installation of CodeIgniter 2.1 ready to go. For the database, we can use the official sample database provided by MySQL that is available for download as an SQL file.

The Model
(Note that I’m autoloading the database library)
We’ll start by creating a model in the application which needs to do two things: provide a count of all the records in the country table, and retrieve a list of countries from the table. Save the following as models/countries.php:

class search_model extends CI_Model
{

    public function __construct()
    {
        parent::__construct();
    }

    public function record_count()
    {
        return $this->db->count_all('Country');
    }

Next, we’ll add the method to retrieve all the records from the Country table. We will need 2 parameters for this method: $limit and $start. They will be passed into the query to determine how many records to return, and what record to start from. The parameters will be set in the controller shortly:

public function fetch_countries($limit,$start)
{
    $this->db->limit($limit,$start);
    $query = $this->db->get('Country');
    if($query->num_rows()>0)
    {
	foreach ($query->result() as $row)
	{
	   $data[] = $row;
	}

	 return $data;
    }
    else
    {
        return FALSE;
     }
}

Here, we are using CodeIgniter’s database class with Active Record to list all of the data in the Country table. There is a check to make sure that some records are returned. If no records are returned, we send back a zero to make it easy to test for records before displaying the results in our view.

The Controller
Next, we need to create a method in the default controller named welcome.php. We’ll call it example1. Just before we do that, we’ll need to load the model we just created, and also the pagination library:

public function __construct()
{
	parent:: __construct();
	$this->load->model('search_model');
	$this->load->library('pagination');
}

Now we can create the method. We’ll start by adding some of the most common configuration options for the pagination library. The options are included in an array, which is then passed as a parameter to the library’s initialise method. These options must pass a base url, a total rows count, how many rows per page we want, and which part of our url will contain the page section the user is on:

public function example1()
{
	$config['base_url'] = base_url() . 'welcome/example1';
	$config['total_rows'] = $this->search_model->record_count();
	$config['per_page'] = 20;
	$config['uri_segment'] = 3;

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

We are using the base_url() function from the built-in URL helper, to map the pagination to our controller/method. The $config['total_rows'] parameter is set from the count result we have in our model. We have set the number of rows per page to 20, and we have told CodeIgniter that the page parameter will be in the third segment of the URI string (more on that in a minute).

Remember those parameters that we sent into the query for a list of countries ($Limit and $start)? Now we can set them in the example1 method:

$page = ($this->uri->segment(3))? $this->uri->segment(3) : 0;
$data['results'] = $this->search_model->fetch_countries($config['per_page'],$page);

These two lines are important. The $page variable uses the ternary operator to either set the variable value to whatever is in the third segment of the URI string, or to zero (meaning we are on the first page). The second line calls the method from our model using the number of records per page and our newly created $page variable. What those values do is create a query like:
SELECT * FROM Country limit 20,10

Finally for the controller method; we add the following:

$data['links'] = $this->pagination->create_links();
$this->load->view('example1',$data

This uses the built-in method in the pagination library to create the pagination links, and then we load a view to display the outcome.

The View
You can copy the view called welcome_message.php and called it example1.php. Then, you can replace most of the content of the body with the following:

<body>

<div id="container">
	<h1>Countries</h1>

	<div id="body">
		<?php foreach($results as $data):?>
			<?=$data->Name?> - <?=$data->Continent?><br />
		<?php endforeach;?>
		<p>
			<?=$links?>
		</p>
	</div>

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

</body>

Completed Controller Method
Your completed method in the welcome controller should look like this:

public function example1()
{
	$config['base_url'] = base_url() . 'welcome/example1';
	$config['total_rows'] = $this->search_model->record_count();
	$config['per_page'] = 20;
	$config['uri_segment'] = 3;
	$this->pagination->initialize($config);

	$page = ($this->uri->segment(3))? $this->uri->segment(3) : 0;
	$data['results'] = $this->search_model->fetch_countries($config['per_page'],$page);
	$data['links'] = $this->pagination->create_links();
	$this->load->view('example1',$data);
}

So when you visit http://yourdomain.dev/welcome/example1 you should see the pagination links:

Click the pagination links and you should find yourself moving through the 239 records in the Country table. You may notice that the number of linked digits change:

There is a reason for that, and it can fixed, so let’s do that now.

More Config Options
There is a config item that might help to solve this problem. It’s probably useful at this stage, to remember that we are retrieving records from a database, so CodeIgniter has to re-calculate the digit links as each page is accessed. The first thing we could do, is disable the first/last previous/next links to tidy up the navigation:

$config['first_link'] = FALSE;
$config['last_link'] = FALSE;
$config['next_link'] = FALSE;
$config['prev_link'] = FALSE;

It doesn’t stop the expanding digits, but it does tidy things up. The other thing we could do is calculate how many pages there are, by dividing the total rows by the number of rows required per page. Round the result, and pass it to the $config['num_links'] parameter. Here we go then:

public function example1()
{

	$config['base_url'] = base_url() . 'welcome/example1';
	$config['total_rows'] = $this->search_model->record_count();
	$config['per_page'] = 20;
	$config['uri_segment'] = 3;
	$choice = $config['total_rows']/$config['per_page'];
	$config['num_links'] = round($choice);
	$this->pagination->initialize($config);

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

	$data['results'] = $this->search_model->fetch_countries($config['per_page'],$page);
	$data['links'] = $this->pagination->create_links();
	$this->load->view('example1',$data);
}

That will improve the links now:

So now you can use the most useful configuration options for the CodeIgniter pagination library. You can also fix the way the pagination links work to provide a consistent experience for your users. A user guide comes with every download of CodeIgniter, so be sure to check the other config options for the library there. It contains options for styling the pagination links, and changing the way the links are rendered on the page.

Tags: Codeigniter, pagination