Codeigniter - How to fetch datatable data from ajax?

Question

I'm working an application based on CodeIgniter. Here the code:

Controller:

public function index(){
    $data = array(
            'record' => $this->Parameter_model->get_parameter('tbl_parameter')
        );
    $this->site->view('parameter', $data);
}

Model:

public function get_parameter($table){
    $query = $this->db->query("select * from $table order by 'parameter_ID' ASC");
    if($query->num_rows() > 0){
        foreach($query->result_array() as $row){
            $data[] = $row;
        }

        $query->free_result();
    }

    else{
        $data = NULL;
    }

    return $data;
}

View:

<table id="parameter" class="listdata table table-bordered table-striped table-hover">
  <thead>
    <tr>
      <th class="text-nowrap">Parameter</th>
      <th class="text-nowrap">Method</th>
      <th class="text-nowrap">Type</th>
      <th class="text-nowrap">Action</th>
    </tr>
  </thead>
  <tbody>
    <?php if(!empty($record)):?>
      <?php foreach($record as $row):?>
        <tr align="center">
          <td class="text-nowrap"><a href="<?=set_url('parameter/parameter_view/'.$row['parameter_ID']);?>"><strong><?php echo $row['parameter_name'];?></strong></a></td>
          <td class="text-nowrap"><?php echo $row['parameter_method'];?></td>
          <td class="text-nowrap">
            <?php 
              if($row['parameter_type'] == "1"){
                echo "General";
              }
              else{
                echo "COA Only";
              }
            ?>
          </td>
          <td class="text-nowrap">
            <div>
              <a href="<?=set_url('parameter#edit?parameter_ID='.$row['parameter_ID']);?>" class="btn btn-warning btn-xs">Edit</a>
              <a href="<?=set_url('parameter/parameter_view/'.$row['parameter_ID']);?>" class="btn btn-success btn-xs">Lihat</a>
              <a href="<?=set_url('parameter#hapus?parameter_ID='.$row['parameter_ID']);?>" class="btn btn-danger btn-xs">Hapus</a>
            </div>
          </td>
        </tr>
      <?php endforeach;?>
    <?php endif;?>
  </tbody>
  <tfoot>
    <tr>
      <th class="text-nowrap">Parameter</th>
      <th class="text-nowrap">Method</th>
      <th class="text-nowrap">Type</th>
      <th class="text-nowrap">Action</th>
    </tr>
  </tfoot>
</table>

Javascript:

// parameter
// Setup - add a text input to each footer cell
$('#parameter tfoot th').each( function () {
    var title = $(this).text();
    $(this).html( '<input type="text" style="width:100%;" title="Search '+title+'" placeholder="Search '+title+'" />' );
} );

// DataTable
var table = $('#parameter').DataTable({
    paging: true,
    searching: true,
    ordering: true,
    "order": [[ 0, "asc" ]],
    scrollX: true,
    scroller: true,
});

// Apply the search
table.columns().every( function () {
    var that = this;

    $( 'input', this.footer() ).on( 'keyup change', function () {
        if ( that.search() !== this.value ) {
            that
                .search( this.value )
                .draw();
        }
    } );
} );

Above code work well.

Now, I want to fetch data into the table id="parameter" via ajax request. I've create an ajax request from url, lets say from here http://'+host+path+'/action/ambil, where var path = window.location.pathname; and var host = window.location.hostname;.

The ajax response produce:

