Отображение большого объема данных из базы данных с использованием обработки на стороне сервера DataTables и AJAX

Недавно я начал программировать в Codeigniter MVC Framework, и теперь я застрял с проблемой, связанной с обработкой на стороне сервера Datatables с использованием AJAX. У меня более 7 тыс. Строк в моей таблице базы данных, и если я пытаюсь отобразить эти данные с помощью обработки на стороне клиента DataTables, это увеличивает время загрузки страницы, поэтому я пытаюсь выполнить обработку на стороне сервера. Я прогуглил это, но не нашел подходящего или требуемого решения. и сейчас у меня есть этот код, но это вызывает ошибку AJAX.

Мой контроллер

        public function fetch_products(){
$this->load->model("test_model");
$list = $this->test_model->get_datatables();
$data = array();
$no = $_POST['start'];
foreach($list as $row){
$no++;
$sub_array = array();
$sub_array[] = $row->tickid;
// $sub_array[] = $row->Updated;
$sub_array[] = null;
$sub_array[] = null;
$sub_array[] = $row->IsVerified;
$sub_array[] = $row->IsProblematic;
$sub_array[] = $row->ImageMain;
$sub_array[] = $row->ID;
$sub_array[] = $row->Category;
$sub_array[] = $row->SubCategory;
$sub_array[] = $row->Brand;
$sub_array[] = $row->Size;
$sub_array[] = $row->Description;
$sub_array[] = $row->AddedBy;
$sub_array[] = $row->DateAdded;
$sub_array[] = $row->ItemCondition;
$sub_array[] = $row->Season;
$sub_array[] = $row->BarCode;
$sub_array[] = $row->UBarCode;
$sub_array[] = $row->BoxNumber;
$sub_array[] = $row->ImageOther;
$sub_array[] = $row->Attributes;
$sub_array[] = $row->Status;
$sub_array[] = $row->PriceMSRP;
$sub_array[] = $row->PriceDiscounted;
$sub_array[] = $row->PriceVIP;
$sub_array[] = $row->PriceWS;
$sub_array[] = $row->Cost;
$sub_array[] = $row->NewBarCodeAddedBy;
$sub_array[] = $row->NewBarCodeAddedOn;
$sub_array[] = $row->data_updated_by;
$sub_array[] = $row->data_updated_date;
$sub_array[] = $row->images_updated_by;
$sub_array[] = $row->images_updated_date;

$data[] = $sub_array;
}

$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->customers->count_all(),
"recordsFiltered" => $this->customers->count_filtered(),
"data" => $data,
);

echo json_encode($output);

Моя модель:

    <?php
class Test_model extends CI_Model{
var $table = "list_items";
var $column_search = array("tickid","ImageMain","ID","Category","SubCategory","Brand","Size","Description",
"AddedBy","DateAdded","ItemCondition","Season","BarCode","UBarCode","BoxNumber","ImageOther","Attributes",
"Status","PriceMSRP","PriceDiscounted","PriceVIP","PriceWS","Cost","NewBarCodeAddedBy","data_updated_by",
"data_updated_date","images_updated_by","images_updated_date","NewBarCodeAddedOn","IsVerified","IsProblematic");
var $column_order = array(null,null,"ID","Category","SubCategory",null,null,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

private function _get_datatables_query()
{

$this->db->from($this->table);

$i = 0;

foreach ($this->column_search as $item) // loop column
{
if($_POST['search']['value']) // if datatable send POST for search
{

if($i===0) // first loop
{
$this->db->group_start(); // open bracket. query Where with
OR clause better with bracket. because maybe can combine
with other WHERE with AND.
$this->db->like($item, $_POST['search']['value']);
}
else
{
$this->db->or_like($item, $_POST['search']['value']);
}

if(count($this->column_search) - 1 == $i) //last loop
$this->db->group_end(); //close bracket
}
$i++;
}

if(isset($_POST['order'])) // here order processing
{
$this->db->order_by($this->column_order[$_POST['order']['0']
['column']], $_POST['order']['0']['dir']);
}
else if(isset($this->order))
{
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}

function get_datatables()
{
$this->_get_datatables_query();
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$query = $this->db->get();
return $query->result();
}

function count_filtered()
{
$this->_get_datatables_query();
$query = $this->db->get();
return $query->num_rows();
}

public function count_all()
{
$this->db->from($this->table);
return $this->db->count_all_results();
}

}

Мой взгляд:

    <div class="panel panel-default">
<div class="panel-heading">
Existing Products In Database
</div>
<div class="panel-body">

<!-- <div id="users_wrapper" class="dataTables_wrapper form-inline dt-bootstrap">
<div class="row"> -->
<!-- <div class="col-sm-12" id="my_table"> -->
<div class="table-responsive">
<table id="product_data" class="table table-bordered dataTable" style="width: 100%;">
<thead>
<tr role="row">
<th width="auto">Action</th>
<th width="auto">Updated</th>
<th width="auto">Verified</th>
<th width="auto">Problematic</th>
<th width="auto">Front Image</th>
<th width="auto">ID</th>
<th width="auto">Category</th>
<th width="auto">SubCategory</th>
<th width="auto">Brand</th>
<th width="auto">Size</th>
<th width="auto">Description</th>
<th width="auto">Added By</th>
<th width="auto">Uploaded On</th>
<th width="auto">Condition</th>
<th width="auto">Season</th>
<th width="auto">BarCode</th>
<th width="auto">Updated BarCode</th>
<th width="auto">BoxNumber</th>
<th width="auto">Other Images</th>
<th width="auto">Attributes</th>
<th width="auto">Status</th>
<th width="auto">PriceMSRP</th>
<th width="auto">PriceDiscounted</th>
<th width="auto">PriceVIP</th>
<th width="auto">PriceWS</th>
<th width="auto">Cost</th>
<th width="auto">BarCode Updated By</th>
<th width="auto">BarCode Updated Date</th>
<th width="auto">Data Updated By</th>
<th width="auto">Data Updated Date</th>
<th width="auto"> Images Updated By</th>
<th width="auto">Images Updated Date</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</div>
</div>
</div>
</div>

Мой Javascript:

    //Generating DataTable for Products
$(document).ready(function(){
var tblbuttonsedit = "<button class=\"btn_usr_action_edit\">Edit</button>";
var tblbuttondelete = " <button class=\"btn_usr_action_del\">Delete</button>";
var table_product= $('#product_data').DataTable({
"processing" : true ,
"serverSide" : true,
"scrollY": "500px",
"scrollX": true,
"scrollCollapse": true,
"pagingType": "input",
"order" : [],
"ajax": {"url": ajaxurl +
"Product_database/fetch_products","type":"POST"},
"columnDefs":
[
{
"targets": 0,
"data" : null,
"defaultContent": tblbuttonsedit + tblbuttondelete
},

{
"className": "highlighted_col",
"targets": [3]      // highlight ID column
}
]
})
});

2

Решение

Задача ещё не решена.

Другие решения

Других решений пока нет …

По вопросам рекламы [email protected]