Pagination hitting the database twice !

Pagination requires reading a record set twice:

  1. Once to read the whole set so that it can count the total number records
  2. Then to read a window of records to display

Here’s my way of doing it:

  • Using a public class property to store the total records (public $total_records)
  • Using a private function to build the query (that is common for both activities). The parameter ($isCount) we pass to this function reduces the amount of data the query generate, because for the row count we only need one field but when we read the data window we need all required fields.
  • The get_list() function first calls the database to find the total and stores it in $total_records and then reads a data window to return to the caller.
  • Remember we cannot access $total_records without first calling the get_list() method !
class Banner_model extends CI_Model {

public $total_records; //holds total records for get_list()

public function get_list($count = 10, $start = 0) {
        $query = $this->db->get();
        $result = $query->result();
        $this->total_records = count($result); //store the count

        $this->db->limit($count, $start);
        $query = $this->db->get();
        $result = $query->result();

        return $result;

private function build_query($isCount = FALSE) {
        if ($isCount) {
        } else {
            $this->db->select('*, as banner_id, b.status as banner_status');
        $this->db->from('banner b');
        $this->db->join('company c', ' = b.company_id');
        $this->db->order_by("", "desc"); //latest ones first

And now from the controller we call:

$data['banner_list'] = $this->banner_model->get_list();
$config['total_rows'] = $this->banner_model->total_records;