Correctly reload Table in Bootstrap/DataTable-CodePudding

I have a simple Table with Bootstrap and DataTable styling. Thanks to Edit/Delete/Add buttons, I am able to interact with it. For example, this is the JS code to Edit a row:

    function editRow(){
        var catProg = document.getElementById('editCatProg_Input').value;
        var catUser = document.getElementById('editCatUser_Input').value;
        var catPass = document.getElementById('editCatPass_Input').value;
        var catInUso = document.getElementById('editCatInUso_Input').value;
        var catDesc = document.getElementById('editCatDesc_Input').value;
        var catUltimo = document.getElementById('editCatUltimo_Input').value;
        var catDat = document.getElementById('editCatDat_Input').value;

        $.ajax({
            url: 'UpdateRow.php',
            type: 'POST',
            data: { 
                CatProg: catProg,
                CatUser: catUser,
                CatPass: catPass,
                CatInUso: catInUso,
                CatDesc: catDesc,
                CatUltimo: catUltimo,
                CatDat: catDat
            },
            dataType: "text",
            success: function(result){
                // relaod (fetch db again)
                window.location.replace(window.location.pathname   window.location.search   window.location.hash);
            },
            error: function(){
                alert("Request did not succeed. Reload and try again.");
            }
        });
    }

As you can see, I am forced to reload the whole page to update the Table. This brings two main problems:

If I am editing a row on page X, the reload will bring me again to page 1. I'd like it to stay on page X, with a reloaded table;
The page's refresh is not visually pleasing.

Is there any way to accomplish that? I tried this way:

In a JavaScript function obtain the new table from a Php script by an Ajax call, and substituite it to the old table;
Refresh the table with ajax.reload() but I am afraid I did not understand how to correctly use it.

Can anyone give me any ideas and/or snippets of code I might implement? Thank you in advance.

CodePudding user response：

You can send a JSON object, representing the successfully updated data record, back from the server to DataTables. You can use the DataTables API to take that response JSON and use it to update the data in the underlying DataTable, for the specific row.

I am going to assume you are using input fields in the table, as that is the more complicated case. But if you are using a modal dialog (or something similar) then the approach would be a simplified version of this.

My source data for testing is:

{
  "data": [
    {
      "name": "Tiger Nixon",
      "position": "System Architect",
      "salary": "$320,800"
    },
    {
      "name": "Garrett Winters",
      "position": "Accountant",
      "salary": "$170,750"
    },
    ...
  ]
}

The "salary" column contains editable data:

The script for creating the table and handling the updates is as follows:

$(document).ready(function() {

  // src is the button which was clicked:
  function editRow(src) {
    let row = $(src).closest("tr");
    // the data fields for the updated row:
    let name = row.find(".name").text();
    let position = row.find(".posn").text();
    let salary = row.find(".sal").val();

    // fields converted to JSON:
    let rowData = { 
      name: name,
      position: position,
      salary: salary
    };
    
    // We will need this to do an in-place update of the edited row.
    // It is the internal DataTables row index number for the edited row:
    let rowIndex = table.cell( $(src).closest("td") ).index().row;

    $.ajax({
      url: '[your URL goes here]',
      type: 'POST',
      contentType: 'text', // type of data sent to the server
      data: JSON.stringify(rowData), // the data we send to the server
      dataType: 'json', // type of data returned from the server
      success: function(data, status, xhr) {
        // {order:'index'} uses the index based on the original data load order,
        // otherwise it would use the current sort/filter order.
        // invalidate() causes the DataTables cache to be re-populated. 
        // Using 'dom' means the data is reloaded from user-provided DOM values.
        // The draw( false ) call ensures the table does not reset to page 1:
        table.rows( rowIndex, {order:'index'} ).invalidate( 'dom' ).draw( false );
        
      },
      error: function(xhr, status, error){
        console.log( error );
      }
    });

  }

  var table = $('#example').DataTable( {
    ajax: {
      method: "GET",
      url: "[your URL goes here]"
    },
    columns: [
      { title: "Name", data: "name", className: "name" },
      { title: "Position", data: "position", className: "posn" },
      { 
        title: "Salary", 
        data: "salary",
        render: function ( data, type, row, meta ) {
          if ( type === 'display' ) {
            // render the display value first, so there is an input control:
            return '<input  value="'   data   '">';
          } else {
            // get the value from the input control and use just the value
            // for sorting and filtering:
            let api = $( '#example' ).DataTable();
            let td = api.cell({row: meta.row, column: meta.col}).node();
            let inputData = $('select, input', td).val();
            return inputData; // the value used for sorting and filtering
          }
        } 
      },
      { defaultContent: '<button  type="button">Update</button>' }
    ]

  } );
  
  // attach a click event to visible and not-yet-visible buttons:
  $( "#example" ).on( "click", ".upd", function() {
    editRow( this );
  });
  

} );

When the user clicks on an update button, the related function retrieves the row data values. In my case, that is 2 static values and one user-provided value (salary).

(If you are using a modal dialog then this part may be simpler for you - more like the code in your question.)

What gets submitted to the server is a stringified piece of JSON - for example:

{"name":"Airi Satou","position":"Accountant","salary":"$162,701"}

What gets returned (after a successful save operation) is basically the same information, but as a JSON object.

Note - this could be different from what was sent, if (for example) you are handling additional fields which could change (e.g. "last updated" timestamp, and so on). In my simple example, the returned data is identical to the sent data.

We take this returned data and we update the underlying DataTable (which, up to this point) has no awareness of what the user typed into the salary input field. What the user typed only exists in the DOM - the HTML - but not in DataTables, yet.

The line which takes care of this is:

table.rows( rowIndex, {order:'index'} ).invalidate( 'dom' ).draw( false );

But please also note the row().data( your_json_here ) function, mentioned below.

The invalidate() function handles changes to the cache. The draw( false ) function ensures all data is refreshed, but without resetting the table to page 1 - that is what the false parameter does.

If you have returned data which is different from the sent data (the above "last update" point), then you can use row().data( your_json_here ) function as well, to set the updated row data, after invalidating the cached data.

There is a lot going on there. But it draws the updated data via an Ajax call, so the full page is not refreshed. I added more comments in the code, for further clarifications.

Credits

I took some inspiration from this old question: Search within dropdowns searches all the dropdown options instead of selected option