I recently started coding in Codeigniter MVC Framework and now i'm stuck with a problem and it's related to Datatables server side processing using AJAX. I have more than 7k rows in my Database Table and if I try to display that data using DataTables Client Side Processing it increases my Page Load Time so i'm trying to do Server Side Processing. I've googled it but didn't find any suitable or required solution. and right now I have this code but this is throwing me an AJAX error.
My Controller
public function fetch_products(){
$this->load->model("test_model");
$list = $this->test_model->get_datatables();
$data = array();
$no = $_POST['start'];
foreach($list as $row){
$no++;
$sub_array = array();
$sub_array[] = $row->tickid;
// $sub_array[] = $row->Updated;
$sub_array[] = null;
$sub_array[] = null;
$sub_array[] = $row->IsVerified;
$sub_array[] = $row->IsProblematic;
$sub_array[] = $row->ImageMain;
$sub_array[] = $row->ID;
$sub_array[] = $row->Category;
$sub_array[] = $row->SubCategory;
$sub_array[] = $row->Brand;
$sub_array[] = $row->Size;
$sub_array[] = $row->Description;
$sub_array[] = $row->AddedBy;
$sub_array[] = $row->DateAdded;
$sub_array[] = $row->ItemCondition;
$sub_array[] = $row->Season;
$sub_array[] = $row->BarCode;
$sub_array[] = $row->UBarCode;
$sub_array[] = $row->BoxNumber;
$sub_array[] = $row->ImageOther;
$sub_array[] = $row->Attributes;
$sub_array[] = $row->Status;
$sub_array[] = $row->PriceMSRP;
$sub_array[] = $row->PriceDiscounted;
$sub_array[] = $row->PriceVIP;
$sub_array[] = $row->PriceWS;
$sub_array[] = $row->Cost;
$sub_array[] = $row->NewBarCodeAddedBy;
$sub_array[] = $row->NewBarCodeAddedOn;
$sub_array[] = $row->data_updated_by;
$sub_array[] = $row->data_updated_date;
$sub_array[] = $row->images_updated_by;
$sub_array[] = $row->images_updated_date;
$data[] = $sub_array;
}
$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->customers->count_all(),
"recordsFiltered" => $this->customers->count_filtered(),
"data" => $data,
);
echo json_encode($output);
My model:
<?php
class Test_model extends CI_Model{
var $table = "list_items";
var $column_search = array("tickid","ImageMain","ID","Category","SubCategory","Brand","Size","Description",
"AddedBy","DateAdded","ItemCondition","Season","BarCode","UBarCode","BoxNumber","ImageOther","Attributes",
"Status","PriceMSRP","PriceDiscounted","PriceVIP","PriceWS","Cost","NewBarCodeAddedBy","data_updated_by",
"data_updated_date","images_updated_by","images_updated_date","NewBarCodeAddedOn","IsVerified","IsProblematic");
var $column_order = array(null,null,"ID","Category","SubCategory",null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
private function _get_datatables_query()
{
$this->db->from($this->table);
$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']);
}
else if(isset($this->order))
{
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
function get_datatables()
{
$this->_get_datatables_query();
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
return $query->result();
}
function count_filtered()
{
$this->_get_datatables_query();
$query = $this->db->get();
return $query->num_rows();
}
public function count_all()
{
$this->db->from($this->table);
return $this->db->count_all_results();
}
}
My view:
<div class="panel panel-default">
<div class="panel-heading">
Existing Products In Database
</div>
<div class="panel-body">
<!-- <div id="users_wrapper" class="dataTables_wrapper form-inline dt-bootstrap">
<div class="row"> -->
<!-- <div class="col-sm-12" id="my_table"> -->
<div class="table-responsive">
<table id="product_data" class="table table-bordered dataTable" style="width: 100%;">
<thead>
<tr role="row">
<th width="auto">Action</th>
<th width="auto">Updated</th>
<th width="auto">Verified</th>
<th width="auto">Problematic</th>
<th width="auto">Front Image</th>
<th width="auto">ID</th>
<th width="auto">Category</th>
<th width="auto">SubCategory</th>
<th width="auto">Brand</th>
<th width="auto">Size</th>
<th width="auto">Description</th>
<th width="auto">Added By</th>
<th width="auto">Uploaded On</th>
<th width="auto">Condition</th>
<th width="auto">Season</th>
<th width="auto">BarCode</th>
<th width="auto">Updated BarCode</th>
<th width="auto">BoxNumber</th>
<th width="auto">Other Images</th>
<th width="auto">Attributes</th>
<th width="auto">Status</th>
<th width="auto">PriceMSRP</th>
<th width="auto">PriceDiscounted</th>
<th width="auto">PriceVIP</th>
<th width="auto">PriceWS</th>
<th width="auto">Cost</th>
<th width="auto">BarCode Updated By</th>
<th width="auto">BarCode Updated Date</th>
<th width="auto">Data Updated By</th>
<th width="auto">Data Updated Date</th>
<th width="auto"> Images Updated By</th>
<th width="auto">Images Updated Date</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
</div>
My Javascript:
//Generating DataTable for Products
$(document).ready(function(){
var tblbuttonsedit = "<button class=\"btn_usr_action_edit\">Edit</button>";
var tblbuttondelete = " <button class=\"btn_usr_action_del\">Delete</button>";
var table_product= $('#product_data').DataTable({
"processing" : true ,
"serverSide" : true,
"scrollY": "500px",
"scrollX": true,
"scrollCollapse": true,
"pagingType": "input",
"order" : [],
"ajax": {"url": ajaxurl +
"Product_database/fetch_products","type":"POST"},
"columnDefs":
[
{
"targets": 0,
"data" : null,
"defaultContent": tblbuttonsedit + tblbuttondelete
},
{
"className": "highlighted_col",
"targets": [3] // highlight ID column
}
]
})
});