{"record":[{"parameter_ID":"1","parameter_name":"pH","parameter_method":"(pH meter)","parameter_type":"1",{"parameter_ID":"2","parameter_name":"Viscosity","parameter_method":"(Brookfield-Viscometer)","parameter_type":"1"}]}

Question
How to configure datatable with Ajax data source, and how to display the data into the table, so I can use the data for example to create a link like code
<a href="<?=set_url('parameter/parameter_view/'.$row['parameter_ID']);?>">

Answer 1

In Controller

public function get_user_data(){
    
    $columns = ['id','name','email','status','action'];
    $limit = $this->input->post('length');
    $start = $this->input->post('start');
    $order = $columns[$this->input->post('order')[0]['column']];
    $dir = $this->input->post('order')[0]['dir'];
    $totaldata = $this->User_model->count_all_user();
    $totalFiltered = $totaldata;

    if (empty($this->input->post('search')['value'])) {
        $users_data = $this->User_model->get_user_data($limit,$start,$order,$dir);

    }else{
        $search = $this->input->post('search')['value'];
        $users_data = $this->User_model->search_user_data($limit,$start,$order,$dir,$search);
        $totalFiltered = $this->User_model->count_search_user_data($search);
    }

    $data = [];
    if (!empty($users_data))
    {
        foreach($users_data as $user)
        {
            $nestedData['id'] = $user->id;
            $nestedData['name'] = $user->name;
            $nestedData['email'] = $user->email;
            $nestedData['status'] = $user->status;
            $nestedData['action'] = '<button Onclick="get_user_data('.$user->id.')" class="btn btn-sm btn-primary">view</button>';
            $data[] = $nestedData;
        }
    }

    $json_data = array(
        "draw" => intval($this->input->post('draw')),
        "recordsTotal" => intval($totaldata),
        "recordsFiltered" => intval($totalFiltered),
        "data" => $data
    );
    echo json_encode($json_data);
}

In Model

public function count_all_user()
{
    $this->db->select('*');
    $this->db->from('users');
    return $this->db->count_all_results();
}

 public function get_user_data($limit,$start,$order,$dir)
 {
    $this->db->select('*');
    $this->db->from('users');
    $this->db->limit($limit, $start);
    $this->db->order_by($order, $dir);
    $query = $this->db->get();
    if ($query->num_rows() > 0) 
    {
        return $query->result();
    }else{
        return null;
    }
}

public function search_user_data($limit,$start,$order,$dir,$search) 
{
    $this->db->select('*');
    $this->db->from('users');
    $this->db->group_start();
    $this->db->or_like('email', $search);
    $this->db->or_like('name', $search);
    $this->db->or_like('id', $search);
    $this->db->group_end();
    $this->db->limit($limit,$start);
    $this->db->order_by($order,$dir);
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        return $query->result();
    }else
    {
        return null;
    }
}

public function count_search_user_data($search)
{
    $this->db->like('id', $search);
    $query = $this->db->get('users');
    return $query->num_rows();
}

Answer 2

