jQuery Datatables : Searching and filtering with Ajax pagination

Question

I have a SQL table with 36000 entries to show in a Datatables list. The pagination works well since I develop it like this :

var table = $('.datatable').DataTable({
    pageLength : 20,
    lengthChange : false,
    processing : true,
    serverSide : true,
    ajax : {
        url :"ajax.php",
        type: "post",
    }
});

In my file ajax.php, I simply echo my lines (JSON encoded), according to the limit set by the page number.

The problem is native filtering and searching no longer works. When I want to filter a column, the "Processing" layer appears, then disappears but my data' is still the same. When I want to research through the table, nothing happens.

So, here are my questions :

How can I restore searching and filtering ?
How can I filter and search through all the lines (not only the ones that are showed) ? With Ajax, yes, but how in Jquery ?

Thank's in advance

Edit : Thank's to Abdul Rehman Sayed, I manage to do the search part. Here is what I have done :

var table = $('.datatable').DataTable({
    pageLength : 20,
    lengthChange : false,
    processing : true,
    serverSide : true,
    ajax : {
        data : function(d) {
            d.searching = get_search($('.datatable'));
        },
        url :"ajax.php",
        type: "post",
    },
    searching : false,
});

$('.datatable thead th').each(function() {
    var title = $(this).data('name');
    $('.datatable').find('tfoot tr').append('<td><input type="text" name="'+title+'"/></td>');
});

