Server side example without MySQL

Brecht2727

Can someone provide me an example how to work in ajax server side without mysql?
I have a cURL in my ajax and with a foreach a loop through all records. That's working, but column sorting, pagination or search is not working.

tangerine

The simple server-side example is here:
https://datatables.net/examples/server_side/simple.html

Brecht2727

@tangerine: your link is still with MySQL database

kthorngren

Are you wanting to enable server side processing? You don't need to just for ajax loaded data. See this doc for the differences. If you still need server side processing for your solution then you will need to write a server side processing script that follows the SSP protocol. If you don't need SSP then the ajax loaded data will be sorted client side.

Kevin

Brecht2727

I think server side processing will give me more possibilities? Not shure about it. Just a thought.
I have to reload the data in the table sometimes and i need also checkboxes for every row, search option, pagination and 2 datepicker fields (date from - to). I know how to work with server side script with MySQL but not without.
I will try to use the SSP protocol. Do you have an example how to use it without database? Because all examples i can find is with database

kthorngren

I think server side processing will give me more possibilities?

Server side processing is used for large data sets that would otherwise cause performance issues if the full set of data is loaded at the client. Server side processing requires the server script to perform paging, searching and sorting then return just the page of data to the client. SSP will actually be more complicated as many of the client side tools like row.add() or Search API's aren't enabled. It is expected that the SSP script perform these activities.

Unless you have thousands of rows that cause performance issues you will be better served with client side processing. You can do all those thing you mentioned without the complexities of creating a SSP script. You just need to return all your data via ajax.

I will try to use the SSP protocol. Do you have an example how to use it?
The ssp.class.php script is used for the Server side processing examples.

Kevin

Brecht2727

I am trying to use the client side processing but have a question about sending extra params with the data. How can i extend the data?
I need to send also the 2 datepicker fields (date from - to) to the ajax.

My code, for now, looks like this:

      $.ajax({
        url: 'ajax/ajax-test.php',
        cache: false,
        async: false,
        dataType: 'json',
        success: function(data) {

          $('#myTable').DataTable({
            dom: '<"top"<"left-col"B><"center-col"r><"right-col"f>>t<"bottom"lip>',
            data: data,
            columns: [
               { data: 'check' },
               { data: 'id' },
               { data: 'company' },
               { data: 'invoice' },
               { data: 'type' },
               { data: 'datum' }
            ],
            columnDefs: [
              { 
                  "targets": [0],
                  "orderable": false,
                  "class": 'text-center',
              },
              { 
                  "targets": [1],
                  "class": 'text-center',
              },
              { 
                  "targets": [3],
                  "class": 'text-center',
              },
              { 
                  "targets": [4],
                  "class": 'text-center',
              },
              { 
                  "targets": [5],
                  "class": 'text-center',
              },
            ],        
            order: [[1, "desc"]],        
          });

        }
      });

kthorngren

You will want to use the data option. Refer to the jQuery ajax() docs for details.

Kevin

Brecht2727

@kthorngren: I was not thinking that way, but it works like you suggested.
I was thinking in my case to extend the data: data from the datatable. Is this also possible to do? Maybe for future projects? Just to know ;-)

allan

In DataTables terms, what you have there could be rewritten as:

$('#myTable').DataTable({
    ajax: {
        url: 'ajax/ajax-test.php',
        dataSrc: '',
    },
    dom: '<"top"<"left-col"B><"center-col"r><"right-col"f>>t<"bottom"lip>',
    data: data,
    columns: [
        { data: 'check' },
        { data: 'id' },
        { data: 'company' },
        { data: 'invoice' },
        { data: 'type' },
        { data: 'datum' },
    ],
    columnDefs: [
        {
            targets: [0],
            orderable: false,
            class: 'text-center',
        },
        {
            targets: [1, 3, 4, 5],
            class: 'text-center',
        },
    ],
    order: [[1, 'desc']],
});

i.e. no need to make your own $.ajax() call. The ajax.dataSrc property is set to an empty string to tell DataTables to expect an array of data.

I've also taken the opportunity to simplify your columnDefs array since there were repeating declarations.

Allan

Brecht2727

Thanks Allan for the tips, but i have the problem that the data: data is coming from the ajax success so i can not use this, i think?

