[FIXED] The error in that show from the datatable query

Issue

Can I know why when I use the join query in my get datatable part, it will show the error, but if I just do in normal select query, it will has no problem. Can somebody help me about this issue? Thanks.

Here is the image for my error in view:

Here is the code for my view:

    <div class="container">
        <div class="container">
            <div class="card">
                <div class="card-body">
                    <h1 class="display-4" id="heading"><?php echo $content_heading ?></h1>
                    <div class="row col-12">
                        <button class="btn btn-primary" onclick="reloadTable()"> Reload</button>
                        <button type="button" id="btnFilterClear" class="btn btn-success ml-5" > All</button>
                        <button type="button" id="btnFilterFTKKP" class="btn btn-success ml-2" > FTKKP</button>
                        <button type="button" id="btnFilterFTKEE" class="btn btn-success ml-2" > FTKEE</button>
                        <button type="button" id="btnFilterFTKMA" class="btn btn-success ml-2" > FTKMA</button>
                        <button type="button" id="btnFilterFTKA" class="btn btn-success ml-2" > FTKA</button>
                        <button type="button" id="btnFilterFTKPM" class="btn btn-success ml-2" > FTKPM</button>
                        <button type="button" id="btnFilterFK" class="btn btn-success ml-2" > FK</button>
                        <button type="button" id="btnFilterFIST" class="btn btn-success ml-2" > FIST</button>
                        <button type="button" id="btnFilterFIM" class="btn btn-success ml-2" > FIM</button>
                    </div>
                    <!-- DataTables -->
                    <table class="table table-striped table-hover" id="dataTable">
                        <thead>
                            <tr>
                                            <!--<th>No</th>-->
                                            <th>Subject ID</th>
                                            <th>Subject Name</th>
                                            <th>Department</th>
                                            <th>Action</th>
                            </tr>
                        </thead>
                        <tbody>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

<script>
    var table;
    var base_url = '<?php echo base_url();?>';

    $(document).ready(function() {

    $("#image").change(function() {
            photoPreview(this,"service");
    });

    $("input").keydown(function(){
        $(this).removeClass('is-invalid');
        $(this).next().empty();
    });
    $("textarea").keydown(function(){
        $(this).removeClass('is-invalid');
        $(this).next().empty();
    });
    $("select").change(function(){
        $(this).removeClass('is-invalid');
        $(this).next().empty();
    });
        $("input").change(function(){
        $(this).removeClass('is-invalid');
        $(this).next().empty();
    });

        //Datatables
        table = $('#dataTable').DataTable({

        "processing": true, //Feature control the processing indicator.
        "serverSide": true, //Feature control DataTables' server-side processing mode.
        "order": [], //Initial no order.
        "lengthMenu": [ 5, 10, 25, 50 ],
        "pageLength": 5,
        "language": {
                "infoFiltered": ""
        },

                // Load data for the table's content from an Ajax source
                "ajax": {
                    "url": "<?php echo site_url('attendance/ajax_list_attendance_for_all_student')?>",
                    "type": "POST"
                },

                //Set column definition initialisation properties.
                "columnDefs": [
                {
                        "targets": [ -1 ], //last column
                        "orderable": false, //set not orderable
                    },
                    {
                        "width": "2%", "targets": -1,
                    },
                    ],
                });

            $( "#dataTable_filter" ).addClass( "float-md-right" );
            $( "#dataTable_paginate" ).addClass( "float-md-right" );
            $( "#dataTable_length" ).addClass( "form-inline" );
            $( "#dataTable_length" ).parent().addClass( "my-md-auto" );

        $('#btnFilterClear').on( 'click', function () {
            table.search("", true, false, true).draw();
        } );

        $('#btnFilterFTKKP').on( 'click', function () {
            table.search("Faculty of Chemical and Process Engineering Technology (FTKKP)", true, false, true).draw();
        } );

        $('#btnFilterFTKEE').on( 'click', function () {
            table.search("Faculty of Electrical and Electronics Engineering Technology (FTKEE)", true, false, true).draw();
        } );

        $('#btnFilterFTKMA').on( 'click', function () {
            table.search("Faculty of Mechanical and Automotive Engineering Technology (FTKMA)", true, false, true).draw();
        } );

        $('#btnFilterFTKA').on( 'click', function () {
            table.search("Faculty of Civil Engineering Technology (FTKA)", true, false, true).draw();
        } );

        $('#btnFilterFTKPM').on( 'click', function () {
            table.search("Faculty of Manufacturing and Mechatronic Engineering Technology (FTKPM)", true, false, true).draw();
        } );
        $('#btnFilterFK').on( 'click', function () {
            table.search("Faculty of Computing (FK)", true, false, true).draw();
        } );
        $('#btnFilterFIST').on( 'click', function () {
            table.search("Faculty of Industrial Sciences and Technology (FIST)", true, false, true).draw();
        } );
        $('#btnFilterFIM').on( 'click', function () {
            table.search("Faculty of Industrial Management (FIM)", true, false, true).draw();
        } );
        });

        function reloadTable()
        {
        table.ajax.reload(null,false); //reload datatable ajax
    }

