DataTables Server-side Processing in CodeIgniter 4
4 min readOct 14, 2022
This tutorial will discuss the easiest way to implement DataTables jQuery Plugin with remote server-side processing in Codeigniter 4. Here I will see how to get data from a remote MySQL database through ajax in Codeigniter 4.
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.
For this tutorial, we need to look at only 6 post requests.
- 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 order should be applied. This is an index reference to the column’s array of information that is also submitted to the server.
- order[0]dir: Ordering direction for this column. It will be ASC or DESC to 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)
View Page
<!DOCTYPE html>
<html lang="en"><head>
<meta charset="utf-8" />
<title>News - List</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta content="A fully featured admin theme which can be used to build CRM, CMS, etc." name="description" />
<meta content="Coderthemes" name="author" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css"/><script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
</head><body>
<!-- Begin page -->
<div id="wrapper">
<div class="content-page">
<div class="content">
<!-- Start Content-->
<div class="container-fluid">
<!-- start page title -->
<div class="row">
<div class="col-12">
<div class="page-title-box">
<div class="page-title-right">
</div>
<h4 class="page-title">News - List</h4>
</div>
</div>
</div>
<!-- end page title -->
<div class="row">
<div class="col-xl-12 col-lg-12">
<div class="card-box">
<table id="table-news" class="table table-bordered dt-responsive nowrap" width="100%">
<thead>
<tr>
<th width="5%">No.</th>
<th width="10%">Date</th>
<th width="30%">Title</th>
<th width="25%">Description</th>
<th width="20%">Action</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
<!--- end row -->
</div>
<!-- end container-fluid -->
</div>
<!-- end content -->
</div>
</div>
<script>
$(function() {
var table_news = $('#table-news').DataTable({
"processing":true,
"serverSide":true,
"order":[],
"ajax": {
url : '<?php echo base_url("news/news_list"); ?>',
type: "GET"
},
"columnDefs":[
{
"targets":[0],
"orderable":false,
},
],
});
$("#table-news tbody").on('click', 'button', function() {
var id = $(this).attr('data-id');
if(this.name == "deleteButton") {
var is_delete = confirm("Are your sure?");
if(is_delete) {
$.post('news/delete', {id: id}, function(result) {
$(".result").html(result);
table_news.ajax.reload();
});
}
}
});
});
</script>
</body></html>
Controller
<?php
namespace App\Controllers;
use App\Controllers\BaseController;use App\Models\NewsModel;class News extends BaseController {public function __construct() {
$db = db_connect();
helper(['url', 'form', 'array']);$this->news = new NewsModel($db);$this->ip_address = $_SERVER['REMOTE_ADDR'];
$this->datetime = date("Y-m-d H:i:s");
}public function index() {
$this->list();
}public function list() {
$data = [];
$data['content_title'] = 'News - List';
$data['status_list'] = $this->status->get_all(["status"=>"1"]);
echo view('list', $data);
}public function news_datatable() {
$arrayList = [];
$postData = $this->request->getGet();
$i = $this->request->getGet('start');
$result = $this->news->getRows($postData);
foreach($result as $row) {
$action = '
<a href="'.base_url('news/edit?id='.$row->id).'" class="btn btn-sm btn-primary">
<i class="fe-edit"></i> Edit</a>
<button name="deleteButton" data-id="'.$row->id.'" class="btn btn-sm btn-danger">
<i class="fe-trash"></i> Delete</button>
';
$arrayList [] = [
++$i,
$row->created_at,
$row->title,
$row->description,
$action
];
}
$output = array(
"draw" => $this->request->getGet('draw'),
"recordsTotal" => $this->news->countAll(),
"recordsFiltered" => $this->news->countFiltered($this->request->getGet()),
"data" => $arrayList,
);echo json_encode($output);
}public function delete() {
$id = $this->request->getPost('id');
$where = ['id' => $id];
$result = $this->news->delete($where);
if($result) {
echo "deleted";
}
}
}
Model Page
<?php namespace App\Models;use CodeIgniter\Model;
use CodeIgniter\Database\ConnectionInterface;class NewsModel extends Model {protected $db;
protected $table;
protected $column_order;
protected $column_search;
protected $builder;
public function __construct(ConnectionInterface &$db) {
$this->db =& $db;
$this->table = 'news_info';
$field_names = $this->db->getFieldNames($this->table);
$this->column_order = $this->getColumnOrder($field_names);
$this->column_search = $this->getColumnSearch($field_names);
}public function getColumnOrder($field_names) {
$column_order = [];
$column_order[0] = null;
foreach($field_names as $row) {
$column_order [] = $row;
}
return $column_order;
}public function getColumnSearch($field_names) {
$column_order = [];
foreach($field_names as $row) {
$column_order [] = $row;
}
return $column_order;
}public function getRows($postData) {
$this->_get_datatables_query($postData);
if($postData['length'] != -1){
$this->builder->limit($postData['length'], $postData['start']);
}
$query = $this->builder->get();
return $query->getResult();
}public function countAll(){
$this->builder = $this->db->table($this->table);
return $this->builder->countAllResults();
}public function countFiltered($postData){
$this->_get_datatables_query($postData);
$query = $this->builder->get();
return $this->builder->countAll();
}private function _get_datatables_query($postData){
$this->builder = $this->db->table($this->table);
$i = 0;
// loop searchable columns
foreach($this->column_search as $item){
// if datatable send POST for search
if($postData['search']['value']){
// first loop
if($i===0){
// open bracket
$this->builder->groupStart();
$this->builder->like($item, $postData['search']['value']);
} else{
$this->builder->orLike($item, $postData['search']['value']);
}
// last loop
if(count($this->column_search) - 1 == $i){
// close bracket
$this->builder->groupEnd();
}
}
$i++;
}
if(isset($postData['order'])){
$this->builder->orderBy($this->column_order[$postData['order']['0']['column']], $postData['order']['0']['dir']);
}else if(isset($this->order)){
$order = $this->order;
$this->builder->orderBy(key($order), $order[key($order)]);
}
}}