Model Code

  public function get_cities(){

  $this->db->order_by('city_id','desc');
  $this->db->limit('1000');
  $this->db->select('*,cities.name as city_name');
  $this->db->from('cities');
  $this->db->join('states','states.id=cities.state_id','left');
  $this->db->join('countries','countries.id=states.country_id','left');
  $data=$this->db->get();
  return $data->result_array();
  }
   private function get_datatables_query_city(){
    $column_search = array('cities.name','states.name','countries.country_name');
    $order = array('city_id' => 'desc');
    $column_order = array(null, 'city_name', 'sname', 'country_name', null);
    $this->db->select('cities.city_id,cities.name as city_name,states.name as sname,states.id as state_id,countries.id as country_id,countries.country_name');
     $this->db->from('cities');
     $this->db->join('states','states.id=cities.state_id','left');
     $this->db->join('countries','countries.id=states.country_id','left');
     $i = 0;
     foreach ($column_search as $item)
     {
        if($_POST['search']['value']) // if datatable send POST for search
            {
                if($i===0)
                {
                    $this->db->group_start();
                    $this->db->like($item, $_POST['search']['value']);
                }
                else
                {
                    $this->db->or_like($item, $_POST['search']['value']);
                }
                if(count($column_search) - 1 == $i)
                $this->db->group_end();
        }
        $i++;
    }
    if(isset($_POST['order']))
    {
        $this->db->order_by($column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    }
    else if(isset($order))
    {
        $order = $order;
        $this->db->order_by(key($order), $order[key($order)]);
    }
}
function get_datatables_city()
{
    $this->get_datatables_query_city();
    if($_POST['length'] != -1)
        $this->db->limit($_POST['length'], $_POST['start']);
    $query = $this->db->get();
    return $query->result();
}
function count_filtered_city()
{
    $this->get_datatables_query_city();
    $query = $this->db->get();
    return $query->num_rows();
}
public function count_all_city()
{
    $this->db->from('cities');
    return $this->db->count_all_results();
} 

Controller Code

 public function city_list(){
        $this->load->view('admin/header');
        $this->load->view('admin/city');
        $this->load->view('admin/footer');
    }
    public function city_ajax(){
        $list = $this->Admin_model->get_datatables_city();
        $data = array();
        $no = $_POST['start'];
        foreach ($list as $city){
            $no++;
            $row = array();
            $row[] = $no;
            $row[] = $city->city_name;
            $row[] = $city->sname;
            $row[] = $city->country_name;
            $row[] = '<div class="d-flex align-items-center card-action-wrap">
            <div class="inline-block dropdown">
            <a class="dropdown-toggle no-caret" data-toggle="dropdown" href="#" aria-expanded="false" role="button"><i class="ion ion-ios-more"></i></a>
            <div class="dropdown-menu dropdown-menu-right" x-placement="top-end" style="position: absolute; transform: translate3d(-214px, -161px, 0px); top: 0px; left: 0px; will-change: transform;">
            <a class="dropdown-item" href="'.base_url().'Adminhome/city_edit/'.$city->city_id.'" ><i class="fas fa-edit read-icon"></i> Edit</a>
            <a id="mybutton" href="javascript:void(0);" onclick="citydelete('.$city->city_id.')" class="dropdown-item text-danger remove"  data-toggle="modal" data-target="#delete" data-id="'.$city->city_id.'"><i class="fas fa-trash-alt read-icon text-danger"></i> Delete</a>
            </div>
            </div>
            </div>';
            $data[] = $row;
        }
        $output = array(
            "draw" => $_POST['draw'],
            "recordsTotal" => $this->Admin_model->count_all_city(),
            "recordsFiltered" => $this->Admin_model->count_filtered_city(),
            "data" => $data,
        );
        echo json_encode($output);
    }

view code

            <div class="section-body">
                <div class="row">
                    <div class="col-12">
                        <div class="card">
                            <div class="card-body">
                                <div class="table-responsive">
                                    <table class="table table-striped" id="example">
                                        <thead>
                                            <tr>
                                                <th>Sr.Number</th>
                                                <th>City Name</th>
                                                <th>State Name</th>
                                                <th>Country</th>
                                                <th>Manage</th>
                                            </tr>
                                        </thead>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </div>
    <div class="modal fade" id="delete" tabindex="-1" role="dialog" aria-labelledby="exampleModalCenterTitle" aria-hidden="true">
        <div class="modal-dialog modal-dialog-centered" role="document">
            <div class="modal-content">
                <div class="modal-body">
                    Are you sure want to delete.
                </div>
                <div class="modal-footer bg-whitesmoke br">
                    <a id="confirm-button"> <button type="button" class="btn btn-danger">Delete</button></a>
                    <button type="button" class="btn btn-secondary" data-dismiss="modal">Cancel</button>
                </div>
            </div>
        </div>
    </div>
    <div class="alert alert-primary show2" role="alert" id="snackbar2" style="visibility: hidden">
        Successfully Deleted
    </div>
    <script>
        function citydelete(id){
            $('#delete').modal('show');
            rowToDelete = $(this).closest('tr');
            $('#confirm-button').click(function(){
                 $.ajax({
                    url: '<?php echo base_url();?>/Adminhome/city_delete',
                    type: 'GET',
                    data: {id: id},
                    success: function (data){
                        $("#"+id).remove();
                        var table = $('#example').DataTable();
                        table.ajax.reload( null, false );
                        $("#delete").modal('hide');
                        document.getElementById("snackbar2").style.visibility = "visible";
                        setTimeout(function() {
                            document.getElementById('snackbar2').style.visibility = 'hidden';
                        }, 3000);
                    }
                });
                })
        }
    </script>
    <script type="text/javascript">
        var table;
        $(document).ready(function() {
            table = $('#example').DataTable({ 
                "processing": true,
                "serverSide": true,
                "stateSave": true,
                "order": [],
                "ajax": {
                    "url": "<?php echo site_url('Adminhome/city_ajax')?>",
                    "type": "POST"
                },
                "columnDefs": [
                { 
                    "targets": [ 0 ],
                    "orderable": false,
                },
                ],
            });
        });
    </script>   

Answer 3

Updated with More explanation and more code

You can try my code this is working fine for me.

Controller Code

public function showFeeCode()
{
    $data = $this->fmodel->fetchAllData('*','fee_assign',array());
    if (is_array($data) || is_object($data))
    {
        foreach ($data as $key => $value) {
            $button = "";
            $button .= "<button class='btn btn-success fa fa-pencil' onclick='editFunction(".$value['id'].")' data-toggle='tooltip' title='Edit Details'></button> ";
            $result['data'][$key] = array(
                    $value['feegroup'],
                    $value['name'],
                    $button
                     );
        }
    }
    echo json_encode($result);
}

Modal Code

public function fetchAllData($data,$tablename,$where)
    {
        $query = $this -> db 
                        ->where($where)
                        -> get($tablename);
                            
    if($query->num_rows() > 0){
            return $query->result_array();
        }
        else{
            return array('error');
        }
    }

view code (table)

<table id="myTable" class="table display">
                      <thead class="alert alert-danger">
                          <tr>
                              <th>Fee Type</th>
                              <th>Fee Code</th>
                              <th>Action</th>
                          </tr>
                      </thead>
                  </table>

ajax code to fetch the data the ajax code recides in the view page.

$(document).ready(function() {
  $('#myTable').dataTable( {
        "ajax":"<?= base_url('Fee/showFeeCode'); ?>",
        'order':[],
    });
  });

if you want to pass some parameter to the controller then you can pass it by ajax

 $(document).ready(function() {
var id = 4;
  $('#myTable').dataTable( {
        "ajax":"<?= base_url('Fee/showFeeCode'); ?>/"+id,
        'order':[],
    });
  });

You can receive this id with the help of passing parameter to the controller function.

if you want to send multiple data then you can use this format

var first_data = 1;
var second_data = 2;
$('#myTable').dataTable( {
            "ajax": {
            "url": '<?php echo base_url('fetchData') ?>',
            "type": "POST",
            "data": {first_data:first_data,second_data:second_data}
             },
            'order':[],
        });
      });

