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

Question

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

You are getting a server error. Can you show us this error from the `Network` tab of your browser under the current request? — Fotis, Sep 02 '17 at 22:07
Hey man, what's inside $_POST['search']['value'] ? try to echo it out to see if it exists or not — Sletheren, Sep 02 '17 at 22:20
$_POST['search']['value'] it is used by Datatable plugin to send data to the server using Ajax @Sletheren — Nidal, Sep 02 '17 at 22:23
yes I know, but does it have a value? meaning does the plugin send that value to the controller? — Sletheren, Sep 02 '17 at 22:33
I don't know what database you're using but you might look at the encoding used there and see if it's encoding arabic correctly. See this SO answer [UTF-8 all the way](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through). — ourmandave, Sep 03 '17 at 00:25
If you look at the network tab in browser devtools, it will show the POST request which is failing. If you click that request, you will be able to see headers, preview and response. What is the response? `Internal Server Error` is just a generic msg, the real problem will be in the actual response from the server, and also in your server logs. What is it? — Don't Panic, Sep 03 '17 at 15:58
I see that helped you solve your problem. Always start with the actual error! :-) — Don't Panic, Sep 04 '17 at 10:06

score 1 · Answer 1 · answered Sep 03 '17 at 17:25

To me it sounds very much like you simply not set proper utf8 charset for the database connection:

https://www.codeigniter.com/user_guide/database/connecting.html

$config['char_set'] = 'utf8';
$config['dbcollat'] = 'utf8_general_ci';
$this->load->database($config);

Is sufficient for arabic. If you need JSON_UNESCAPED_UNICODE something is wrong.

Check what charset and collation the courses, courseteachers, coursecategories and what else tables you are using is set to. If they not have utf8 and utf8_general_ci you can update with :

alter table courses convert to character set utf8 collate utf8_general_ci;

But take a backup of the tables first!

I have no practical experience with codeigniter, but your error seems very familiar and it is easy to reproduce the exact behaviour when json_encode'ing language specific letters from a PHP backend to dataTables, where the connection charset is different from utf8, or the database or the table not have proper charset and collation settings.

Thanks for your answer it was helpful – Nidal Sep 04 '17 at 09:32 — Nidal, Sep 04 '17 at 09:32

score 1 · Accepted Answer · answered Sep 04 '17 at 09:27

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');

to

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

Solved the problem.

score 0 · Answer 3 · answered Aug 31 '17 at 22:39

0

If you're expecting $_POST data, you probably need to use when setting UTF-8:

headers: {
  'Content-Type': 'application/x-www-form-urlencoded;charset=UTF-8'
}

instead of application/json. This is all per this answer.

answered Aug 31 '17 at 22:39

ourmandave

1,505
2
15
49

I have tried your solution and it didn't work for me, the same old problem – Nidal Sep 02 '17 at 09:34

Fotis · Answer 4 · 2017-09-02T22:53:11.240

You're getting a JavaScript error because the response is invalid (error). Looks like the $_POST['search'] is undefined which causes the trouble, meaning that the search variable is not getting posted to the server, or the server filters it out, which could be caused from what is described in the answer of this question.

Also, make sure you're using $this->input->post('search') instead of $_POST['search'].

Finally, to wipe out all possibilities, consider adding contentType inside the ajax entry on your datatables initialisation if you're using jQuery >= 1.5 or beforeSend if you're using an older version like the following:

 $("#allData").DataTable({
...
            // Load data for the table's content from an Ajax source
            "ajax": {
                "url": "<?php echo site_url('Course/all_list')?>",
                "type": "POST",
                "contentType": 'application/x-www-form-urlencoded; charset=UTF-8',
            },
...

I used `$this->input->post()`instead of `$_POST` and I used `utf8_decode()` function as mentioned in of the answers in your link, the error isn't shown any more but it return empty result (No records) even for one character search — Nidal, Sep 02 '17 at 22:56
What's the value being posted under `search`? Check the headers tab in the request that is being sent out — Fotis, Sep 02 '17 at 22:59

score 0 · Answer 5 · edited Mar 16 '22 at 10:30

0

You must add "accept":"UTF-8" in DataTable jquery config:

$("#allData").DataTable({
    "accept":"UTF-8"
})

edited Mar 16 '22 at 10:30

Elikill58

4,050
24
23
45

answered Mar 16 '22 at 09:07

Ola Nasr

1
1

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

5 Answers5