//view Service
function clickviewallstudentattendance(subject_id)
{
        window.location.href = "<?php echo site_url('attendance/student_attendance_by_date/')?>"+subject_id; // redirect to student_attendance_detail
}
</script>

Here is my code for my controller:

    public function ajax_list_attendance_for_all_student()
    {
        $list = $this->attendance->get_datatables_attendance_for_all_student(2); 
        $data = array();
        $no = $_POST['start'];
        $i = 1;
        foreach ($list as $attendance) {
            $no++;
            $row = array();
            //$row[] = $attendance->id;
            $row[] = $attendance->subject_id;
            $row[] = $attendance->subject_name;
            $row[] = $attendance->department;
            $row[] = '<button class="btn btn-warning m-0" href="javascript:void(0)" onclick="clickviewallstudentattendance('."'".$attendance->subject_id."'".')">View</button>';
            $data[] = $row;
        }

        $output = array(
                        "draw" => $_POST['draw'],
                        "recordsTotal" => $this->attendance->count_all_attendance_for_all_student(),
                        "recordsFiltered" => $this->attendance->count_filtered_attendance_for_all_student(2),
                        "data" => $data,
                );
        //output to json format
        echo json_encode($output);
    }

Here is the code for my model:

    private function _get_datatables_query_attendance_for_all_student()
    {
        $this->db->select("a.subject_id, a.subject_name, s.department");
        $this->db->from('attendance a');
        $this->db->join('student s', 'a.student_id=s.student_id', 'inner');
        $this->db->group_by('a.subject_id');

        $i = 0;

        foreach ($this->column_search_attendance_for_all_student 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_attendance_for_all_student) - 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_attendance_for_all_student[$_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_attendance_for_all_student()
    {
        $this->_get_datatables_query_attendance_for_all_student();
        if($_POST['length'] != -1)
            $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        return $query->result();
    }

        function count_filtered_attendance_for_all_student()
    {
        $this->_get_datatables_query_attendance_for_all_student();
        $query = $this->db->get();
        return $query->num_rows();
    }

        public function count_all_attendance_for_all_student()
    {
        $this->db->from($this->table);
        return $this->db->count_all_results();
    }

This is the working query that I do in my model:

    private function _get_datatables_query_student_attendance_for_every_subject_by_lecturer()
    {
        $this->db->select('*');
        $this->db->from('attendance');
        $user_id = $this->session->userdata("id");
        $this->db->where('lecturer_id', $user_id);

        $i = 0;

        foreach ($this->column_search_student_attendance_detail 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_student_attendance_detail) - 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_student_attendance_detail[$_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_student_attendance_for_every_subject_by_lecturer()
    {
        $this->_get_datatables_query_student_attendance_for_every_subject_by_lecturer();
        if($_POST['length'] != -1)
            $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        return $query->result();
    }

        function count_filtered_student_attendance_for_every_subject_by_lecturer()
    {
        $this->_get_datatables_query_student_attendance_for_every_subject_by_lecturer();
        $query = $this->db->get();
        return $query->num_rows();
    }

        public function count_all_student_attendance_for_every_subject_by_lecturer()
    {
        $this->db->from($this->table);
        return $this->db->count_all_results();
    }

Solution

The dataTables error you're getting is a general error that can be triggered if the server responds with anything other than a valid HTTP 2xx response. I see in your ajax that you're using a type of POST, and CodeIgniter usually will require CSRF with POSTs. Either switch this to GET, or add CSRF fields to the POST request, as below:

var csrfdata = {<?= csrf_token() ?>:"<?= csrf_hash() ?>"};


"ajax": {
            "url": "<?php echo site_url('attendance/ajax_list_attendance_for_all_student')?>",
            "type": "POST",
            "data": csrfdata
        },

For reference, CodeIgniter Security page: https://codeigniter.com/user_guide/libraries/security.html

Answered By - James