How to Refresh a DataTable without Losing Your Current Page or Ordering CodeUnit 28 MAY 2012

DataTables is a fantastic plug-in for the jQuery Javascript library that I use for pretty much all my web development projects. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table – in other words, it creates useful dynamic, with oodles of extra functionality, tables out of plain old vanilla HTML tables.

Anyway, there is often a need to be able to refresh your datatable after applying some action to the source data, and the usual method of forcing a refresh is by calling the standard fnDraw() function on the table object. However, whilst this will redraw the table, it will basically reset you completely, removing any filtering and jumping back to page 1 if you have a table with pagination.

So not ideal.

Thankfully one of the DataTables forum guys came up with a handy bit of extension code which introduces a new function called fnStandingRedraw() which extends the datatables API and allows you to fresh the datatable without losing your current view.

To implement, simply put the following code into a .js file and load it after you load the standard datatables.js library:

$.fn.dataTableExt.oApi.fnStandingRedraw = function(oSettings) {
    //redraw to account for filtering and sorting
    // concept here is that (for client side) there is a row got inserted at the end (for an add)
    // or when a record was modified it could be in the middle of the table
    // that is probably not supposed to be there - due to filtering / sorting
    // so we need to re process filtering and sorting
    // BUT - if it is server side - then this should be handled by the server - so skip this step
    if(oSettings.oFeatures.bServerSide === false){
        var before = oSettings._iDisplayStart;
        oSettings.oApi._fnReDraw(oSettings);
        //iDisplayStart has been reset to zero - so lets change it back
        oSettings._iDisplayStart = before;
        oSettings.oApi._fnCalculateEnd(oSettings);
    }
     
    //draw the 'current' page
    oSettings.oApi._fnDraw(oSettings);
};

Now on your page code, you can simply ask for a refresh by doing this:

//var oTable1 = $('#mytable').dataTable();
oTable1.fnStandingRedraw();

Nifty.

