记录在40秒后被丢弃;与MySQL服务器的连接丢失";在查询期间

Question

我不知道这是否是使用代码的正确方法。我从数据库中获取记录，并在刷新或重新加载页面时将其显示在页面上。到目前为止，我桌上只有500张唱片。

但当我重新加载或刷新页面时，在屏幕上显示记录几乎需要40秒(有时需要1分钟(。

有时我也会得到错误

在查询期间丢失了与MySQL服务器的连接

以上问题请参阅此链接错误代码：2013。查询过程中丢失了与MySQL服务器的连接。我检查了已接受的答案，但这对我不起作用。

我正在使用以下代码

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>

脚本

注意：我在index.php页面上有以下脚本

$('#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 }
]
});

控制器

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;
}

型号

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

}

Answer 1

您可以使用服务器端数据表，您可以尝试https://datatables.net/