table.columns().every(function() {
    var that = this;
    $('input', this.footer()).on('keyup', function(e) {
        that.search(this.value).draw();
    }
});

function get_search(datatable) {
    var result = [];
    datatable.find('tfoot').find('input').each(function() {
        result.push([$(this).attr('name'), $(this).val()]);
    });
    return result;
}

For filtering, I develop an ugly code :

$('.datatable').find('th').click(function() {
    var item = $(this);
    removeClasses($('.datatable'), item.index());
    if(item.hasClass('sorting_asc')) {
        item.removeClass('selected_asc').addClass('selected_desc');
    } else {
        item.removeClass('selected_desc').addClass('selected_asc');
    }
});

function get_sorting(datatable) {
    var result = false;
    datatable.find('th').each(function() {
        var item = $(this);
        var name = item.data('name');
        if(item.hasClass('selected_asc')) {
            result = name+' ASC';
        } else if(item.hasClass('selected_desc')) {
            result = name+' DESC';
        } else {
            // continue
        }
    });
    return result;
}

function removeClasses(datatable, index) {
    datatable.find('th').each(function() {
        if($(this).index() !== index) {
            $(this).removeClass().addClass('sorting');
        }
    });
}

You are handling the pagination on the server-side, you also need to handle the searching and sorting on the server-side. When you make the searching/sorting request, you can see the relevant parameters in the querystring, see here for a full list — markpsmith
– markpsmith, Commented Sep 7, 2015 at 12:38

Abdul Rehman Sayed · Accepted Answer · 2015-09-07 13:36:27Z

10

You will have to do all the searching & filtering on server side.

For every request of search/filter or page, the datatable passes all of this as form data to the server page. Refer https://www.datatables.net/manual/server-side

You will have to use this form data to filter/search/paginate on the records on sql table & pass it accordingly to the client.

The datatable merely shows what it gets from the server.

edited Sep 7, 2015 at 13:36

answered Sep 7, 2015 at 13:27

Abdul Rehman Sayed

6,7129 gold badges51 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pauloscorps Over a year ago

Thank you for your answer. I manage to do the search part (see the original post edited), but I have no idea on how to manage filtering. Do you have an idea please ?

Abdul Rehman Sayed Over a year ago

There is a server side script for php here : datatables.net/examples/server_side/object_data.html

Karthikeyan K · Accepted Answer · 2020-12-30 14:34:09Z

0

Just pass the params in the URL itself

var table = $('.datatable').DataTable({
    pageLength : 20,
    lengthChange : false,
    processing : true,
    serverSide : true,
    ajax : {
        url :`ajax.php?param_name=${$("#filter").val()}`
    }
});

This code will work

answered Dec 30, 2020 at 14:34

Karthikeyan K

531 silver badge5 bronze badges

1 Comment

sanjeev shetty Over a year ago

Its better to use post method and custom data like ajax: { url: "url", data: function (d) { d.custom_param1="a"; d.custom_param2="b";}}

Yogesh Sonawane · Accepted Answer · 2025-09-06 03:17:15Z

I know, I'm giving answer too late, but stackoverflow.com is still a very best resource to understand things in this GenAI world.

Here is implementation with pagination, server-side search.

Client side:

// Get total count of records first
this.reportService.getInventoryCount(`file-master/get-inventory-count/${this.wid}`).subscribe({
      next: (response: any) => {
        this.initializeDataTable(response.totalRecords);
      },
      error: () => { }
 });

// initialize the datatable
initializeDataTable(totalRecords: number): void {
    $('#data-table').DataTable({      
      destroy: true,
      autoWidth: false,
      responsive: true,
      serverSide: true,
      processing: true,
      paging: true,
      lengthChange: true,
      pageLength: 10,
      buttons: ["copy", "csv", "excel", "pdf", "print", "colvis"],
      ajax: (data: any, callback: any) => {
        const skip = data.start;
        const length = data.length;
        const searchTerm = data.search.value;
        if (!isEmpty(searchTerm) && searchTerm.length <= 2) return;
        this.reportService.getInventoryDataPaginated(this.wid, skip, length, searchTerm).subscribe({
          next: (response: any) => {
            const tableData = response.data.map((item: any, index: number) => ({
              seqNo: skip + index + 1,
              complexity: item.complexity,
              externalCall: (item.callExternals.length > 0) ? `<a style="cursor:pointer;" class="call-externals" data-index="${index}">${item.callExternals.length} Call Externals</a>` : '-',
              called: (item.calledFrom.length > 0) ? `<a style="cursor:pointer;" class="called-from" data-index="${index}">${item.calledFrom.length} Called From</a>` : '-',
              usesEntities: "-",
              participateInWorkflow: "-",
              ...item
            }));
            callback({
              data: tableData,
              recordsTotal: searchTerm.length > 0 ? response.recordsTotal : totalRecords,
              recordsFiltered: searchTerm.length > 0 ? response.recordsTotal : totalRecords,
              draw: data.draw
            });
          }, error: () => {
            callback({ data: [], recordsTotal: totalRecords, recordsFiltered: totalRecords, draw: data.draw });
          }
        });
      },
      columns: [{ data: 'seqNo', title: 'Sr#' },
      { data: 'fileName', title: 'Object Name' },
      { data: 'fileTypeName', title: 'Type' },
      { data: 'loc', title: 'Loc' },
      { data: 'complexity', title: 'Complexity' },
      { data: 'externalCall', title: 'External Call' },
      { data: 'called', title: 'Called From' },
      { data: 'fileName', title: 'Description' }],
      columnDefs: [{
        targets: 1,
        render: function (data: string) {
          return `<span class="truncated-text" title="${data}"><a style="cursor:pointer;" class="file-name" data-index="${data}">${data}</a></span>`;
        }
      }, {
        targets: 2,
        render: function (data: string) {
          return `<span class="truncated-text" title="${data}">${data}</span>`;
        }
      }, {
        targets: 3,
        render: function (data: string) {
          return `<span class="truncated-text" title="${data}">${data}</span>`;
        }
      }, {
        targets: 4,
        render: function (data: string) {
          return `<span class="truncated-text" title="${data}">${data}</span>`;
        }
      }],
      select: {
        style: 'single'
      },
      order: [[0, 'asc']],
    }).buttons().container().appendTo('#data-table_wrapper .col-md-6:eq(0)');
}

There are two API calls to backend service which returns data as per need. Search also works here.

I've not added server side code intensionally, as this is purely dependent on when we want to show. Thanks!

Collectives™ on Stack Overflow

jQuery Datatables : Searching and filtering with Ajax pagination

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related