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 :

1. How can I restore searching and filtering ?
2. 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');
        }
    });
}

Solution

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.

Comments

[markpsmith]

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

[ashish bansal]

can you please link the jsfiddle

[Pauloscorps]

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]

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

[sanjeev shetty]

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