How to fix DataTables paging and search box in php codeigniter?

user_11224377 0
Viewed 899

I've been debugging this error and I still can't see where is the error. I can't determine the exact error if it is in the view or model or script. Need help to fix this bug. Below are my source code. My issues are the search box is not working and the paging is also not working

Here is my code for html view

The issue on my html view is, it does not filter or limit the rows. it shows all the rows. For example I have 100 rows, will display 100 rows even though I limit it to 10.

<div class="card-body">
    <div class="table-responsive">
         <table id="datatable" class="table table-bordered table-striped text-gray-900">
            <thead>
                <tr>
                     <th class="text-center">Name</th>
                    <th class="text-center">Date of Application</th>
                    <th class="text-center">Purpose</th>
                    <th class="text-center">Action</th>
                    <th class="text-center">Status</th>
                </tr>
            </thead>
            <tbody></tbody>
        </table>
    </div>
</div>

Here is my JS script

I don't know where exactly is the error on these code. The error does not even show in the console log

$(function() {
'use strict';

  var datatable = $('#datatable');

  $('#datatable').DataTable({
      dom: 'lfrtipr',
      ajax: base_url + 'Home/get_application_request',
      type: 'post',
      processing: true,
      order:[],
      serverSide: true,
      paging: true,
      columns: [

      {data: 'name'},
      {data: 'date_of_application'},
      {data: 'purpose'},
      {render: function(data, type, row){
                  var action = '<a href="'+base_url+'Application_request/Request_'+row.EMP_NO+'" class="sd_link">Action</a>';
                  return action;
               }
      },
      {data: 'status'},
       ]
      columnDefs: [{
        defaultContent: '-',
        targets: '_all',
        data: null,
        orderable: false,
      }]
  });

});

Here is my code for model

I've been reading this code over and over and modifying it just to see where the bug is. But still can't debug. <?php defined('BASEPATH') OR exit('No direct script access allowed');

class Home_model extends CI_Model {

    public function get_application_request(){
        $search  = $this->input->post('search', true);
        $start   = $this->input->post('start', true);
        $offset  = $this->input->post('length', true);

        $this->db->select('*')
                 ->where('active', '1')
                 ->from('application_request');

    if($search['value'] != ''){
            $this->db->group_start()
                     ->like('EMP_NO', $search['value'])
                     ->or_like('record_type', $search['value'])
                     ->or_like('date_of_application', $search['value'])
                     ->or_like('name', $search['value'])
                     ->or_like('status', $search['value'])
                     ->or_like('office_division', $search['value'])
                     ->or_like('position', $search['value'])
                     ->or_like('purpose', $search['value'])
                     ->group_end();
        };

        $query = $this->db->order_by('date_of_application', 'DESC')
                          ->limit($start, $offset)
                          ->get();

        $total_records = $this->count_rows($search);
        
        $results = array(
            'draw' => $this->input->post('draw', true),
            'recordsTotal'    => $total_records,
            'recordsFiltered' => $total_records,
            'data'            => $query->result_array()
        );

        return $results;                      
    }

    public function count_rows($search){
        $this->db->select('*')
                 ->where('active', '1')
                 ->from('application_request');

    
        if($search['value'] != ''){
            $this->db->group_start()
                      ->like('EMP_NO', $search['value'])
                     ->or_like('record_type', $search['value'])
                     ->or_like('date_of_application', $search['value'])
                     ->or_like('name', $search['value'])
                     ->or_like('status', $search['value'])
                     ->or_like('office_division', $search['value'])
                     ->or_like('position', $search['value'])
                     ->or_like('purpose', $search['value'])
                     ->group_end();
        };

        $query = $this->db->get();

        return $query->num_rows();
    }

}

Here is my code for main.php

