CodeIgniter Forums

Full Version: How to implement Server Side DataTable in CI4
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi i'm trying to implement Server Side Datatable in CI4 but it is not working. I'm not sure what is my mistake is

********************VIEW********************
Name : index.php

Code:
<table class="table table-striped table-bordered table-hover nowrap" id="kt_table_2">
Code:
 <thead>
Code:
     <tr>
       <th>#</th>
       <th>Product Code No</th>
       <th>Part Number</th>
       <th>Description</th>
     </tr>
</thead>
</table>

<script>
$(document).ready(function(){



$('#kt_table_2').DataTable({
"processing": true,
"serverSide": true,
"ajax":{
"url": '/product-code/showProductCodes',
"dataType": "json",
"type": "POST",
"columns": [
  { "data": "product_code_no" },
  { "data": "part_number" },
  { "data": "date_created" }
]
}
});

});

</script>




********************Routes
********************

PHP Code:
$routes->add('/product-codes(:any)''Code8ProductCode::index'); //This is the Page Itself

$routes->add('/product-code/showProductCodes(:any)''Code8ProductCode::showProductCodes'); //This is the Ajax Request 



********************Controller********************
Note : i only show the controller for Ajax request
Code:
public function showProductCodes(){

        
        $columns = array(
            0 =>'product_code_no',
            1 =>'part_number',
            2=> 'date_created',
            3=> 'product_code_no',
        );

        $limit = $this->request->getVar('length');
        $start = $this->request->getVar('start');
        $order = $columns[$this->request->getVar('order')[0]['column']];
        $dir = $this->request->getVar('order')[0]['dir'];
        
        $totalData = $this->Code8ProductCode_model->countAll();
        
        $totalFiltered = $totalData;
        
        if(empty($this->request->getVar('search')['value'])){           
            $records = $this->Code8ProductCode_model->allRecords($limit,$start,$order,$dir);
        }else{
            $search = $this->request->getVar('search')['value'];
            
            $records =  $this->Code8ProductCode_model->recordsSearch($limit,$start,$search,$order,$dir);

            $totalFiltered = $this->Code8ProductCode_model->recordsSearchCount($search);
        }   

        $data = array();
        if(!empty($records))
        {
            foreach ($records as $record)
            {
                $nestedData['product_code_no'] = $record->product_code_no;
                $nestedData['part_number'] = $record->part_number;
                //$nestedData['body'] = substr(strip_tags($record->body),0,50)."...";
                //$nestedData['created_at'] = date('j M Y h:i a',strtotime($record->date_created));
                $nestedData['description'] = $record->description;
                $nestedData['date_created'] = $record->date_created;
               
                $data[] = $nestedData;
            }
        }
        
        $json_data = array(
            "draw"            => intval($this->request->getVar('draw')), 
            "recordsTotal"    => intval($totalData), 
            "recordsFiltered" => intval($totalFiltered),
            "data"            => $data   
            );
           
        return json_encode($json_data);
        

    }//End Function




********************Model********************
Note : i only show the involve function for the DataTable

PHP Code:
function countAll(){   
        $CompanyGroupNo 
3;
        
$status 1;
    
        $sql "SELECT product_code_no FROM `tbl_pcc_product_code` WHERE `status` = :status: AND `company_group_no` = :CompanyGroupNo:";
        
$query $this->BaseModel->db->query($sql, ['CompanyGroupNo' => $CompanyGroupNo'status' => $status]);
        
$results $query->getResultArray();
        
        return 
count($results);
    }
    
    function 
allRecords($limit,$start,$col,$dir){   
        $CompanyGroupNo 
3;
        
$status 1;        
                
        
$sql "SELECT * FROM `tbl_pcc_product_code` WHERE `status` = :status: AND `company_group_no` = :CompanyGroupNo: LIMIT :limit:, :start: ORDER BY :col:, :dir:";
        
$query $this->BaseModel->db->query($sql, [
            
'CompanyGroupNo' => $CompanyGroupNo,
            
'status' => $status
            
'limit' => $limit
            
'start' => $start
            
'col' => $col
            
'dir' => $dir
        
]);
        
$results $query->getResultArray();    

        if(count($results)>){
            return $results
        }else{
            return null;
        }
        
    
}
    
    function 
recordsSearch($limit,$start,$search,$col,$dir){
        
$CompanyGroupNo 3;
        
$status 1;        
                
        
$sql "SELECT * FROM `tbl_pcc_product_code` WHERE `status` = :status: AND `company_group_no` = :CompanyGroupNo: AND part_number LIKE CONCAT('%', :search:,'%') LIMIT :limit:, :start: ORDER BY :col:, :dir:";
        
$query $this->BaseModel->db->query($sql, [
            
'CompanyGroupNo' => $CompanyGroupNo,
            
'status' => $status
            
'limit' => $limit
            
'start' => $start
            
'col' => $col
            
'dir' => $dir
            
'search' => $search
        
]);
        
$results $query->getResultArray();        
        
       
        
if(count($results)>){
            return $results
        }else{
            return null;
        }
    }
    
    function 
recordsSearchCount($search){
        $CompanyGroupNo 3;
        
$status 1;
        
        
$sql "SELECT product_code_no FROM `tbl_pcc_product_code` WHERE `status` = :status: AND `company_group_no` = :CompanyGroupNo: AND part_number LIKE CONCAT('%', :search:,'%') ";
        
$query $this->BaseModel->db->query($sql, [
            
'CompanyGroupNo' => $CompanyGroupNo,
            
'status' => $status
            
'search' => $search
        
]);    
        
        
$results $query->getResultArray();
        return count($results);
    

Thanks,
I expect your index route ('/product-codes(:any)') is matching your AJAX call. Try switching the order of the routes, or making the first route more specific.
(07-28-2020, 05:41 AM)MGatner Wrote: [ -> ]I expect your index route ('/product-codes(:any)') is matching your AJAX call. Try switching the order of the routes, or making the first route more specific.
$routes->add('/product-codes(:any)''Code8ProductCode::index'); //This is the Page Itself

$routes->add('/product-code/showProductCodes(:any)''Code8ProductCode::showProductCodes'); //This is the Ajax Request 

the 2nd one is the Ajax request call
@mr_e they match in order. Your first route is likely catching "/product-code/showProductCodes/foo" because of its "(:any)"

EDIT: Nope, my mistake! I missed that one had an "s". Without digging in I'm not sure what the issue is.
Try to remove the (:any) from the routing statement

Code:
$routes->add('/product-code/showProductCodes(:any)', 'Code8ProductCode::showProductCodes');

change to:
Code:
$routes->add('/product-code/showProductCodes', 'Code8ProductCode::showProductCodes');


Also I noticed that there is no / before the (:any), so to my understanding that url can be anything as long as it start with "showProductCodes".
(08-02-2020, 01:45 PM)abatrans Wrote: [ -> ]Try to remove the (:any) from the routing statement

Code:
$routes->add('/product-code/showProductCodes(:any)', 'Code8ProductCode::showProductCodes');

change to:
Code:
$routes->add('/product-code/showProductCodes', 'Code8ProductCode::showProductCodes');


Also I noticed that there is no / before the (:any), so to my understanding that url can be anything as long as it start with "showProductCodes".

Try wiht POST and GET un routes:

Code:
$routes->get('/product-code/showProductCodes(:any)', 'Code8ProductCode::showProductCodes');

change to:
Code:
$routes->post('/product-code/showProductCodes', 'Code8ProductCode::showProductCodes');