monetization

Datatables with CodeIgniter – Server Side Sorting – Part 3

The next part of using Datatables with CodeIgniter series that we will look at is sorting and ordering of table data. You'll notice at the top of each column in your Datatable is a sorting-icon that allows you to order the table based on that column.

Datatables Series

So far all sorting is done by Datatables. This is an issue when we implement pagination because now the data set is only a sub-section of our larger dataset, which means the sorting is only applied to the small sub-section and will not give you accurate results.

First, let's look at limiting the amount of columns you can order. Take a look at our current Datatables Javascript code in application/views/books/index.php:

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

We can restrict the ordering of certain columns by using Datatable's Column attribute. It works like so:

"columns": [
            null,
            null,
            null,
            null,
            { orderable: false},
        ],

An array of objects is created for our columns. If you want to allow a column to be orderable, you can use the null value. If you want to disable the column from being sorted, you can write {orderable: false} line.

The order of the elements in the array correspond to the table order of columns i.e. the first element corresponds to the first column of the table.

You can set the default ordering value by using the order attribute:

"order": [
          [4, "asc" ]
        ]

Where the 4 represents the column index of the table and the "asc" represents the sorting order (ascending or descending: asc or desc).

In order for this code to make any difference to our application, we need to pass these values to the controller Books.php and use them to modify our database query.

First, let's get our ordered data from Datatables and add it to our application/controllers/Books.php:

$order = $this->input->get("order");

          $col = 0;
          $dir = "";
          if(!empty($order)) {
               foreach($order as $o) {
                    $col = $o['column'];
                    $dir= $o['dir'];
               }
          }

Datatables sends the order data in an array with the name order. Datatables allows multi-ordering, but for this tutorial we'll just focus on single column ordering.

We create two new variables $col and $dir. $col will represent the index of our table column and $dir will represent the ordering sort option (asc or desc).

The code above loops through the array and stores the values in $col and $dir. Since we have only enabled single sorting, we only want to get one value from the ordering array.

Next we need to do some validation and checking.

if($dir != "asc" && $dir != "desc") {
               $dir = "asc";
          }

          $columns_valid = array(
               "books.name",
               "books.price",
               "books.author",
               "books.rating",
               "books.publisher"
          );

          if(!isset($columns_valid[$col])) {
               $order = null;
          } else {
               $order = $columns_valid[$col];
          }

First we check that $dir is a valid sorting option, if not, set it to the default value of asc.

Next we need to check the $col value is a valid table column to sort. We can do this by creating an array of the table columns in the same order as the table.

Then we do an (isset()) check to make sure our column exists. If it doesn't, we'll set $order to null so the database query knows not to use it. If it does exist, we'll set order to the name of the table column.

Now we pass these values to our get_books() function:

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

Let's modify the get_books function to take into account our new ordering options in application/models/Books_model.php:

