codeigniter 数据表服务器端处理阿拉伯语搜索无法正常工作

k2arahey  于 2022-12-07  发布在  其他
关注(0)|答案(5)|浏览(159)

我使用Codeigniter和Data table server-side processing从数据库获取数据
我的控制器功能

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

我的模型函数

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

我的脚本

<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>

它工作正常,我得到结果

表的所有功能都工作得很好(分页,服务器处理....),甚至搜索英语单词也工作得很好,但阿拉伯语单词就不行了,当我输入单词的第一个字母时,会出现错误信息

我已经尝试了许多选项,例如添加

header( 'Content-Type: application/json; charset=utf-8' );

echo json_encode($output,JSON_UNESCAPED_UNICODE);

在控制器中,但它不工作,我该怎么办?
控制台输出

Failed to load resource: the server responded with a status of 500 (Internal Server Error)

网络选项卡


在标题中搜索值为阿拉伯语


指令集

1bqhqjot

1bqhqjot1#

对我来说,这听起来很像是您没有为数据库连接设置正确的utf8字符集:


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

对于阿拉伯语来说已经足够了。如果你需要JSON_UNESCAPED_UNICODE,那就有问题了。
检查coursescourseteacherscoursecategories以及您正在使用的其他表所设置的字符集和排序规则。如果它们没有utf8utf8_general_ci,您可以使用以下命令进行更新:

alter table courses convert to character set utf8 collate utf8_general_ci;

但请先备份表!

我没有使用codeigniter的实际经验,但是您的错误似乎非常熟悉,当json_encode将特定语言的字母从PHP后端发送到dataTables时,很容易重现确切的行为,其中连接字符集与utf8不同,或者数据库或表没有正确的字符集和排序规则设置。

bbmckpt7

bbmckpt72#

我已经从here得到了解决方案,这是因为表中的日期字段,所以我得到了问题

Illegal mix of collations for operation 'like' while searching

更改行

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

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

解决了问题。

kuhbmx9i

kuhbmx9i3#

如果您需要$_POST数据,则在设置UTF-8时可能需要使用:

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

而不是application/json. This is all per this answer

6kkfgxo0

6kkfgxo04#

你得到了一个JavaScript错误,因为响应无效(错误)。看起来$_POST['search']是未定义的,这导致了这个问题,意味着search变量没有被发送到服务器,或者服务器过滤掉了它,这可能是由this question的答案中所描述的原因引起的。
此外,请确保使用的是$this->input->post('search')而不是$_POST['search']
最后,为了排除所有可能性,如果使用jQuery〉= 1.5,考虑在datatables初始化的ajax条目中添加contentType;如果使用的是较旧的版本,则添加beforeSend,如下所示:

$("#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',
            },
...
y3bcpkx1

y3bcpkx15#

必须在DataTable jquery配置中添加“accept”:“UTF-8”:

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

相关问题