How to implement Server Side DataTable in CI4

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)>0 ){
            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)>0 ){
            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.

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".

Dirk B.
ESD Digital
No SEO spam - see forum guidelines

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');