I'm using Codeigniter and Data table server-side processing to get data from Database
My Controller Function
public function all_list()
{
$this->load->model('courses_model');
$list = $this->courses_model->get_all_datatables();
$data = array();
$no = $_POST['start'];
foreach ($list as $courses) {
$no++;
$row = array();
$row[] = $no;
$row[] = '<img alt="course" src="'.base_url('assets/template_files/images/courses/thumb/'.$courses->GeneralPhoto). '" width="150" height="100">';
$row[] = $courses->CourseName;
$row[] = $courses->TeacherName;
$row[] = date('Y-m-d',strtotime($courses->CourseStartDate));
$row[] = date('Y-m-d',strtotime($courses->CourseEndDate));
$row[] = $courses->PeriodWeekly;
$row[] = $courses->CategoryName;
$row [] ="<a href='$courses->CourseID' ><button type='button' class='btn btn-xs btn-primary'>عرض الدورة</button></a>";
$data[] = $row;
}
$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->courses_model->count_all(),
"recordsFiltered" => $this->courses_model->count_filtered(),
"data" => $data,
);
//output to json format
echo json_encode($output);
}
My Model Functions
//All
var $table = '(
SELECT
courses.*
,(SELECT CourseCategoryName FROM coursecategories WHERE coursecategories.CourseCategoryID=courses.CourseCategoryID) AS CategoryName
,(SELECT GROUP_CONCAT(gu.Name) AS TeacherName
FROM (SELECT ct.CourseID AS CourseID, GROUP_CONCAT(t.StaffID) AS StaffID
FROM courseteachers AS ct
INNER JOIN staff AS t ON ct.StaffTeacherID = t.StaffID
GROUP BY CourseID) as res
INNER JOIN generaluser AS gu ON gu.GeneralUserID = res.StaffID
WHERE CourseID=courses.CourseID) AS TeacherName
FROM courses
) temp';
var $column_search = array('CourseID','GeneralPhoto','CourseName','TeacherName','CourseStartDate','CourseEndDate','PeriodWeekly','CategoryName'); //set column field database for datatable searchable
var $order = array('CourseID' => 'desc'); // default order
private function _get_datatables_query($term='')
{
//the query
$this->db->from($this->table);
$i = 0;
foreach ($this->column_search as $item) // loop column
{
if($term) // if datatable send POST for search
{
if($i===0) // first loop
{
// open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
$this->db->like($item,$term);
}
else
{
$this->db->or_like($item, $term);
}
}
$i++;
}
if(isset($this->order))
{
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
function get_all_datatables()
{
$term = $_POST['search']['value'];
$this->_get_datatables_query($term);
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
return $query->result();
}
function count_filtered()
{
$term = $_POST['search']['value'];
$this->_get_datatables_query($term);
$query = $this->db->get();
return $query->num_rows();
}
public function count_all()
{
$this->db->from($this->table);
return $this->db->count_all_results();
}
My Script
<script>
$(function () {
$("#allData").DataTable({
"processing": true, //Feature control the processing indicator.
"serverSide": true, //Feature control DataTables' server-side processing mode.
"order": [], //Initial no order.
// Load data for the table's content from an Ajax source
"ajax": {
"url": "<?php echo site_url('Course/all_list')?>",
"type": "POST",
},
//Set column definition initialisation properties.
"columnDefs": [
{
"targets": [ 0 ], //first column / numbering column
"orderable": false, //set not orderable
},
],
"language":
{
"sProcessing": "جارٍ التحميل...",
"sLengthMenu": "أظهر _MENU_ مدخلات",
"sZeroRecords": "لم يعثر على أية سجلات",
"sInfo": "إظهار _START_ إلى _END_ من أصل _TOTAL_ مدخل",
"sInfoEmpty": "يعرض 0 إلى 0 من أصل 0 سجل",
"sInfoFiltered": "(منتقاة من مجموع _MAX_ مُدخل)",
"sInfoPostFix": "",
"sSearch": "ابحث:",
"sUrl": "",
"oPaginate": {
"sFirst": "الأول",
"sPrevious": "السابق",
"sNext": "التالي",
"sLast": "الأخير"
}
},
});
});
</script>
it works properly and I get the result
All the functions of the table work perfectly (pagination,server processing....), even search is working perfectly with English word but not ًWith Arabic, when I type the first letter of the word an error message appears
I have tried many options like adding
header( 'Content-Type: application/json; charset=utf-8' );
and
echo json_encode($output,JSON_UNESCAPED_UNICODE);
in the controller but it didn't work what should I do?
The Console Output
Failed to load resource: the server responded with a status of 500 (Internal Server Error)
The Network Tab
And
And The Search value is Arabic in Headers
