codeigniter 记录在40秒后显示,有时在查询过程中出现“与MySQL服务器的连接丢失”

6vl6ewon  于 2022-12-07  发布在  Mysql
关注(0)|答案(1)|浏览(133)

我不知道这是不是正确的代码使用方式。我从数据库中提取记录,并在刷新或重新加载页面时将它们显示在页面上。到目前为止,我的表中只有500条记录。
但当我重新加载或刷新页面,然后它需要近40秒(有时它的谈话1分钟)显示在屏幕上的记录.
有时我也会收到错误
查询期间与MySQL服务器的连接丢失
我指的是上面的问题Error Code: 2013. Lost connection to MySQL server during query这个链接。我检查接受的答案,但这是不为我工作。
我正在使用以下代码

索引.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();
       
  }

相关问题