Data table Server Side Processing Arabic search won’t work correctly

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

Answer

I have got the solution from here , it was because of Date fields in the table so I got the problem

Illegal mix of collations for operation 'like' while searching

Changing the line

var $column_search = array('CourseID','GeneralPhoto','CourseName','TeacherName','CourseStartDate','CourseEndDate','PeriodWeekly','CategoryName');

 var $column_search = array('CourseName','TeacherName','CategoryName');

Solved the problem.

Advertisement

Answer