DataTables Server-side Processing with Custom Parameters in CodeIgniter
In the previous tutorial, I show the easiest way to implement DataTables Server-side Processing in CodeIgniter and this tutorial is an improved and more standardised generic version of that tutorial. In this tutorial, I will show how to pass custom parameters along with Datatables ajax request and how to standardise the model to make it more generic for easy reusability in other projects. If you find the model codes little difficult to understand then you can refer to my previous tutorial for an easy code. For those who don't here about Datatables, DataTables is a table enhancing plug-in for the jQuery Javascript library that helps in adding sorting, paging and filtering abilities to plain HTML tables with minimal effort. The main goal is to enhance the accessibility of data in normal HTML tables.
Now before we start coding include Datatables CSS file and Javascript files from CDN in your view page as follows. This time I am using Datatables bootstrap theme.
<!-- Datatable Bootstrap CDN -->
<link href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" type="text/css" rel="stylesheet" />
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js" type="text/javascript"></script>
Now let's understand what all tasks we need to do,
This time in our view page we also have two filtering fields (from date and to date) to filter posts from the specific time period. Initially, when the page loaded Datatables is generated with complete posts and when we pass from and to dates as custom ajax request in Datatables we get a filtered result. You can pass as many numbers of custom parameters as you can in ajax request but for sake of simplicity of this tutorial, I am passing only two.
Note
If you only visited this page to know the core part ie the code for passing custom parameters in Datatables ajax request here is it.
"data":function(data) {
data.from = $('#datepicker1').val();
data.to = $('#datepicker2').val();
}The $("#form").serialize() function will not work in the case of Datatable. So we use an object, the ajax.data option is used to extend the data object that DataTables constructs internally to submit to the server. This provides an easy method of adding additional, static, parameters to the data to be sent to the server as above.
SO LET'S START CODING
The Controller
The complete code in the Data controller is given below.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Data extends CI_Controller {
Public function __construct() {
parent::__construct();
$this->load->model('Datamodel');
}
public function index()
{
$this->load->view('header');
$this->load->view('datapage');
$this->load->view('footer');
}
public function ajax()
{
$from = $this->input->post('from');
$to = $this->input->post('to');
if($from!='' && $to!='')
{
$from = date('Y-m-d',strtotime($from));
$to = date('Y-m-d',strtotime($to));
}
$posts = $this->Datamodel->get_datatables($from,$to);
$data = array();
$no = $this->input->post('start');
foreach ($posts as $post)
{
$no++;
$row = array();
$row[] = $no;
$row[] = $post->title;
$row[] = substr(strip_tags($post->body),0,50)."...";
$row[] = date('jS F Y h:i a',strtotime($post->created_at));
$data[] = $row;
}
$output = array(
"draw" => $this->input->post('draw'),
"recordsTotal" => $this->Datamodel->count_all(),
"recordsFiltered" => $this->Datamodel->count_filtered($from,$to),
"data" => $data,
);
//output to json format
echo json_encode($output);
}
}
The index function is used to load our view page and the ajax function is used to handle Datatables Ajax request. The table body part is created dynamically by ajax function each time.
For this tutorial, we need to look at only 6 post requests. Here are they with the explanation.
- length : Number of records that the table can display in the current draw. It is expected that the number of records returned will be equal to this number unless the server has fewer records to return.
- start : Paging first record indicator. This is the start point in the current data set (0 index based - i.e. 0 is the first record).
- order[0]column : Column to which ordering should be applied. This is an index reference to the
columnsarray of information that is also submitted to the server. - order[0]dir : Ordering direction for this column. It will be
ascordescto indicate ascending ordering or descending ordering, respectively. - search[value] : The Global search value.
- draw : Draw counter. This is used by DataTables to ensure that the Ajax returns from server-side processing requests are drawn in sequence by DataTables (Ajax requests are asynchronous and thus can return out of sequence)
The Model
The complete code for Datamodel is given below.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class Datamodel extends CI_Model
{
function __construct() {
parent::__construct(); // call model constructor
}
var $table = 'posts'; // define table
var $select = array('title','body','created_at'); //specify the columns you want to fetch from table
var $column_order = array('id','title','body','created_at'); //set column field database for datatable orderable
var $column_search = array('id','title','body','created_at'); //set column field database for datatable searchable
var $order = array('created_at' => 'desc'); // default order
public function get_datatables($from, $to)
{
$this->_get_datatables_query($from, $to);
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
return $query->result();
}
public function count_filtered($from, $to)
{
$this->_get_datatables_query($from, $to);
$query = $this->db->get();
return $query->num_rows();
}
public function count_all()
{
$this->db->from($this->table);
return $this->db->count_all_results();
}
private function _get_datatables_query($from,$to)
{
$this->db
->select($this->select)
->from($this->table);
if($from!='' && $to!='' || $from!= NULL) // To process our custom input parameter
{
$this->db->where('created_at BETWEEN "'. date('Y-m-d', strtotime($from)). '" and "'. date('Y-m-d', strtotime($to)).'"');
}
$i = 0;
foreach ($this->column_search as $item) // loop column
{
if($_POST['search']['value']) // if datatable send POST for search
{
if($i===0) // first loop
{
$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
$this->db->like($item, $_POST['search']['value']);
}
else
{
$this->db->or_like($item, $_POST['search']['value']);
}
if(count($this->column_search) - 1 == $i) //last loop
$this->db->group_end(); //close bracket
}
$i++;
}
if(isset($_POST['order'])) // here order processing
{
$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}
elseif (isset($this->order)) // default order processing
{
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
}
If you are passing custom parameters in ajax request then you can pass it as parameters in model functions as above.
View Page
The complete code for datapage.php file is given below.
<style type="text/css">
.pagination>.active>a, .pagination>.active>a:focus, .pagination>.active>a:hover, .pagination>.active>span, .pagination>.active>span:focus, .pagination>.active>span:hover {
background-color: rgb(124,77,255);
}
</style>
<!-- Datatable Bootstrap CDN -->
<link href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" type="text/css" rel="stylesheet" />
<script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js" type="text/javascript"></script>
<script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js" type="text/javascript"></script>
<div class="container">
<a><h3 class="text-center">Passing Custom Parameters in DataTables CodeIgniter - Demo</h3></a>
<!-- search box container starts -->
<div class="well">
<div class="panel panel-default">
<div class="panel-body">
<div class="row">
<div class="col-md-10 col-md-offset-2">
<form class="form-inline" method="post" >
<div class="form-group">
<label for="fromdate"> FROM DATE : </label>
<input type="date" id="datepicker1" value="<?php echo set_value('fromdate'); ?>" class="form-control" placeholder="FROM DATE" required>
</div>
<div class="form-group">
<label for="todate"> TO DATE : </label>
<input type="date" id="datepicker2" value="<?php echo set_value('todate'); ?>" class="form-control" placeholder="TO DATE" required>
</div>
<div class="form-group">
   <button style="background-color: rgb(124,77,255);color: #fff" type="submit" id="search" class="btn btn-default">SEARCH</button>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
<!-- search box container ends -->
<div class="row">
<div class="col-md-12">
<table class="table table-bordered" id="myTable">
<thead>
<th>No</th>
<th>Title</th>
<th>Body</th>
<th>Created At</th>
</thead>
</table>
</div>
</div>
</div>
<script>
$(document).ready(function () {
var dataTable = $('#myTable').DataTable( {
processing:true,
serverSide: true,
"ajax": {
"url": "<?php echo base_url('data/ajax'); ?>",
"type": "POST",
"data":function(data) {
data.from = $('#datepicker1').val();
data.to = $('#datepicker2').val();
data.<?php echo $this->security->get_csrf_token_name(); ?> = "<?php echo $this->security->get_csrf_hash(); ?>";
},
},
} );
$('#search').on( 'click change', function (event) {
event.preventDefault();
if($('#datepicker1').val()=="")
{
$('#datepicker1').focus();
}
else if($('#datepicker2').val()=="")
{
$('#datepicker2').focus();
}
else
{
dataTable.draw();
}
} );
});
</script>Note: Do not forget to pass CSRF Token along with ajax POST request as above if you turn on CSRF protection in CodeIgniter config file. Cross Site Request Forgery protection is by default turned off in CodeIgniter. If you like to get maximum security in your application you can turn it on config.php file. If you enable CSRF protection don't forget to turn csrf_regenerate to false as it causes problems with back/forward navigation, multiple tabs/windows, asynchronous actions, etc.
For debugging and to see ajax post parameters you can use Firebug in firefox or Google chrome DevTools. Firebug is my favourite and easy to use but Google chrome developer tools also work well. Monitor the Network tab in the console.
Now let's see the output image.
I also made a tutorial on DataTables Server-side Processing in Laravel. Since CodeIgniter follow traditional MVC and does not has support for ORM the number of codes is higher than Laravel. The ORM in laravel helps it to avoid all model coding we did in CodeIgniter but CodeIgniter is much easy to understand if you are a beginner. I hardly recommend everybody switch to laravel after learning basic of CodeIgniter and basics of MVC. The laravel help us to code much faster and it reduces the number of lines required by about 50%. If anybody has any suggestions or doubts or need any help comment below.
Similar Posts
-
24th May 2017 04:44:37 AM
-
24th May 2017 04:55:41 AM
-
66184