Codeigniter Tutorials

Home » Codeigniter »

How to load datatable using Ajax Request

File Name : view page

<table id="book-table" class="table ">
<thead>
<tr>
<th>Location</th>
<th>Location Date & Time</th>
<th>Created Date</th>
<th>Action</th>
</tr>
</thead>
<tbody>
</tbody>
</table>

public function books_page()
{

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

$books = $this->books_model->get_books();

$data = array();

foreach($books->result() as $r) {

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

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

These 3 variables are sent to our page as $_GET requests automatically by Datatables. $draw is a unique number sent to our page so that Datatables can handle the request correctly. We output this variable at the end so the plugin knows it has the right request.

$start and $length are used for pagination. We'll be using these two variables in our database model later on, but for now, think of them as being used in terms of the starting point from which we grab records (i.e. we want to start from record 5) and length is how many records to return.

$books = $this->books_model->get_books();
$data = array();

The $data array will be used to store our table data.

foreach($books->result() as $r) {

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

Next we loop through our database results and store the data in an array. The fields must correspond to the position in which you created them in your HTML table.

$output = array(
"draw" => $draw,
"recordsTotal" => $books->num_rows(),
"recordsFiltered" => $books->num_rows(),
"data" => $data
);
echo json_encode($output);
exit();

The pageLength option is sent to our controller script in the variable $length. You can change it to whatever value you want.

Datatables likes to read our data in json format.

we make a new array called $output which contains the $draw variable.

the total number of rows and our $data array which contains the table output.

Finally we need to use PHP's json_encode function on our new $output array and exit the program.

Next Example :-

<table class="table" id="cate_list">
<thead>
<tr>
<th>ID</th>
<th>Category Name</th>
<th>Slug</th>
<th>Category Url</th>
<th>Created Date</th>
<th>Action</th>

</tr>
</thead>
<tbody>
</tbody>
</table>

public function get_categorylist()
{
$getdata = $this->Admin_model->category_get();
$data = array();
foreach ($getdata as $value)
{
$row = array();
$row[] = $value->cat_id;
$row[] = $value->category_name;
$row[] = $value->category_slug;
$row[] = $value->category_link;
$row[] = $value->created_date;
$row[] = '<a href="'.base_url().'admin/category/editcategory/'.$value->cat_id.'"> <button class="btn btn-default btn-rounded btn-sm">
<span class="fa fa-pencil"></span>
</button></a>
  
<a href="javascript:void(0)"><button class="btn btn-danger btn-rounded btn-sm" onclick="delfunction('.$value->cat_id.')"><span class="fa fa-times"></span></button></a>
';
$data[] = $row;
}

$output = array(
"data" => $data,
);

echo json_encode($output);
exit();
}

function category_get()
{
$this->db->select('*');
$this->db->from('category');
$query = $this->db->get();
return $query->result();
}

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

$getdata = $this->Admin_model->category_get();
$data = [];
foreach($getdata as $value) {
$data[] = array(
$value->cat_id,
$value->category_name,
$value->category_slug,
$value->category_link,
$value->created_date,

'<a href="'.base_url().'admin/category/editcategory/'.$value->cat_id.'"> <button class="btn btn-default btn-rounded btn-sm">
<span class="fa fa-pencil"></span>
</button></a>
  
<a href="javascript:void(0)"><button class="btn btn-danger btn-rounded btn-sm" onclick="delfunction('.$value->cat_id.')"><span class="fa fa-times"></span></button></a>
'

);
}
$result = array(
"draw" => $draw,
"recordsTotal" => $this->Admin_model->recordsTotal(),
"recordsFiltered" => $this->Admin_model->recordsTotal(),
"data" => $data
);
echo json_encode($result);
exit();
}

function recordsTotal()
{
$this->db->select('*');
$this->db->from('category');
$query = $this->db->get();
return $query->num_rows();
}

// another method
/*
public function get_categorylist()
{
$getdata = $this->Admin_model->category_get();
$data = array();
foreach ($getdata as $value)
{
$edit = '<a href="'.base_url().'event-edit/'.$value->id.'" class="btn btn-sm btn-success">Edit</a>';
$row = array($value->cat_id,$value->category_name,$value->category_slug,$value->category_link, $value->created_date ,$edit);
$data[] = $row;
}

$output = array(
"data" => $data,
);

echo json_encode($output);
exit();
}
*/

Example : datatabel load using ajax dynamically

<table class="table" id="mytable">
<thead>
<tr>

<th>Id</th>
<th>Class Name</th>
<th>Created_at</th>
<th>Updated_at</th>
<th>Action</th>
</tr>
</thead>
<tbody>

</tbody>
</table>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>

<script>
$(document).ready(function(e){

//var base_url = "<?php echo base_url();?>";
$('#mytable').DataTable({

"pageLength" : 10,
"serverSide": true,
"order": [[0, "asc" ]],
"ajax":{
url : '<?php echo base_url()?>admin/class_stud/classdetailis',
type : 'POST'
},
}); // End of DataTable
}); // End Document Ready Function

</script>

Controller :- class_stud

/*
public function classdetailis()
{
$getdata = $this->Admin_model->getclass();
$data = array();
foreach ($getdata as $value)
{
$row = array();
$row[] = $value->id;
$row[] = $value->class_name;
$row[] = $value->created_at;
$row[] = $value->updated_at;
$row[] = '<a href="'.base_url().'admin/class_stud/editclassdetailis/'.$value->id.'"> <button class="btn btn-success btn-rounded btn-sm">

Edit</button></a>
  
<a href="javascript:void(0)"><button class="btn btn-danger btn-rounded btn-sm" onclick="delfunction('.$value->id.')"><span class="fa fa-times"></span></button></a>
';
$data[] = $row;
}

$output = array(
"data" => $data,
);

echo json_encode($output);
exit();
}

*/

Controller : class_stud

public function classdetailis()
{
$draw = intval($this->input->post("draw"));
$start = intval($this->input->post("start"));
$length = intval($this->input->post("length"));
$order = $this->input->post("order");
$search= $this->input->post("search");
$search = $search['value'];

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

if($dir != "asc" && $dir != "desc")
{
$dir = "desc";
}
$valid_columns = array(
0=>'id',
1=>'class_name',
2=>'class_section',
3=>'created_at',
4=>'updated_at'

);
if(!isset($valid_columns[$col]))
{
$order = null;
}
else
{
$order = $valid_columns[$col];
}
if($order !=null)
{
$this->db->order_by($order, $dir);
}

if(!empty($search))
{
$x=0;
foreach($valid_columns as $sterm)
{
if($x==0)
{
$this->db->like($sterm,$search);
}
else
{
$this->db->or_like($sterm,$search);
}
$x++;
}
}
$this->db->limit($length,$start);

$classinfo = $this->db->get("class_details");
//$classinfo = $this->Admin_model->getclass();

$data = array();
foreach($classinfo->result() as $rows)
{
$data[]= array(
$rows->id,
$rows->class_name,
$rows->created_at,
$rows->updated_at,

'<a href="'.base_url().'admin/class_stud/editclassdetailis/'.$rows->id.'" class="btn btn-warning mr-1">Edit</a>
<a href="javascript:void(0)" class="btn btn-danger mr-1" onclick="delfunction('.$rows->id.')">Delete</a>'

);
}

$total_class = $this->Admin_model->totalclass();
$output = array(
"draw" => $draw,
"recordsTotal" => $total_class,
"recordsFiltered" => $total_class,
"data" => $data
);
echo json_encode($output);
exit();
}

model :- Admin_model

public function getclass()
{
$this->db->select('*');
$this->db->from('class_details');
$query = $this->db->get();
return $query->result();
}

public function totalclass()
{
$query = $this->db->select("COUNT(*) as num")->get("class_details");
$result = $query->row();
if(isset($result)) return $result->num;
return 0;
}

Example :-

<table class="table display text-nowrap" id="teacherlist">
<thead>
<tr>
<th>
<div class="form-check">
<input type="checkbox" class="form-check-input checkAll">
<label class="form-check-label">ID</label>
</div>
</th>
<th>Photo</th>
<th>Name</th>
<th>Gender</th>
<th>DOB</th>
<th>Class</th>
<th>Section</th>
<th>E-mail</th>
<th>Phone</th>
<th>Adhar</th>
<th>Address</th>
<th>city</th>
<th>pin</th>
<th>state</th>
<th>Action</th>
</tr>
</thead>
<tbody>

</tbody>
</table>



<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script>

<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script>

<script>

$(document).ready(function(e){

//var base_url = "<?php echo base_url();?>";
$('#teacherlist').DataTable({

"pageLength" : 10,
"serverSide": true,
"order": [[0, "asc" ]],
"ajax":{
url : '<?php echo base_url()?>admin/teacher/get_teacherlist',
type : 'POST'
},
}); // End of DataTable
}); // End Document Ready Function

</script>

controller:- teacher/get_teacherlist

/*
public function get_teacherlist()
{
$getdata = $this->Admin_model->getclass();
$data = array();
foreach ($getdata as $value)
{
$row = array();
$row[] = $value->id;
$row[] = $value->class_name;
$row[] = $value->created_at;
$row[] = $value->updated_at;
$row[] = '<a href="'.base_url().'admin/class_stud/editclassdetailis/'.$value->id.'"> <button class="btn btn-success btn-rounded btn-sm">

Edit</button></a>
  
<a href="javascript:void(0)"><button class="btn btn-danger btn-rounded btn-sm" onclick="delfunction('.$value->id.')"><span class="fa fa-times"></span></button></a>
';
$data[] = $row;
}

$output = array(
"data" => $data,
);

echo json_encode($output);
exit();
}

*/

public function get_teacherlist()
{
$draw = intval($this->input->post("draw"));
$start = intval($this->input->post("start"));
$length = intval($this->input->post("length"));
$order = $this->input->post("order");
$search= $this->input->post("search");
$search = $search['value'];

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

if($dir != "asc" && $dir != "desc")
{
$dir = "desc";
}
$valid_columns = array(
0=>'id',
1=>'teacherpic',
2=>'first_name',
3=>'gender',
4=>'dob',
5=>'class_name',
6=>'email',
7=>'mobileno',
8=>'adharno',
9=>'address',
10=>'city',
11=>'pincode',
12=>'state'

);
if(!isset($valid_columns[$col]))
{
$order = null;
}
else
{
$order = $valid_columns[$col];
}
if($order !=null)
{
$this->db->order_by($order, $dir);
}

if(!empty($search))
{
$x=0;
foreach($valid_columns as $sterm)
{
if($x==0)
{
$this->db->like($sterm,$search);
}
else
{
$this->db->or_like($sterm,$search);
}
$x++;
}
}
$this->db->limit($length,$start);

$url = base_url()."assets/admin/images/teachers/";

$classinfo = $this->db->get("teacher_details");
//$classinfo = $this->Admin_model->get_teachers();

$data = array();
foreach($classinfo->result() as $rows)
{
$data[]= array(
$rows->id,
'<img src="'.$url.$rows->teacherpic.'"/>',
$rows->first_name,
$rows->gender,
$rows->dob,
get_class_name($rows->class_name),
$rows->class_section,
$rows->email,
$rows->mobileno,
$rows->adharno,
$rows->address,
$rows->city,
$rows->pincode,
$rows->state,

'<a href="'.base_url().'admin/class_stud/editteacher/'.$rows->id.'" class="btn btn-warning mr-1">Edit</a>
<a href="javascript:void(0)" class="btn btn-danger mr-1" onclick="delfunction('.$rows->id.')">Delete</a>'

);
}

$totalteacher = $this->Admin_model->total_teacher();
$output = array(
"draw" => $draw,
"recordsTotal" => $totalteacher,
"recordsFiltered" => $totalteacher,
"data" => $data
);
echo json_encode($output);
exit();
}

public function get_teachers()
{
$this->db->select('*');
$this->db->from('teacher_details');
$query = $this->db->get();
return $query->result();
}

public function total_teacher()
{
$query = $this->db->select("COUNT(*) as num")->get("teacher_details");
$result = $query->row();
if(isset($result)) return $result->num;
return 0;
}

DataTables AJAX Pagination with Search and Sort in CodeIgniter

Example :- employees Table

CREATE TABLE `employees` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`emp_name` varchar(60) NOT NULL,
`salary` varchar(50) NOT NULL,
`gender` varchar(10) NOT NULL,
`city` varchar(80) NOT NULL,
`email` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Employee_model.php :

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

class Employee_model extends CI_Model {

function getEmployees($postData=null){

$response = array();

## Read value
$draw = $postData['draw'];
$start = $postData['start'];
$rowperpage = $postData['length']; // Rows display per page
$columnIndex = $postData['order'][0]['column']; // Column index
$columnName = $postData['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $postData['order'][0]['dir']; // asc or desc
$searchValue = $postData['search']['value']; // Search value

## Search
$searchQuery = "";
if($searchValue != ''){
$searchQuery = " (emp_name like '%".$searchValue."%' or email like '%".$searchValue."%' or city like'%".$searchValue."%' ) ";
}

## Total number of records without filtering
$this->db->select('count(*) as allcount');
$records = $this->db->get('employees')->result();
$totalRecords = $records[0]->allcount;

## Total number of record with filtering
$this->db->select('count(*) as allcount');
if($searchQuery != '')
$this->db->where($searchQuery);
$records = $this->db->get('employees')->result();
$totalRecordwithFilter = $records[0]->allcount;

## Fetch records
$this->db->select('*');
if($searchQuery != '')
$this->db->where($searchQuery);
$this->db->order_by($columnName, $columnSortOrder);
$this->db->limit($rowperpage, $start);
$records = $this->db->get('employees')->result();

$data = array();

foreach($records as $record ){

$data[] = array(
"emp_name"=>$record->emp_name,
"email"=>$record->email,
"gender"=>$record->gender,
"salary"=>$record->salary,
"city"=>$record->city
);
}

## Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);

return $response;
}

}

Employee.php

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

class Employee extends CI_Controller {

public function __construct(){

parent::__construct();
$this->load->helper('url');

// Load model
$this->load->model('Employee_model');

}

public function index(){

// load view
$this->load->view('emp_view');

}

public function empList(){

// POST data
$postData = $this->input->post();

// Get data
$data = $this->Employee_model->getEmployees($postData);

echo json_encode($data);
}

}

emp_view.php

<!DOCTYPE html>
<html>
<head>
<title>DataTables AJAX Pagination with Search and Sort in CodeIgniter</title>


<link href='//cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css' rel='stylesheet' type='text/css'>


<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>


<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>

</head>
<body>


<table id='empTable' class='display dataTable'>

<thead>
<tr>
<th>Employee name</th>
<th>Email</th>
<th>Gender</th>
<th>Salary</th>
<th>City</th>
</tr>
</thead>

</table>


<script type="text/javascript">
$(document).ready(function(){
$('#empTable').DataTable({
'processing': true,
'serverSide': true,
'serverMethod': 'post',
'ajax': {
'url':'<?=base_url()?>index.php/Employee/empList'
},
'columns': [
{ data: 'emp_name' },
{ data: 'email' },
{ data: 'gender' },
{ data: 'salary' },
{ data: 'city' },
]
});
});
</script>
</body>
</html>

Example:

the DataTables working with the client-side data. But if your web application handles a large amount of data from the database, you must consider using server-side processing option in Datatables

CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Controller : Members.Php

The Members controller contains 3 functions, __construct(), index(), and getLists().

__construct() – Load the member model.
index() – Load the member’s list view.
getLists() – This function handles server-side processing and is called by Ajax method of DataTable.
Fetch member’s records from the database using getRows() function of Member model.
The data is filtered based on the $_POST parameters posted by the Datatables.
Prepare data for Datatables.
Render output as JSON format.

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

class Members extends CI_Controller{

function __construct(){
parent::__construct();
$this->load->model('member');
}

function index(){
$this->load->view('members/index');
}

function getLists(){
$data = $row = array();

// Fetch member's records
$memData = $this->member->getRows($_POST);

$i = $_POST['start'];
foreach($memData as $member){
$i++;
$created = date( 'jS M Y', strtotime($member->created));
$status = ($member->status == 1)?'Active':'Inactive';
$data[] = array($i, $member->first_name, $member->last_name, $member->email, $member->gender, $member->country, $created, $status);
}

$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->member->countAll(),
"recordsFiltered" => $this->member->countFiltered($_POST),
"data" => $data,
);

// Output to JSON format
echo json_encode($output);
}

}

Model (Member.Php)

__construct() – Specify the table name, column fields order, searchable column fields, and recordset order.
getRows() – Fetch the members data from the database. Returns the filtered records based on the specified parameters in the POST method.
countAll() – Count all the records of members table.
countFiltered() – Count filtered records based on the posted parameters.
_get_datatables_query() – This is a helper function of Member model. Perform the SQL queries needed for an server-side processing requested.

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

class Member extends CI_Model{

function __construct() {
// Set table name
$this->table = 'members';
// Set orderable column fields
$this->column_order = array(null, 'first_name','last_name','email','gender','country','created','status');
// Set searchable column fields
$this->column_search = array('first_name','last_name','email','gender','country','created','status');
// Set default order
$this->order = array('first_name' => 'asc');
}

// Fetch members data from the database

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

// Count all records

public function countAll(){
$this->db->from($this->table);
return $this->db->count_all_results();
}

// Count records based on the filter params
public function countFiltered($postData){
$this->_get_datatables_query($postData);
$query = $this->db->get();
return $query->num_rows();
}

// Perform the SQL queries needed for an server-side processing requested
private function _get_datatables_query($postData){

$this->db->from($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->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)]);
}
}

}

