Codeigniter - How to fetch datatable data from aja

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

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.

Answer 2

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.

Answer 3

You can check this article. Although I have written it using raw php, you can implement it quite easily by creating a separate library using that ssp class & can create this type of links.