Answer 4

You can do dataTable by server side script as follow.

1. Change your controller so that It will handle the server side call from datatable and you can create dynamic links in controller only. I have added comment in controller for more details.
2. Change your script to call it with ajax.
3. Don't load any thing in view tbody while loading page.
4. Note : I have skipped the model part I used direct query. Hope you can change it.

Contorller

public function index() {
        $data = array();
        if ($this->input->is_ajax_request()) {
            /** this will handle datatable js ajax call * */
            /** get all datatable parameters * */
            $search = $this->input->get('search');/** search value for datatable  * */
            $offset = $this->input->get('start');/** offset value * */
            $limit = $this->input->get('length');/** limits for datatable (show entries) * */
            $order = $this->input->get('order');/** order by (column sorted ) * */
            $column = array('parameter', 'method', 'type');/**  set your data base column name here for sorting* */
            $orderColumn = isset($order[0]['column']) ? $column[$order[0]['column']] : 'parameter';
            $orderDirection = isset($order[0]['dir']) ? $order[0]['dir'] : 'asc';
            $ordrBy = $orderColumn . " " . $orderDirection;

            if (isset($search['value']) && !empty($search['value'])) {
                /** creat sql or call Model * */
                /**   $this->load->model('Parameter_model');
                  $this->Parameter_model->get_parameter('tbl_parameter'); * */
                /** I am calling sql directly in controller for your answer 
                 * Please change your sql according to your table name
                 * */
                $sql = "SELECT * FROM TABLE_NAME WHERE column_name '%like%'" . $search['value'] . " order by " . $ordrBy . " limit $offset,$limit";
                $sql = "SELECT count(*) FROM TABLE_NAME WHERE column_name '%like%'" . $search['value'] . " order by " . $ordrBy;
                $result = $this->db->query($sql);
                $result2 = $this->db->query($sql2);
                $count = $result2->num_rows();
            } else {
                /**
                 * If no seach value avaible in datatable
                 */
                $sql = "SELECT * FROM TABLE_NAME  order by " . $ordrBy . " limit $offset,$limit";
                $sql2 = "SELECT * FROM TABLE_NAME  order by " . $ordrBy;
                $result = $this->db->query($sql);
                $result2 = $this->db->query($sql2);
                $count = $result2->num_rows();
            }
            /** create data to display in dataTable as you want **/    

            $data = array();
            if (!empty($result->result())) {
                foreach ($result->result() as $k => $v) {
                    $data[] = array(
                        /** you can add what ever anchor link or dynamic data here **/
                         'parameter' =>  "<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong></a>",
                          'method' =>  "<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong></a>",
                          'parameter_type' =>  "<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong></a>",
                          'actions' =>  "<a href=".set_url('parameter/parameter_view/'.$v['parameter_ID'])."><strong>".$v['parameter_name']."</strong></a>" 

                    );
                }
            }
            /**
             * draw,recordTotal,recordsFiltered is required for pagination and info.
             */
            $results = array(
                "draw" => $this->input->get('draw'),
                "recordsTotal" => count($data),
                "recordsFiltered" => $count,
                "data" => $data 
            );

            echo json_encode($results);
        } else {
            /** this will load by default with no data for datatable
             *  we will load data in table through datatable ajax call
             */
            $this->site->view('parameter', $data);
        }
    }

View

   <table id="parameter" class="listdata table table-bordered table-striped table-hover">
  <thead>
    <tr>
      <th class="text-nowrap">Parameter</th>
      <th class="text-nowrap">Method</th>
      <th class="text-nowrap">Type</th>
      <th class="text-nowrap">Action</th>
    </tr>
  </thead>
  <tbody>
    /** tbody will be empty by default. **/
  </tbody>
  <tfoot>
    <tr>
      <th class="text-nowrap">Parameter</th>
      <th class="text-nowrap">Method</th>
      <th class="text-nowrap">Type</th>
      <th class="text-nowrap">Action</th>
    </tr>
  </tfoot>
</table>

Script

 <script>
        $(document).ready(function() {
            $('#example').DataTable({
                url: '<?php base_url(); ?>controllerName/index',
                processing: true,
                serverSide: true,
                paging: true,
                searching: true,
                ordering: true,
                order: [[0, "asc"]],
                scrollX: true,
                scroller: true,
                columns: [{data: "parameter"}, {data: "method"}, {data: "parameter_type"}, {data: "action"}]
                /** this will create datatable with above column data **/
            });
        });
    </script>

If you wish to use some third party library check this. For your model query you can customize it as mention in this post.