I am using codeigniter and I use datatable pagiantion in my code for pagination but search box is not working
View:
<table id="loc_vh_frt_table" class="table table-bordered table-striped table-sm" >
<thead>
<tr>
<th>SR No</th>
<th>Local Vehicle Freight No</th>
<th>Date</th>
<th> Update/Disable </th>
</tr>
</thead>
</table>
<script>
$(document).ready(function(){
var table = $('#loc_vh_frt_table').DataTable({
"processing": true,
"serverSide": true,
scrollY : "500px",
scrollX : true,
scrollCollapse: true,
"order": [],
"ajax": {
"url": "<?php echo base_url('booking/local_vehicle_freight/CLocalVehicleFreight/getLists/'); ?>",
"type": "POST"
},
"columnDefs": [{
"targets": [0],
"orderable": false
}],
"columnDefs": [ {
"targets": [9],
"data": null,
"defaultContent": "<button class="btn btn-success btn1 btn-sm" >UPDATE</button> <button class="btn btn-success btn2 btn-sm" >DISABLE</button>"
}]
});
$('#loc_vh_frt_table tbody').on( 'click', 'button.btn1', function () {
var data = table.row( $(this).parents('tr') ).data();
$.redirect("<?php echo base_url(); ?>booking/local_vehicle_freight/CLocalVehicleFreight/updateFreightMemo?loc_truck_id="+data[1], "POST");
});
$('#loc_vh_frt_table tbody').on( 'click', 'button.btn2', function () {
var data = table.row( $(this).parents('tr') ).data();
disable_freight(data[1]);
});
});
</script>
Controller:
function getLists(){
$data = $row = array();
$memData = $this->localFreightModel->getRows($_POST);
$i = $_POST['start'];
foreach($memData as $loc_vh){
$i++;
$data[] = array( $i,
$loc_vh->loc_truck_ids,
$loc_vh->local_truck_date,
null);
}
$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->localFreightModel->countAll(),
"recordsFiltered" => $this->localFreightModel->countFiltered($_POST),
"data" => $data,
);
echo json_encode($output);
}
Model:
function __construct() {
$this->table = 'local_truck_freight l';
$this->column_order = array(null, 'l.loc_truck_ids','l.local_truck_date','`bn.branch_name as from_branch`','`bn1.branch_name as to`','`vn.vehicle_no as vehicle`','l.loc_truck_add','loc_truck_remark','l.loc_pay_type');
$this->column_search = array('l.loc_truck_ids','l.local_truck_date','`bn.branch_name as from_branch`','`bn1.branch_name as to`','`vn.vehicle_no as vehicle`','l.loc_truck_add','loc_truck_remark','l.loc_pay_type');
$this->order = array('l.loc_truck_ids' => 'asc');
}
public function getRows($postData){
$this->db->select('l.loc_truck_ids,l.local_truck_date,`bn.branch_name as from_branch`,`bn1.branch_name as to`,`vn.vehicle_no as vehicle`,l.loc_truck_add,loc_truck_remark,l.loc_pay_type');
$this->_get_datatables_query($postData);
if($postData['length'] != -1){
$this->db->limit($postData['length'], $postData['start']);
$this->db->join('vehicle vn', 'l.loc_truck_lorryno=vn.vehicle_id','left');
$this->db->join('branch bn', 'l.local_truck_from=bn.branch_id','left');
$this->db->join('branch bn1','l.loc_truck_to=bn1.branch_id','left');
$this->db->where('l.local_truck_from',$this->session->userdata('user_branch'));
$this->db->where('l.status','active');
}
$query = $this->db->get();
//return $query->result();
return $query->result();
}
public function countAll(){
$this->db->from($this->table);
return $this->db->count_all_results();
}
public function countFiltered($postData){
$this->_get_datatables_query($postData);
$query = $this->db->get();
return $query->num_rows();
}
private function _get_datatables_query($postData){
$this->db->from($this->table);
$i = 0;
foreach($this->column_search as $item){
if($postData['search']['value']){
if($i===0){
$this->db->group_start();
$this->db->like($item, $postData['search']['value']);
}else{
$this->db->or_like($item, $postData['search']['value']);
}
if(count($this->column_search) - 1 == $i){
$this->db->group_end();
}
}
$i++;
}
if(isset($postData['order'])){
$this->db->order_by($this->column_order[$postData['order']['0']['column']], $postData['order']['0']['dir']);
}else if(isset($this->order)){
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
The error of this code is :
Unknown column 'bn.branch_name as from_branch' in 'where clause' SELECT `l`.`loc_truck_ids`, `l`.`local_truck_date`, `bn`.`branch_name` as `from_branch`, `bn1`.`branch_name` as `to`, `vn`.`vehicle_no` as `vehicle`, `l`.`loc_truck_add`, `loc_truck_remark`, `l`.`loc_pay_type` FROM `local_truck_freight` `l` LEFT JOIN `vehicle` `vn` ON `l`.`loc_truck_lorryno`=`vn`.`vehicle_id` LEFT JOIN `branch` `bn` ON `l`.`local_truck_from`=`bn`.`branch_id` LEFT JOIN `branch` `bn1` ON `l`.`loc_truck_to`=`bn1`.`branch_id` WHERE ( l.loc_truck_ids LIKE '%p%' ESCAPE '!' OR l.local_truck_date LIKE '%p%' ESCAPE '!' OR `bn.branch_name as from_branch` LIKE '%p%' ESCAPE '!' OR `bn1.branch_name as to` LIKE '%p%' ESCAPE '!' OR `vn.vehicle_no as vehicle` LIKE '%p%' ESCAPE '!' OR l.loc_truck_add LIKE '%p%' ESCAPE '!' OR loc_truck_remark LIKE '%p%' ESCAPE '!' OR l.loc_pay_type LIKE '%p%' ESCAPE '!' ) AND `l`.`local_truck_from` = '1' AND `l`.`status` = 'active' ORDER BY `l`.`loc_truck_ids` ASC LIMIT 10 Filename: models/booking/local_vehicle_freight/MLocalVehicleFreight.php Line Number: 37
In above code I uploaded my model view and controller. I can’t get exactly where I am wrong in my code. It perform fetch data sort data and pagination but it give me error on my search box.
Advertisement
Answer
//js
var dtable = $('#loc_vh_frt_table').DataTable({
processing: true,
serverSide: false,
searching: true,
dom: 'lBfrtip',
lengthMenu: [[5, 10, 25, 50, 100, -1], [5, 10, 25, 50, 100, "All"]],
pageLength: 10,
buttons: [
'csv', 'print'
],
ajax: {
"url": base_url + "/controller_name/datatables",
'method': 'POST',
'data': function (d) {
d._token = $("input[name=_token]").val();
}
}
//controller
function index() {
$data = array();
$data['view'] = 'view_page_name';
$this->load->view('header', $data);
}
function datatables() {
$data = array();
$stock =$this->Stock_model->getRows($this->input->post());
parent::json_output(["draw" => intval($this->input->post('draw')), "recordsTotal" => $stock[1], "recordsFiltered" =>$stock[1], "data" => $stock[0]]);
return;
}
//model
function getRows($requestArray) {
if (isset($requestArray['keyword']) && $requestArray['keyword'] != '') {
$this->db->where .= " AND (field_name_by_which_you_want_search LIKE '%" . $requestArray['keyword'] . "%')";
$this->db->where .= " AND (field_name_by_which_you_want_search LIKE '%" . $requestArray['keyword'] . "%')";
}
$this->db->where($where);
if ($this->input->post('length') != '-1') {
$this->db->limit($this->input->post('length'), $this->input->post('start'));
}
$this->db->order_by('id','desc');
$query = $result->get('table_name')->result_array();
$count = $result->get('table_name')->num_rows();
return [$query, $count];
}