Is there a solution for my second question: how to extend the data: data from the datatable for client side

kthorngren

Remove data: data and let the Datatables ajax option populate the table.

Kevin

Brecht2727

Thanks Kevin, but now we come to the same problem we had before. How can i send my extra data params (date from-to input fields) with this code:

    ajax: {
        url: 'ajax/ajax-test.php',
        dataSrc: '',
    },

kthorngren

Use ajax.data as a function. There are examples in the docs.

Kevin

Brecht2727

Oh yes, now i see... Many thanks to Allan and Kevin for the help.
All working now. I end with this code for the ajax part in the datatable:

              ajax: {
                  url: 'ajax/ajax-test.php',
                  dataSrc: '',
                  type: "POST",
                  data: {
                      fdate: $('#datepicker').val(),
                      tdate: $('#datepicker1').val()
                  }                  
              },

Complete code is now:

            $('#myTable').DataTable({
              ajax: {
                  url: 'ajax/ajax-test.php',
                  dataSrc: '',
                  type: "POST",
                  data: {
                      fdate: $('#datepicker').val(),
                      tdate: $('#datepicker1').val()
                  }                  
              },
              columns: [
                  { data: 'check' },
                  { data: 'id' },
                  { data: 'company' },
                  { data: 'invoice' },
                  { data: 'type' },
                  { data: 'datum' },
              ],
              columnDefs: [
                  {
                      targets: [0],
                      orderable: false,
                      class: 'text-center',
                  },
                  {
                      targets: [1, 3, 4, 5],
                      class: 'text-center',
                  },
              ],
              order: [[1, 'desc']],
            });

kthorngren

If you use something like ajax.relaod() to reload the table when the datepicker values change you will need to use ajax.data as a function to get the changed values. Otherwise the values sent will not change. They will be the values at the time of initialiation.

Kevin

Brecht2727

Ok Kevin, thanks. I changed my code:

            $('#myTable').DataTable({
              ajax: {
                url: 'ajax/ajax-get-table-data.php',
                dataSrc: '',
                type: "POST",
                data: function ( d ) {
                  d.fdate = $('#datepicker').val(),
                  d.tdate = $('#datepicker1').val()
                }                
              },
              language: {
               url: 'https://cdn.datatables.net/plug-ins/1.12.1/i18n/nl-NL.json'
              },              
              columns: [
                  { data: 'check' },
                  { data: 'id' },
                  { data: 'company' },
                  { data: 'invoice' },
                  { data: 'type' },
                  { data: 'datum' },
              ],
              columnDefs: [
                  {
                      targets: [0],
                      orderable: false,
                      class: 'text-center',
                  },
                  {
                      targets: [1, 3, 4, 5],
                      class: 'text-center',
                  },
              ],
              order: [[1, 'desc']],
            });

Brecht2727

But when i use the reload function i have an error

      setInterval( function () {
          $('#myTable').ajax.reload();
      }, 10000 );

Uncaught TypeError: Cannot read properties of undefined (reading 'reload')

allan

setInterval( function () {
    $('#myTable').DataTable().ajax.reload();
}, 10000 );

ajax.reload() is a DataTables API method, not a jQuery one.

Kevin - thanks for catching my error earlier!

Allan

Brecht2727

Thanks Allan, error is gone but in console i can see that the setInterval is working but i can`t see the datatable is reloading. There is no indication like Loading message in the table?

setInterval( function () {
    $('#myTable').DataTable().ajax.reload();
    console.log('test');
}, 10000 );

Brecht2727

I figured out that the option processing: true must be enabled to see the message! Now it is ok. But is it possible to restyle this message? I am using bootstrap 5

allan

Sure - use the div.dataTables_processing selector in CSS and style as you require.

Allan

Brecht2727

There is something else after ajax.reload function. Is it possible to hold the pagination position? Now after reload the paging is start from 1 again.
In server side processing we have the serverState: true but it is not working on client side.

kthorngren

Take a look at the ajax.reload() docs for controlling paging. The second example in the docs show how to stay on the same page.

Kevin

Brecht2727

Yes, everything is working now! Now i have a fully working example of client side script.
Many thanks Allan and Kevin!!!