About Lesson
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.
1. Create a list.php file in applications/views/ directory
<!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="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>
<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>
<div class="row">
<div class="col-xl-12 col-lg-12">
<div class="card-box">
<table id="table-news"
class="table table-bordered"
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>
</div>
</div>
</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>2. Create a model file News_defer_model.php in the applications/models/ directory.
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class News_defer_model extends CI_Model {
public function __construct() {
parent::__construct();
$this->is_admin = ($this->session->userdata('login_role') ==
"admin") ? true : false;
$this->login_id = $this->session->userdata('login_id');
//
$this->table = 'news_info';
$fields = $this->db->list_fields($this->table);
$this->column_order = $this->getColumnOrder($fields);
$this->column_search = $this->getColumnSearch($fields);
}
public function getColumnOrder($fields) {
$list = [];
$list[0] = null;
foreach($fields as $field) {
$list[] = $field;
}
return $list;
}
public function getColumnSearch($fields) {
$list = [];
foreach($fields as $field) {
$list[] = $field;
}
return $list;
}
public function getRows($postData){
$this->_get_datatables_query($postData);
if($postData['length'] != -1){
$this->db->limit($postData['length'], $postData['start']);
}
$query = $this->db->get();
return $query->result();
}
public function countAll(){
$this->db->from($this->table);
return $this->db->count_all_results();
}
public function countFiltered($postData){
$this->_get_datatables_query($postData);
$query = $this->db->get();
return $query->num_rows();
}
private function _get_datatables_query($postData){
$this->db->from($this->table);
if($this->is_admin) {
$this->db->where(["status"=>$postData['status']]);
} else {
$this->db->where(["status"=>$postData['status'], "created_by"=>$this->login_id]);
}
$this->db->order_by('created_at', 'DESC');
$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->db->group_start();
$this->db->like($item, $postData['search']['value']);
}else{
$this->db->or_like($item, $postData['search']['value']);
}
// last loop
if(count($this->column_search) - 1 == $i){
// close bracket
$this->db->group_end();
}
}
$i++;
}
if(isset($postData['order'])){
$this->db->order_by(
$this->column_order[$postData['order']['0']['column']],
$postData['order']['0']['dir']
);
}else if(isset($this->order)){
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
}3. Create a controller file News.php in the applications/controllers/ directory.
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 indexes 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)
<?php
defined('BASEPATH') OR exit('No direct script access allowed');
class News extends MY_Controller {
public function __construct() {
parent::__construct();
$this->load->model('News_model', 'news');
//
$this->ip_address = $_SERVER['REMOTE_ADDR'];
$this->datetime = date('Y-m-d H:i:s');
$this->login_id = $this->session->userdata('login_id');
$this->login_name = $this->session->userdata('login_name');
}
public function index() {
$this->list();
}
public function list() {
$data = [];
$data['content_title'] = 'News - List';
$data['status_list'] = $this->status->get_all(["status"=>"1"]);
$this->load->view('list', $data);
}
public function news_datatable() {
$arrayList = [];
$result = $this->news_defer->getRows($this->input->get());
$i = $this->input->get('start');
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,
nice_date($row->created_at, 'd-m-Y H:i:s'),
$row->title,
$row->description,
$action
];
}
$output = array(
"draw" => $this->input->get('draw'),
"recordsTotal" => $this->news_defer->countAll(),
"recordsFiltered" => $this->news_defer->countFiltered($this->input->get()),
"data" => $arrayList,
);
echo json_encode($output);
}
public function delete() {
$id = $this->input->post('id');
$where = ['id' => $id];
$result = $this->news->delete($where);
if($result) {
echo "deleted";
}
}
}