How to manage datatable with a table with 100 000 lines

madaweb

Hi!

I use datatable with a PHP framework (CodeIgniter) which manages a table with records more than 100 000.
The number of lines is too high so I have to paginate lines by loading ajax for example, does this functionnality already present in datatable?

For the moment, I paginate with CodeIgniter pagination but my problem is: I have to use the simple version of datatable
[code][/code]
Now I want to use "Search" but not the listbox "records per page".

How to do that? How to make "Search" work with ajax loaded datas?

allan

Server-side processing: http://datatables.net/usage/server-side . It will cope with millions of rows.

There is even a CodeIgniter library for DataTables:
https://github.com/IgnitedDatatables/Ignited-Datatables/
https://github.com/blake-nouribekian/codeigniter-datatables

(both not supported by myself - I know nothing about CI!).

Allan

madaweb

Thanks for answering me quick!

You know nothing about CI but you give me excellent links especially "codeigniter-datatables", that helps me a lot!

My problem is now with how to add "Edit" button in each row of the datatable? I look at aoColumns and aoColumnDefs parameters but I don't know if I'm right or wrong to learn about this.

allan

There is an example here: http://editor.datatables.net/release/DataTables/extras/Editor/examples/inlineControls.html

Allan

madaweb

Wonderful, there are many examples in http://editor.datatables.net/examples/

I'll take a look right now

madaweb

I'm back!

I'm stuck on using "inlineControls" : http://editor.datatables.net/release/DataTables/extras/Editor/examples/inlineControls.html

[code]$('#dirigeantdata').dataTable({
"sDom": "<'row-fluid'<'span6'l><'span6'f>r>t<'row-fluid'<'span12'i><'span12 center'p>>",
"sPaginationType": "bootstrap",
"bProcessing": true,
"bServerSide": true,
"sServerMethod": "GET",
"sAjaxSource": "dirigeants/getTable",
"iDisplayLength": 10,
"aLengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
"aaSorting": [[0, 'asc']],
"aoColumns": [
{ "bVisible": true, "bSearchable": true, "bSortable": true },
{ "bVisible": true, "bSearchable": true, "bSortable": true },
{ "bVisible": true, "bSearchable": true, "bSortable": true }
],
"oLanguage": {
"sProcessing": "Traitement en cours...",
"sSearch": "Rechercher :",
"sLengthMenu": "Afficher _MENU_ éléments",
"sInfo": "Affichage de l'élement _START_ à _END_ sur _TOTAL_ éléments",
"sInfoEmpty": "Affichage de l'élement 0 à 0 sur 0 éléments",
"sInfoFiltered": "(filtré de _MAX_ éléments au total)",
"sInfoPostFix": "",
"sLoadingRecords": "Chargement en cours...",
"sZeroRecords": "Aucun élément à afficher",
"sEmptyTable": "Aucune donnée disponible dans le tableau",
"oPaginate": {
"sFirst": "Premier",
"sPrevious": "Précédent",
"sNext": "Suivant",
"sLast": "Dernier"
},
"oAria": {
"sSortAscending": ": activer pour trier la colonne par ordre croissant",
"sSortDescending": ": activer pour trier la colonne par ordre décroissant"
}
}
});[/code]

How to add EDIT and DELETE button in each row of my server-side datatable? Each EDIT and DELETE button has specific action corresponding to row id in database!

madaweb

In fact, it seems that Editor is another story!

allan

In what regard?

To add edit and delete buttons you'd use mRender or sDefaultContent - like in the example you linked to.

Allan

madaweb

Such is the principle.
But how to use sDefaultContent for example to add Edit and Delete links in thelast column?
I try to use it but my datatable using ajax is crashing => TypeError: j[(f - d)] is undefined

For server-side processing, do I have to insert it in my PHP code?

For my curiosity, how to make Datatable sortable?

allan

> But how to use sDefaultContent for example to add Edit and Delete links in thelast column?

Its in the example you linked to :-) :

[code]
{
"mData": null,
"sClass": "center",
"sDefaultContent": 'Edit / Delete'
}
[/code]

> I try to use it but my datatable using ajax is crashing => TypeError: j[(f - d)] is undefined

Please link us to a test case.

> For server-side processing, do I have to insert it in my PHP code?

If you are using Editor's PHP classes, you just need to add `bServerSide:true` :
http://editor.datatables.net/release/DataTables/extras/Editor/examples/server-side-processing.html

> For my curiosity, how to make Datatable sortable?

It is by default: http://datatables.net/release-datatables/examples/basic_init/zero_config.html

Allan

madaweb

Thanks allan for your answer. So I do like that now and it works:
[code]"aoColumns": [
{ "bVisible": true, "bSearchable": true, "bSortable": true },
{ "bVisible": true, "bSearchable": true, "bSortable": true },
{ "bVisible": true, "bSearchable": false, "bSortable": false,
"mRender": function ( data, type, full ) {
return ' Modify Delete';
}
}
],[/code]
In fact, I new to use the "data" returned from server.

Sorting! Ok, so datatables are sortable by default! I use Twitter boostrap styling, so I'm looking now how to display the sorting images ...

allan

http://datatables.net/blog/Twitter_Bootstrap_2 :-)

madaweb

Thanks! Datatable and Bootstrap, I thinks it's a good association.