• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to implement Server Side DataTable in CI4

#1
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,
Reply

#2
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.
Reply

#3
(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
Reply

#4
@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.
Reply

#5
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".
Dirk B.
Abatrans Software
No SEO spam - see forum guidelines
Reply

#6
(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');
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.