拆分一个AJAX调用，该调用将5000行返回到100行的多个AJAX调用中

Question

I have a ajax call, and I am loading the data returned into a datatable

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>

And here's my php function

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:

I will call for v, and the AJAX will send me 100 rows once, then again 100 rows and then again 100 rows.

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.

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.

Answer 1

JQ:

// counter that allows you to get a new set of rows
var step = 0;
// set variable if you want to restrict the number of rows will be loaded
var maxStep = 0;//
// how many rows should be returned
var count = 100;
// if the cancel button is pressed
var cancel = false;

$(function() {

    $('#load').click(function(){

        getData();

    })

    $('#cancel').click(function(){

        cancel = true;

    })

});

function getData()
{
    step++;

    //If cancel variable is set to true stop new calls
    if(cancel == true) return;
    // checks if the variable is set and limits how many rows to be fetched
    if(maxStep >0 and step >= maxStep) return;


    $.post('ajax.php'
        ,{
            'step':step,
            'count':count,
        }
        ,function(data, textStatus, jqXHR){   

             // do something with the data

             // when it finishes processing the data, call back function
             getData();

        }
        ,'json'
    )       
}

AJAX.PHP

$step = 0;
if(isset($_POST['step'])) $step = (int)$_POST['step'];

$count = 0;
if(isset($_POST['count'])) $count = (int)$_POST['count'];



if($step>0 and $count>0)
{
    $offset = ($step-1) * $count;        
    $limit = $offset.','.$count;

    // --------------        
    // your code here
    // --------------

    $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."
        LIMIT ".$limit;// <- limit

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



    // echo rows
    echo json_encode($rows);        
}