public function get_books($start, $length, $order, $dir)
     {

          if($order !=null) {
               $this->db->order_by($order, $dir);
          }

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

We use CodeIgniter's order_by and add in the table name and sorting option. Now you should have implemented sorting!

I hope you have found these tutorials easy to understand.

Below you can find the full code for implementing this tutorial:

application/controllers/Books.php

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Books extends CI_Controller 
{

    public function __construct() {
        Parent::__construct();
        $this->load->model("books_model");
    }

    public function index() 
    {
        $this->load->view("books/index.php", array());
    }

    public function books_page() 
    {

        // Datatables Variables
        $draw = intval($this->input->get("draw"));
        $start = intval($this->input->get("start"));
        $length = intval($this->input->get("length"));

        $order = $this->input->get("order");

        $col = 0;
        $dir = "";
        if(!empty($order)) {
            foreach($order as $o) {
                $col = $o['column'];
                $dir= $o['dir'];
            }
        }

        if($dir != "asc" && $dir != "desc") {
            $dir = "asc";
        }

        $columns_valid = array(
            "books.name", 
            "books.price", 
            "books.author", 
            "books.rating", 
            "books.publisher"
        );

        if(!isset($columns_valid[$col])) {
            $order = null;
        } else {
            $order = $columns_valid[$col];
        }



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

        $data = array();

        foreach($books->result() as $r) {
            
            $data[] = array(
                $r->name,
                $r->price,
                $r->author,
                $r->rating . "/10 Stars",
                $r->publisher
            );
        }

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

        $output = array(
            "draw" => $draw,
              "recordsTotal" => $total_books,
              "recordsFiltered" => $total_books,
              "data" => $data
          );
        echo json_encode($output);
        exit();
    }
    
}

application/models/Books_model.php

<?php

class Books_Model extends CI_Model 
{

    public function get_books($start, $length, $order, $dir) 
    {

        if($order !=null) {
            $this->db->order_by($order, $dir);
        }

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

    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;
    }

}

?>

application/views/books/index.php

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>Book Display</title>
        <link rel="stylesheet" type="text/css" href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.13/css/dataTables.bootstrap.min.css" />
    
        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
        <script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.13/datatables.min.js"></script> 
        <script type="text/javascript" src="https://cdn.datatables.net/1.10.13/js/dataTables.bootstrap.min.js"></script>
    </head>
    <body>

    <div class="container">
    <div class="row">
    <div class="col-md-12">

    <h1>Book List</h1>

    <table id="book-table" class="table table-bordered table-striped table-hover">
    <thead>
    <tr><td>Book Title</td><td>Book Price</td><td>Book Author</td><td>Rating</td><td>Publisher</td></tr>
    </thead>
    <tbody>
    </tbody>
    </table>

    </div>
    </div>
    </div>

    
<script type="text/javascript">
$(document).ready(function() {
    $('#book-table').DataTable({
        "pageLength" : 5,
        "serverSide": true,
        "order": [
          [1, "asc" ]
        ],
        "columns": [
            null,
            null,
            null,
            null,
            { orderable: false},
        ],
        "ajax": {
            url : "<?php echo site_url("books/books_page") ?>",
            type : 'GET'
        },
    });
});
</script>
    </body>
</html>



Enjoyed that? Check These Posts Out

Sager NP8377 Gaming Laptop Review

Fullcalendar with PHP and CodeIgniter - Database Events - Part 2

Using Datatables with CodeIgniter Tutorial

Fullcalendar with PHP and CodeIgniter

...
monetization

Article Comments

Let us know your thoughts below by adding a quick comment!

14/06/2017

Eduardo

Hello, I have followed this tutorial and it has been excellent, but I would like to know how to add buttons to edit and delete directly from the table records in my database with mysql

Reply

14/06/2017

patchesoft

Hi Eduardo,

You would want to modify the controller file to include a new row of buttons in the json output. You could add a string with links/buttons to the functions of modifying/editing the rows. In this case, the $data variable contains the rows of data we output, so you would just add it there.

Reply

26/07/2017

Grumbledore

Hi there,

the tutorial is really greate, but i am struggling to get the datatables searchbox to work.

Does it work for you?

Reply

26/07/2017

Patchesoft

@Grumbledore This tutorial doesn't include the search implementation for Datatables. This is something you'll have to code yourself. I recommend checking out the API first to see how it's implemented :)

Reply

26/07/2017

Grumbledore

xD yeah thought so. struggling there a bit. have to change my query i guess *narf* didnt use the query builder till now. so gonna change that xD

@Eduardo
another way would be to use the render function from datatables.

like that:
"{
targets: 0,
render: function (data, type, row, meta) {
if (type === 'display') {
data = " " + row[0] + " "
}
return data;
}"

Reply

15/01/2018

Justin

Hello, i would like to know how to query with join tables on the function get_books you created.

Reply

31/08/2018

Tipu

@Patchesoft could you please add search functionality?

Reply

Leave A Comment