View (Members/Index.Php)

Use the DataTable() method to initialize the Datatables plugin.

Specify the selector ID (#memListTable) of the HTML table where the DataTables will be attached.
Configure the following to enable the server-side processing:
Set the processing option to true.
Set the serverSide option to true.
Set the Ajax source URL from where DataTables will fetch the server-side data.

<link rel="stylesheet" type="text/css" href="<?php echo base_url('assets/DataTables/datatables.min.css'); ?>"/>


<script src="<?php echo base_url('assets/js/jquery.min.js'); ?>"></script>


<script type="text/javascript" src="<?php echo base_url('assets/DataTables/datatables.min.js'); ?>"></script>

<table id="memListTable" class="display" style="width:100%">
<thead>
<tr>
<th>#</th>
<th>First name</th>
<th>Last name</th>
<th>Email</th>
<th>Gender</th>
<th>Country</th>
<th>Created</th>
<th>Status</th>
</tr>
</thead>
<tfoot>
<tr>
<th></th>
<th>First name</th>
<th>Last name</th>
<th>Email</th>
<th>Gender</th>
<th>Country</th>
<th>Created</th>
<th>Status</th>
</tr>
</tfoot>
</table>

<script>
$(document).ready(function(){
$('#memListTable').DataTable({
// Processing indicator
"processing": true,
// DataTables server-side processing mode
"serverSide": true,
// Initial no order.
"order": [],
// Load data from an Ajax source
"ajax": {
"url": "<?php echo base_url('members/getLists/'); ?>",
"type": "POST"
},
//Set column definition initialisation properties
"columnDefs": [{
"targets": [0],
"orderable": false
}]
});
});
</script>

Itechtuto