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 :
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 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.