Related Link: http://datatables.net/

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.

  • Jonathan Wilson

    Thank you! Have exactly this problem and I thought there must be a way of doing this.
    Really appreciate the post.

  • Fernando

    Yo’re a genius. Hours and hours… and you had it!! ;-) Thanks for sharing it.

  • Anant Ghate

    Great article!!! Thanks!!!

  • xiaoz

    You are so great,I finally solved the question bothered me a little more than a month with your help! thank you very much

  • xiaoz

    There is another problem,If there is no data in current page , you should jump to the previous page,How to solve?

  • Carlos Davis

    Beatiful :). Thanks

  • Alberto

    Oh, you save my life, I thought that fix it would takes hours and in 2 min it is working as i wanted.

  • Khader.M.A

    It is a great great great post. I found my solution here. Thanks a lot Craig Lotter.

  • ladriryah
  • Fernando

    Perfect post, but how can I use this with sql?

  • swaminathan

    Thank you!

  • Great!!! Thanks!!!

  • Majid Lotfi

    Hi,

    I copy and paste your code,and call it here :

    $(“#divPopUp”).dialog({

    resizable: true,

    autoOpen: false,

    height: 500,

    width: 550,

    modal: true,

    buttons: {

    “Assign”: function () {

    var agenciesId = $(this).find(“:checkbox:checked”).map(function () {

    return this.value;

    }).get();

    //alert(“agenciesId :” + agenciesId);

    var ruleIDd = $(“#divPopUp”).data(‘param_1’);

    $.ajax({

    url: ‘assignRenameRuleToAgency.do’,

    data: {“agenciesId”: agenciesId,”ruleId”:ruleIDd },

    success: function(response) {

    toastr.success(response.message);

    //oTable.fnDraw();

    }

    })

    oTable.fnStandingRedraw();

    $(this).dialog(“close”);

    },

    Cancel: function () {

    $(this).dialog(“close”);

    }

    },

    close: function () {

    $(this).find(“:checkbox”).removeAttr(‘checked’);

    $(this).dialog(“close”);

    }

    });

    but I got :

    Uncaught SyntaxError: Unexpected identifier

    Thanks, your help is appreciated.

    • You didn’t comment out “oTable.fnStandingRedraw();” – unless you have an object variable with that name (plus the existing function name), your code will throw an error.

      • Majid Lotfi

        Thanks for your reply, I did not understand what do you mean by comment it out.

  • Huss Rafa :)

    Indeed helped a Lot :)

  • Andy Three Saputra

    I HAVE PROBLEM.. YOU CAN HELP ME.. DATATABLES NOT RELOAD AFTER AJAX SUCCES INSERT DATABASE
    1. DATATABLES VIEW FROM DATABASE SUCCESS
    2. INSERT INTO TABEL IN DATABASE SUCCESS
    3. RELOAD DATATABLES NOT WORKING.

    i USE : .fnReloadAjax(); NOT WORKING
    PLEASE CONFIRM VIA my EMAIL

    MY CODE :

    $(document).ready(function() {
    $(“#assign”).dataTable();
    $(“#sub”).click(function() {
    var act = $(“#act”).val();
    var myCheckboxes = new Array();
    $(“#myCheckboxes:checked”).each(function() {
    myCheckboxes.push($(this).val());
    });
    if (act == “”) {
    alert(“Fill All Fields”);
    window.location=”index.php”;
    } else {
    $.ajax({
    type: “POST”,
    url: “_config/conf_assign.php”,
    data: “myCheck=”+myCheckboxes+”&act=”+act,
    success: function(msg) {
    if(msg==’true’){
    $(“#add_err”).html(“<img src='..//preloader.gif’ />Proses Update Succes”);

    /*here : i WANT TO DATATABLE RELOAD AGAIN…
    /* conf_assign.php = SUCCESS INSERT TO DATABASE AND ECHO TRUE
    /* AND I WANT DATATABLE RELOAD…

    } else if(msg==’false’) {
    $(“#add_err”).html(“<img src='..//warning.png’ />Proses Update Failed”);
    } else {
    $(“#add_err”).html(“<img src='..//warning.png’ />Proses Wrong!!!”);
    }
    },
    beforeSend:function(){
    $(“#add_err”).html(“<img src='..//preloader.gif’ />Proses…”);
    }
    });
    return false;
    }
    });
    });

    No
    Username
    Real Name
    Email
    Level
    Registrasi
    Assign
    Date

    SelectMulti(“TABEL”,”*”,”aktif=’0′”,”ORDER BY adminnama ASC”);
    $n=1;
    foreach ($sql as $rs){
    echo”
      $n  
    $rs[play]
    $rs[adminnama]
    $rs[ILLl]
    $rs[level]
    “; f_tgl($rs[‘tgldaftar’]); echo”
    $rs[aktifbyadmin]
    “; f_tgl($rs[‘tglaktif’]); echo”
    “;
    $n++;
    }
    $db = null;
    ?>

  • ali

    please tell me that when i am using this table and i have one field which selects some data against a record from the selection and update this data in database. When i move to 2nd page the selection made in first page get vanishes from the table as well as from the database. Same is the case with searching please provide me some solution why its happening?

    • If you are loading data from a URL, then each page move is a new data fetch. A good solution would be to store selections in a $_SESSION variable and then remark the selected records in the actual data script using that stored data.

      • Sri

        Hi, i tried the solution but it didn’t work up.Here is what i did i
        created a separate .js file and then when i call the
        table.fnStandingRedraw(); to the page.I couldn’t find any progress.

        Here is my code.

        function ModalFormSuccess() {

        var LocSearch = document.getElementById(“LocSearch”).value; /*Search*/
        var table=$(‘#dacrudtable’).dataTable({
        “sDom”: “<'row'r>t<'row'>”,
        “sAjaxSource”: “ajax.php”,
        “fnServerParams”: function(aoData) {
        aoData.push({
        “name”: “cSearch”,
        “value”: LocSearch
        });
        },

        “bServerSide”: false,
        “bSearchable”: false,
        “bAutoWidth”: false,
        “bDestroy”: true,
        “bStateSave”: true
        ‘iDisplayLength’: 500,
        “aoColumns”: [{ “mData”:”status”, “sClass”:”center “},
        { “mData”:”void_invoice”, “sClass”:”center “},
        { “mData”:”created_date”, “sClass”:”center” },
        { “mData”:”transfer_in_date”, “sClass”:”center” },
        ]
        table.fnStandingRedraw();
        }

        Could you help me to resolve