将返回5000行的AJAX调用拆分为100行的多个AJAX调用 [英] Splitting an AJAX call which returns 5000 rows into multiple AJAX calls with 100 rows
问题描述
我有一个ajax调用，我正在将返回的数据加载到数据表中
I have a ajax call, and I am loading the data returned into a datatable
这是我的jquery ajax调用
Here is my jquery ajax call
<script type="text/javascript">
var oTable;
$(document).ready(function() {
window.prettyPrint() && prettyPrint();
$('#load').click(function()
{
var v = $('#drp_v').val();
var cnt = $('#drp_cnt').val();
var ctg = $('#drp_ctg').val();
var api = $('#drp_api').val();
var nt = $('#drp_nt').val();
$.post("ajax.php",
{ 'version':v,'category':ctg,
'country':cnt,'network_id':nt,
'api':api,'func':'show_datatable'},
function(data)
{
var aColumns = [];
var columns = [];
for(var i = 0; i < data.length; i++)
{
if(i>0)
break;
keycolumns = Object.keys(data[i]);
for(j = 0; j < keycolumns.length; j++)
{
if($.inArray(keycolumns[j],aColumns.sTitle)<=0)
{
aColumns.push({sTitle: keycolumns[j]}) //Checks if
columns.push(keycolumns[j]) //Checks if
}
}
}
var oTable = $('#jsontable').dataTable({
"columns":aColumns,
"sDom": 'T<"clear">lfrtip',
"oTableTools": {
"aButtons": [
{
"sExtends": "csv",
"sButtonText": "CSV",
}
]
}
});
oTable.fnClearTable();
var row = []
for(var i = 0; i < data.length; i++)
{
for(var c = 0; c < columns.length; c++)
{
row.push( data[i][columns[c]] ) ;
}
oTable.fnAddData(row);
row = [];
}
},'json');
});
});
</script>
这是我的php函数
function show_datatable($version,$ctg,$cnt,$nt,$api)
{
$cnt_table = "aw_countries_".$version;
$ctg_table = "aw_categories_".$version;
$off_table = "aw_offers_".$version;
$sizeof_ctg = count($ctg);
$cond_ctg = " ( ";
for($c = 0; $c < $sizeof_ctg ; $c++)
{
$cond_ctg = $cond_ctg." $ctg_table.category = '".$ctg[$c]."' ";
if($c < intval($sizeof_ctg-1))
$cond_ctg = $cond_ctg." OR ";
else if($c == intval($sizeof_ctg-1))
$cond_ctg = $cond_ctg." ) ";
}
$sizeof_cnt = count($cnt);
$cond_cnt = " ( ";
for($cn = 0; $cn < $sizeof_cnt ; $cn++)
{
$cond_cnt = $cond_cnt." $cnt_table.country = '".$cnt[$cn]."' ";
if($cn < intval($sizeof_cnt-1))
$cond_cnt = $cond_cnt." OR ";
else if($cn == intval($sizeof_cnt-1))
$cond_cnt = $cond_cnt." ) ";
}
$sizeof_nt = count($nt);
$cond_nt = " ( ";
for($n = 0; $n < $sizeof_nt ; $n++)
{
$cond_nt = $cond_nt." $off_table.network_id = '".$nt[$n]."' ";
if($n < intval($sizeof_nt-1))
$cond_nt = $cond_nt." OR ";
else if($n == intval($sizeof_nt-1))
$cond_nt = $cond_nt." ) ";
}
$sizeof_api = count($api);
$cond_api = " ( ";
for($a = 0; $a < $sizeof_api ; $a++)
{
$cond_api = $cond_api." $off_table.api_key = '".$api[$a]."' ";
if($a < intval($sizeof_api-1))
$cond_api = $cond_api." OR ";
else if($a == intval($sizeof_api-1))
$cond_api = $cond_api." ) ";
}
$output = "";
$sql = "SELECT *
FROM $off_table,$cnt_table,$ctg_table
WHERE $off_table.id = $cnt_table.id
AND $off_table.id = $ctg_table.id
AND ".$cond_api."
AND ".$cond_nt."
AND ".$cond_cnt."
AND ".$cond_ctg;
$result = mysql_query($sql);
$arr_result = array();
while($row = mysql_fetch_assoc($result))
{
$arr_result[] = $row;
}
$arr_result_enc = json_encode($arr_result);
echo $arr_result_enc;
}
现在，我想修改此代码.说我想像这样工作:
Now, I want to modify this code. Say I want to work it like this:
我将要求v，AJAX将向我发送100行，然后再向我发送100行，然后再向我发送100行.
I will call for v, and the AJAX will send me 100 rows once, then again 100 rows and then again 100 rows.
我的意思是将AJAX调用拆分为一个接一个地返回所有数据的块.比如说，将多次调用AJAX，并且每次它将向我发送100个数据块.
I mean splitting the AJAX call to returns chunks of all the data one after another. Say something like there will be multiple times when the AJAX will be called, and each time it will send me 100 chunks of data.
在进行工作的同时，会有一个带有取消按钮的进度条.
While the work will be going on, there will be a progress bar with a cancel button.
如果我单击取消按钮，那么如果调用了3次AJAX函数，它将显示300数据，然后AJAX将停止.该数据库将仅显示300个数据.
If I click the cancel button, then if 3 times the AJAX function have been called, it will show me 300 data and then the AJAX will be stopped. The database will show only 300 data.
推荐答案
这是对客户端代码的繁重重构，其中，整个过程分为三个独立的函数getBatch()，makeRows()和
Here's a heavy refactoring of the client-side code, in which the overall process is split into into three separate functions getBatch(), makeRows() and showData(), and kicked off by a very modified version of the original click handler.
批处理由getBatch()控制，它发出带有两个额外参数-start和batchSize的ajax请求.这些指示服务器端脚本返回哪一批数据.
Batching is controlled by getBatch(), which makes a ajax requests with two extra params - start and batchSize. These instruct the server-side script which batch of data to return.
makeRows()和showData()只是getBatch()调用的同步工作程序功能.
makeRows() and showData() are just synchronous worker functions called by getBatch().
我假设在DOM元素"#progress"中报告了进度.您可以在这里举报您的喜好.我将其保持简单，以显示累积的行数.通过添加批处理计数器，您还可以显示已完成的批处理数量.
I have assumed that progress is reported in a DOM element "#progress"`. You can report what you like here. I've kept it simple, showing the accumulated row count. By adding a batch counter, you could also show the number of completed batches.
取消是通过将外部变量allowBatch设置为false来实现的.将继续找到当前请求的批次，但将撤回检索其他批次的权限.
Cancellation is achieved by setting outer var allowBatch to false. The currently requested batch will continue to be found but permission to retrieve further batches is withdrawn.
为了很好，在过程的开始和结束时，启用/禁用了各种DOM元素，或显示/隐藏了各种DOM元素.如果不需要，可以将其省略.
For good measure various DOM elements are enabled/disabled or shown/hidden at the start and end of the process. This can be omitted if not required.
var oTable;
$(document).ready(function() {
window.prettyPrint() && prettyPrint();
var aColumns = [],
columns = [],
rows = [],
$progress = $("#progress"),
allowBatch;
function getBatch(options) {
return $.post("ajax.php", options, 'json').then(function(data) {
var promise;
if(allowBatch && data && data.length) {
$progress.text(makeRows(data));
options.start += batchsize;
promise = getBatch(options);
} else {
promise = $.when(showData());
}
return promise;
});
}
function makeRows(data) {
var keycolumns, i, j;
if(aColumns.length == 0) {
keycolumns = Object.keys(data[0]);
for(j = 0; j < keycolumns.length; j++) {
if($.inArray(keycolumns[j], aColumns.sTitle) <= 0) {
aColumns.push({sTitle: keycolumns[j]});
columns.push(keycolumns[j]);
}
}
}
for(i = 0; i < data.length; i++) {
rows.push( columns.map(function(col) {
return data[i][col];
}) );
}
return rows.length;
}
function showData() {
var oTable = $('#jsontable').dataTable({
'columns': aColumns,
'sDom': 'T<"clear">lfrtip',
'oTableTools': {
"aButtons": [{
"sExtends": "csv",
"sButtonText": "CSV"
}]
}
});
oTable.fnClearTable();
$.each(rows, function(row) {
oTable.fnAddData(row);
});
return oTable;
}
$('#load').on('click', function(e) {
e.preventDefault();
var $load = $(this).attr('disabled', true),
$cancel = $("#cancel").show();
$progress.text('').show();
aColumns.length = columns.length = rows.length = 0;
allowBatch = true;
getBatch({
'version': $('#drp_v').val(),
'category': $('#drp_ctg').val(),
'country': $('#drp_cnt').val(),
'network_id': $('#drp_nt').val(),
'api': $('#drp_api').val(),
'func': 'show_datatable',
'start': 0,
'batchsize': 100
}).then(function(oTable) {
// ... all done
$load.attr('disabled', false);
$cancel.hide();
$progress.hide();
});
});
$("#cancel").on('click', function(e) {
e.preventDefault();
allowBatch = false;
}).hide();
$("#progress").hide();
});
仅测试了解析错误，因此可能需要调试
您仍然需要修改服务器端脚本以接受两个附加参数start和batchsize并相应地编写其SQL.那应该是微不足道的.
You will still need to modify the server-side script to accept the two additional parameter start and batchsize and compose its SQL accordingly. That should be fairly trivial.
我最不确定的部分是makeRows()，我对此稍有挣扎.如果没有任何效果，请先看看那里.
The part I'm most uncertain about is makeRows(), which I struggled with slightly. If nothing works, look there first.
这篇关于将返回5000行的AJAX调用拆分为100行的多个AJAX调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