here i combined my assets, header, footer, sidebar, navbar and middle which is the body and just call it on my controller.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
    <meta content='width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0, shrink-to-fit=no' name='viewport' />
    <title><?php echo $title; ?></title>
    <?php
        if($assets) echo $assets ;
    ?>
    <script type="text/javascript">
        var base_url    = '<?php echo base_url(); ?>';
        var module      = '<?php echo $this->uri->segment(1); ?>';

        $.fn.serializeObject = function(){
            var o = {};
            var a = this.serializeArray();
            $.each(a, function() {
                if (o[this.name] !== undefined) {
                    if (!o[this.name].push) {
                        o[this.name] = [o[this.name]];
                    }
                    o[this.name].push(this.value || '');
                } else {
                    o[this.name] = this.value || '';
                }
            });
            return o;
        };
    </script>
</head>
<body class="profile-page sidebar-collapse">
               
               
            <?php if($header) echo $header; ?>
            <div id="layoutSidenav">
                <div id="layoutSidenav_nav">
                     <?php if($sidebar) echo $sidebar; ?>
                </div>
                <div id="layoutSidenav_content">
                    <?php if($middle) echo $middle; ?>
                    <?php if($footer) echo $footer; ?>
                </div>
            </div>

</body>
</html>

Result for console.log(data)

Edit:Screen shot of the result

Here is my code for controller

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Home extends MY_Controller {

    public function __construct(){  
        parent::__construct();
        $this->load->model('Home_model');
        if(!$this->session->userdata('$2a$09$_logged_in')){
            redirect('Login');
        }
    }

    public function index(){
        // $this->data['ref_pdp_chapter_supported'] = $this->Home_model->get_refpdpcharter();
        $this->load->model('Home_model');
        $this->data['title'] = 'Service Records | Home';
        $this->middle        = 'Home_view';
        $this->layout();
    }

    public function Home_js(){
        $this->output->set_content_type('text/javascript');
        $this->load->view('Home.js');
    }

    public function get_application_request(){
        $result = $this->Home_model->get_application_request();
        echo json_encode($result);
    }
    

    

}

Edit: I think there's a problem with draw

The draw function gives null even though theres a data. I don't know the next step from here. enter image description here

datatables codeigniter php javascript
2 Answers

Please try and you can change in your requirements.

<script type="text/javascript">
$(document).ready(function() {
 var testsTable = $('#datatable').DataTable({
      processing: true,
      serverSide: true,
      autoFill: true,
      ajax: base_url + 'home/get_application_request',
    columns: [
            { data: 'SID' },
            { data: 'data-of_application' },
            { data: 'EMP_NO' },
            { data: 'record_type' },
            { data: 'name' }
        ]
  });

});            
 </script>
user_25959400

So finally I have fixed the bug on my DataTable's search box and paging

below are the final code for Model, View, Controller and JS

Here is the correct code for Model

I just switched the lengt and start replaced post method to get

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Home_model extends CI_Model {

    public function get_application_request(){
        $search  = $this->input->get('search', true);
        $start   = $this->input->get('start', true);
        $length  = $this->input->get('length', true);

        $this->db->select('*')
                 ->where('active', '1')
                 ->from('application_request');

    if($search['value'] != ''){
            $this->db->group_start()
                     ->like('EMP_NO', $search['value'])
                     ->or_like('record_type', $search['value'])
                     ->or_like('date_of_application', $search['value'])
                     ->or_like('name', $search['value'])
                     ->or_like('status', $search['value'])
                     ->or_like('office_division', $search['value'])
                     ->or_like('position', $search['value'])
                     ->or_like('purpose', $search['value'])
                     ->group_end();
        };

        $query = $this->db->order_by('date_of_application', 'DESC')
                          ->limit($length, $start)
                          ->get();

        $total_records = $this->count_rows($search);
        
        $results = array(
            'draw'            => intval($this->input->get('draw', true)),
            'recordsTotal'    => intval($total_records),
            'recordsFiltered' => intval($total_records),
            'data'            => $query->result_array()
        );

        return $results;                      
    }

    public function count_rows($search){
        $this->db->select('*')
                 ->where('active', '1')
                 ->from('application_request');

    
        if($search['value'] != ''){
            $this->db->group_start()
                      ->like('EMP_NO', $search['value'])
                     ->or_like('record_type', $search['value'])
                     ->or_like('date_of_application', $search['value'])
                     ->or_like('name', $search['value'])
                     ->or_like('status', $search['value'])
                     ->or_like('office_division', $search['value'])
                     ->or_like('position', $search['value'])
                     ->or_like('purpose', $search['value'])
                     ->group_end();
        };

        $query = $this->db->get();

        return $query->num_rows();
    }



    

}

