I don't know this is the right way to use the code or not. I am fetching the records from the database and displaying them on the page when refresh or reload the page. I have only 500 records on my table as of now.
But when I reload or refresh the page then It's taking almost 40 sec (sometimes it's talking 1 min) to display the records on the screen.
Also sometimes I am getting the error
Lost connection to MySQL server during query
I refer this link for above issue Error Code: 2013. Lost connection to MySQL server during query. I checked accepted answer but that is not working for me.
I am using below code
index.php
<table id="workInProgress" class="table table-striped table-bordered display" style="width:100%">
<thead>
<tr class="table-column-heading">
<th>Order no</th>
<th>Lead Owner</th>
<th>Company</th>
<th>Customer</th>
<th>Product</th>
<th>Bank</th>
<th>Remark</th>
<th>Status</th>
<th>Action</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
Script
Note: I have the below script on the index.php page
$('#workInProgress').DataTable( {
initComplete: function (d) {
this.api().columns([7]).every(function () {
var column = this;
var Jobs = $("#table th").eq([d]).text();
var select = $('<select class="drop-down"><option value="">ALL</option></select>')
.appendTo($(column.header()))
.on('change', function () {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
column
.search(val ? '^' + val + '$' : '', true, false)
.draw();
});
column.data().unique().sort().each(function (d, j) {
select.append('<option value="' + d + '">' + d + '</option>')
});
});
},
language: {
sLengthMenu: "Show _MENU_",// remove entries text
searchPlaceholder: "Search",
emptyTable: "No record found",
search:""
},
"autoWidth": false,
"ordering": false,// remove sorting effect from header
"processing": true,
// "serverSide": true,
"scrollX": true,
"pageLength": 25,
"paging": true,
"ajax": {
"url" : baseUrl + "/Customer_control/workInprocess",
"type" : "POST"
},
"columns": [
{ "data": "orderno" },
{ "data": "Lead_owner" },
{ "data": "companyname" },
{ "data": "customername" },
{ "data": "producttype" },
{ "data": "bankname" },
{ "data": "remark" },
{ "data": "is_leadConfirm" },
{ "data": "action" }
],
"columnDefs": [
{ width: '14%', targets: 0 },
{ width: '13%', targets: 1 },
{ width: '13%', targets: 2 },
{ width: '10%', targets: 3 },
{ width: '8%', targets: 4 },
{ width: '12%', targets:5 },
{ width: '9%', targets: 6 },
{ width: '8%', targets: 7 },
{ width: '14%', targets: 8 }
]
});
Controller
public function workInprocess(){
$order_list=$this->Customer_model->workInprocess_lead();
// Datatables Variables
$draw = intval($this->input->get("draw"));
$start = intval($this->input->get("start"));
$length = intval($this->input->get("length"));
$data['draw'] = 1;
$data['recordsTotal'] = count($order_list);
$data['recordsFiltered'] = count($order_list);
$data['data'] = [];
$i=1;
foreach ($order_list as $key => $row)
{
if ($row->f_filestatus==2) {
$leadConfirm='Submit';
}
else if ($row->f_filestatus==3) {
$leadConfirm='Pending';
}
else if ($row->f_filestatus==4) {
$leadConfirm='PD';
}
else if(($row->f_filestatus==1)|| ($row->f_filestatus==5)){
$leadConfirm='Approved';
}
else{
$leadConfirm='';
}
$action='<select name="pp_fileStatus[]" class="form-control multipleselect">
<option value="" disabled selected>File Status</option>
<option value="8" '. ($row->f_filestatus == "1"?'selected':'').' >Approved</option>
<option value="2" '. ($row->f_filestatus == "2"?'selected':'').' >Submit</option>
<option value="3" '. ($row->f_filestatus == "3"?'selected':'').' >Pendency</option>
<option value="5" '. ($row->f_filestatus == "4"?'selected':'').' >PD</option>
</select>';
$arr_result = array(
"orderno" => $row->order_no.'-'.$row->b_orderno,
"Lead_owner" => $row->empfirstname.' '.$row->emplastname,
"companyname" => $row->companyname,
"customername" => $row->c_firstname.' '.$row->c_lastname,
"producttype" => strtoupper($row->producttype),
"bankname" => $row->bankname,
"remark" => $row->f_remark,
"is_leadConfirm" => $leadConfirm,
"action" => '<ul class="lbp_actionslist">'.$action.'</ul>'
);
$data['data'][] = $arr_result;
}
//echo "<pre>";
//print_r($data);
echo json_encode($data);
exit;
}
Model
public function workInprocess_lead(){
if($this->session->userdata['login_session']['access_role']==5){
$where="f.f_filestatus NOT IN(1,8,9) AND tbl_lead.leadstatus=1 AND tbl_lead.createby='".$this->session->userdata['login_session']['id']."'";
}
else if($this->session->userdata['login_session']['access_role']==3){
$where="f.f_filestatus NOT IN(1,8,9) AND tbl_lead.leadstatus=1 AND tbl_lead.createby='".$this->session->userdata['login_session']['id']."' OR f.f_filestatus NOT IN(1,8,9) AND tbl_lead.leadstatus=1 AND tbl_bankdata.rm_name='".$this->session->userdata['login_session']['id']."'";
}
else{
$where="f.f_filestatus NOT IN(1,8,9) or f.f_filestatus IS NULL AND tbl_lead.leadstatus=1 ";
}
$result="SELECT *, `tbl_employee`.`firstname` as `empfirstname`, `tbl_employee`.`lastname` as `emplastname` FROM `tbl_lead` LEFT JOIN `tbl_bankdata` ON `tbl_lead`.`c_id`=`tbl_bankdata`.`lead_id` JOIN `tbl_bankname` ON `tbl_bankname`.`b_id`=`tbl_bankdata`.`b_bankname` left join tbl_fileStatus f
on tbl_bankdata.bank_id=f.f_bankid
and f.date_of_created = (
select max(date_of_created)
from tbl_fileStatus f1
where f1.f_bankid = f.f_bankid
) JOIN `tbl_employee` ON `tbl_lead`.`createby`=`tbl_employee`.`id` WHERE ".$where."ORDER BY tbl_lead.date_of_created DESC";
$getQuery= $this->db->query($result);
return $getQuery->result();
}
