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
- Using Datatables with CodeIgniter - Part 1
- Datatables with CodeIgniter - Server Side - Part 2
- Datatables with CodeIgniter – Server Side Sorting – Part 3
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>
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