CodeIgniter Pagination with Database Results

Before starting to work with CodeIgniter's useful pagination feature it's worth checking out the offical documentation's page on it. This will give you a basic understanding of how it works, but unfortunately it lacks the knowledge we need to apply it to database records.

Below I'm going to show you a very basic example of how to do this:

In this example my controller is getting some products from a database table using the product category ID:


public function viewcategory($id)
{
//get the products
$data['products'] = $this->productmodel->get_products($id);
//display the results
$this->load->view('templates/header');
$this->load->view('results', $data);
$this->load->view('templates/footer');
}

The model is using a basic query to select all the products where the category ID matches our $id:


public function getproducts($id)
{
$query = $this->db->query('SELECT * FROM products WHERE productcategory = '.$id);
return $query->result_array();
}

And then finally the view displays the results using a foreach loop:


foreach ($results as $row)
{
echo $row['producttitle'];
echo $row['productdescription'];
echo $row['product_price'];
}

Now we shall apply the pagination to the above, making it so that 6 products appear per page. The two important things we will use to make this work is OFFSET and LIMIT - both of these are used in SQL and will allow us to get our 6 products at a time to display on each page. The LIMIT tells the SQL to return no more than 6 results, and the OFFSET tells the SQL how many rows to skip because we've already seen them.

The first thing we need to do is set the LIMIT and OFFSET. Now the LIMIT will always remain the same - we will always want no more than 6 results, so can set that at the beginning of the controller:


public function viewcategory($id)
{
//Set the LIMIT$limit = 6;
//get the products
$data['products'] = $this->productmodel->get_products($id);
//display the results
$this->load->view('templates/header');
$this->load->view('results', $data);
$this->load->view('templates/footer');
}

As for the OFFSET, this will be constantly changing as the user flicks through our pages of results. So we put it as a parameter in the controller which the pagination buttons can send it:


public function viewcategory($id, $offset = 0)
{
//Set the LIMIT
$limit = 6;
//get the products
$data['products'] = $this->productmodel->get_products($id);
//display the results
$this->load->view('templates/header');
$this->load->view('results', $data);
$this->load->view('templates/footer');
}

We set it to 0 to start with. Now we can go ahead and set up our pagination config in the controller (which you've probably read about on the documentation page for pagination):


public function viewcategory($id, $offset = 0)
{
//Set the LIMIT
$limit = 6;
//get the products
$data['products'] = $this->productmodel->getproducts($id);
//create pages$this->load->library('pagination');$config['baseurl'] = 'www.myexamplesite.com/index.php/category/viewcategory/'.$id.'/';$config['totalrows'] = $numberofrows;$config['perpage'] = 6;
$config['urisegment'] = 4;$this->pagination->initialize($config); $data['pagination'] = $this->pagination->create_links();
//display the results
$this->load->view('templates/header');
$this->load->view('results', $data);
$this->load->view('templates/footer');
}

In the code above I've set the baseurl of the specific controller and function we're working in (so that the pagination links send the OFFSET to the right place), and because I already have $id as a parameter the OFFSET will be sent as a second parameter, so I've set urisegment to 4.

Now we will adjust the model slightly to make use of our LIMIT and OFFSET, below is the new code in the controller:


public function viewcategory($id, $offset = 0)
{
//Set the LIMIT
$limit = 6;
//get the products
$data['results'] = $this->categorymodel->getcategoryproducts($id, $limit, $offset);
//create pages
$this->load->library('pagination');
$config['baseurl'] = 'www.myexamplesite.com/index.php/category/viewcategory/'.$id.'/';
$config['totalrows'] = $numberofrows;
$config['perpage'] = 6;
$config['uri_segment'] = 4;
$this->pagination->initialize($config);

And now we adjust the model slightly:


public function getproducts($id, $limit, $offset)
{
$query = $this->db->query('SELECT * FROM products WHERE productcategory = '.$id.' LIMIT '.$limit.' OFFSET '.$offset);
return $query->result_array();
}

And finally, we simply place the pagination links in the view:


foreach ($results as $row)
{
echo $row['producttitle'];
echo $row['productdescription'];
echo $row['product_price'];
}
echo $pagination;

Play around with the LIMIT value and adjust some of the cofiguration to suit your website. The pagination buttons can be easily styled by surrounding them by a div in the view.

Comments (1)

  • PaulPHP

    Very useful, thanks Andrew.