Here is the correct code for view

There's nothing changed or modified here

<main>
                    <div class="container-fluid">
                        <h1 class="mt-4">LIST OF APPLICATIONS FOR SERVICE RECORD</h1>
                       <!--  <ol class="breadcrumb mb-4">
                            <li class="breadcrumb-item"><a href="index.html">Dashboard</a></li>
                            <li class="breadcrumb-item active">Tables</li>
                        </ol> -->
                       <!--  <div class="card mb-4">
                            <div class="card-body">
                                DataTables is a third party plugin that is used to generate the demo table below. For more information about DataTables, please visit the
                                <a target="_blank" href="https://datatables.net/">official DataTables documentation</a>
                                .
                            </div>
                        </div> -->
                        <div class="card mb-4">
                          <!--   <div class="card-header">
                                <i class="fas fa-table mr-1"></i>
                                DataTable Example
                            </div> -->
                            <!-- Here is my code for HTML view -->
                            <div class="card-body">
                                <div class="table-responsive">
                                     <table id="datatable" class="table table-bordered table-striped text-gray-900">
                                        <thead>
                                            <tr>
                                                 <th class="text-center">Name</th>
                                                <th class="text-center">Date of Application</th>
                                                <th class="text-center">Purpose</th>
                                                <th class="text-center">Action</th>
                                                <th class="text-center">Status</th>
                                            </tr>
                                        </thead>
                                        <tbody></tbody>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </main>

                <script type="text/javascript" src="<?php echo base_url(); ?>Home/Home_js"></script>

Here is my correct code for the controller

Actually there's nothing changed here. I just posted this for other's reference

<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Home extends MY_Controller {

    public function __construct(){  
        parent::__construct();
        $this->load->model('Home_model');
        if(!$this->session->userdata('$2a$09$_logged_in')){
            redirect('Login');
        }
    }

    public function index(){
        // $this->data['ref_pdp_chapter_supported'] = $this->Home_model->get_refpdpcharter();
        $this->load->model('Home_model');
        $this->data['title'] = 'Service Records | Home';
        $this->middle        = 'Home_view';
        $this->layout();
    }

    public function Home_js(){
        $this->output->set_content_type('text/javascript');
        $this->load->view('Home.js');
    }

    public function get_application_request(){
        $result = $this->Home_model->get_application_request();
        echo json_encode($result);
    }
    

    

}

Here is my correct code for JS script

I changed type to get dataType to Json

$(function() {
  'use strict';

  var datatable = $('#datatable');

    function get_request(){

      var json = JSON.parse(data);
      var draw = json['draw'];

      return draw;

    }

  $('#datatable').DataTable({
      dom: 'lfrtipr',
      // ajax: base_url + 'Home/get_application_request',
      ajax: base_url + 'Home/get_application_request',
      dataType: "json",
      type: 'get',
      processing: true,
      order:[],
      serverSide: true,
      responsive: true,
      paging: true,
      columns: [

      {data: 'name'},
      {data: 'date_of_application'},
      {data: 'purpose'},
      {render: function(data, type, row){
                  var action = '<a href="'+base_url+'Application_request/Application_request_profile/'+row.EMP_NO+'" class="sd_link">Action</a>';
                  return action;
               }
      },
      {data: 'status'},
       ],
      columnDefs: [{
        defaultContent: '-',
        targets: '_all',
        data: null,
        orderable: false,
      }]
  });
});

Yes! it's finally working. Thanks guys

user_112243770
Related