Datatables with CodeIgniter - Server Side - Part 2

In the first part of using Datatables with CodeIgniter, I showed you how to use the basics of Datatables, pulling data with the database and displaying it to your users. A lot of the functions such as searching, sorting and pagination are built into Datatables, which is great for small datasets.

Datatables Series

This tutorial is going to look at using large datasets. Previously, we were just pulling all the data from our table in one go and leaving all the hard work to Datatables. The problem with this is that when our table size grows into thousands and thousands of records, our server is going to become overloaded with heavy intensive queries, not to mention Datatables won't thank you for it in when it has to process thousands of rows too.

The solution is to only grab a sample of our large dataset by taking parameters from the Datatables plugin.

Pagination

Pagination is one of the more simpler actions to implement. Pagination works by grabbing your data and putting into into multiple pages so that all of your data isn't displayed in one go.

Let's revisit our code application/views/books/index.php:

$('#book-table').DataTable({
        "pageLength" : 5,
        "ajax": {
            url : "<?php echo site_url("books/books_page") ?>",
            type : 'GET'
        },
    });

In our last tutorial I mentioned the pageLength element that we can modify to specify how many records to display per page. When we make a request to our page, this variable is sent as $length in our controller code. This means we can successfully pull a small subset of data based off what the user wants and needs.

Our plan of action is:

Count the total rows in our datatables using a simple non-intensive method. This will allow us to create the row of pages.
Grab the $length variable to know how many rows per page we need. Use this variable in our database query.
Grab the offset variable that tells us where to start our row count from. If we are on page 3, we want to grab the next 5 rows for page 4.

Let's start with #1. First let's create a new method in our Books_model.php file that will be used to count the total amount of pages.

Modify application/models/Books_model.php to include:

public function get_total_books()
 {
      $query = $this->db->select("COUNT(*) as num")->get("books");
      $result = $query->row();
      if(isset($result)) return $result->num;
      return 0;
 }

We use MYSQL's COUNT function to quickly count all the rows. This is much faster than using CodeIgniter's Database Num_rows() function that we were previously using.

Let's modify our application/controllers/Books.php file to use the new total books method:

$total_books = $this->books_model->get_total_books();

$output = array(
   "draw" => $draw,
     "recordsTotal" => $total_books,
     "recordsFiltered" => $total_books,
     "data" => $data
);

That's #1 complete.

#2 and #3 we can do at the same time. We already have the $length variable, we just need the offset. If you were paying attention to the controller code, you'll have noticed the $start variable we added.

$start = intval($this->input->get("start"));
$length = intval($this->input->get("length"));

The $start variable is the offset variable used for counting records. We need to pass both of these to our get_books() method:

$books = $this->books_model->get_books($start, $length);

Now let's modify our Books_model.php file and modify the query to use our two new variables:

public function get_books($start, $length)
     {
          return $this->db
               ->limit($length,$start)
               ->get("books");
     }

The mysql LIMIT function allows us to set the offset and the amount of records we want to get. Now when we load our Datatables, it is only pulling at max $length rows, which is a lot more less intensive on our database. You've potentially gone from pulling thousands of rows to just 5 rows (or whatever number you set).