Datatable carga 100.000 registros en una sola página

JoseAlberto

Estoy cargando una tabla de MySQL con 100.000 registros en un datatable, utilizo serverSide y ajax para traer los datos de la tabla.
También tengo otros scripts para el lenguaje en español, los botones de excel, PDF, etc.
El script carga bien los datos (muy rápido) pero al pintarlos en la tabla los coloca todos en una sola página, es decir, coloca bien todo el formato que le doy a la tabla con su paginación pero todos los registros los coloca en una sola página.
He buscado en los foros pero no consigo nada que me ayude.

rf1234

I am loading a MySQL table with 100,000 records in a datatable, I use serverSide and ajax to fetch the data from the table.
I also have other scripts for the spanish language, excel buttons, PDF, etc.
The script loads the data well (very fast) but when I paint them in the table it places them all in a single page, that is to say, it places well all the formatting that I give to the table with its pagination but all the records it places them in a single page.
I have searched the forums but I can't find anything to help me.

Translated with DeepL.com (free version)

Sounds like something is wrong with your code :-) So please post an example or at least your code as per the forum rules. Thanks.

rf1234

Have you considered using Editor? Great solution at a very low license price. I think I pay less than $50 annually. https://editor.datatables.net/purchase/index?currency=usd

Here is my code for a simple data table that is fully editable and lightning fast. It has 140,000 records currently. I guess my "patience" remarks (see below) are no longer needed

var rateEditor = new $.fn.dataTable.Editor( {
    ajax: {
        url: 'actions.php?action=tblRate'
    },
    table: "#tblRate",
    fields: [ {
            label: lang === 'de' ? 'Referenzzins:' : 'Reference Rate:',
            name:  "rate.ref_rate",
            type:  "select",
            options: refRateOptions
        }, {
            label: lang === 'de' ? 'Währung:' : 'Currency:',
            name:  "rate.currency",
            type:  "select",
            options: liborCurrencyOptions
        }, {
            label: lang === 'de' ? 'Zinsperiode:' : 'Rate Period:',
            name:  "rate.ref_rate_period",
            type:  "select",
            options: refRatePeriodOptions
        }, {
            label: lang === 'de' ? 'Zinsdatum:' : 'Rate Date:',
            name:  "rate.date",
            attr: {
                class: dateMask
            },
            type:  "datetime",
            def:   function () { return today},
            format: 'L',
            opts:  {
                showWeekNumber: true,
                yearRange: 40,
                momentLocale: momentLocale
            }
        }, {
            label: lang === 'de' ? 'Zins %:' : 'Rate %:',
            name:  "rate.rate"
        }
    ]        
} );

rateEditor
        .on( 'postSubmit', function ( e, json, data, action ) {
            if (json.error) {
                if ( json.error.indexOf('1062 Duplicate entry') >= 0 ) {
                   json.error = lang === 'de' ? 
                                "Tut uns Leid, dieser Kurs existiert schon!" : 
                                "Sorry, this rate already exists!";
                }
            }
        });

var rateTable = $('#tblRate').DataTable( {
    dom: "Bfrltip",
    serverSide: true,
    ajax: {
        url: 'actions.php?action=tblRate',
        type: 'POST',
        data: function ( d ) {            
            d.fullTextSearchString = d.search.value;
        }
    },
    language: {
        "searchPlaceholder": lang === 'de' ? 
                    "z.B. usd libor overnight 17.06.2019" :
                    "e.g. usd libor overnight 17/06/2019",
        "search": lang === 'de' ? 
                    "Suche (Geduld bitte: 100k+ Datensätze!):" :
                    "Search (Patience please: 100k+ records!):"
    },
    pageLength: 20,
    lengthMenu: [5, 10, 20, 50, 100, 200, 500],
    columns: [
        {   data: "rate.currency" },
        {   data: "rate.ref_rate" },
        {   data: "rate.ref_rate_period" },
        {   data: "rate.date" },
        {   data: "rate.rate" },
        {   data: "rate.update_time" }
    ],
    columnDefs: [
        // targets may be classes
        {targets: [0, 1, 2, 3, 4, 5], searchable: false}
    ],
    order: [[ 3, 'desc' ]],
    select: {
        style: 'single'
    },            
    buttons: [
        {   extend: "create", editor: rateEditor, className: "lgfAdminOnly" },
        {   extend: "edit",   editor: rateEditor, className: "lgfAdminOnly" },
        {   extend: "remove", editor: rateEditor, className: "lgfAdminOnly" },
        {   extend: "colvis", columns: ':not(.never)' }
    ]
} );

rateTable
    .on ('init', function () {
        if ( ! lgfAdmin ) {
            rateTable.buttons('.lgfAdminOnly').remove();
        }
        $('*[type="search"][class="form-control input-sm"]')
                .addClass('input-lg')
                .css({ 'width': '400px', 'display': 'inline-block' });
        $('div.dataTables_filter').css({ 'margin-top': '1em' });
    });

colin

I see you're using Editor in your example, but our accounts aren't showing that you have a license - it just reports that your trial expired several years ago. Is the license registered to another email address? Please can let us know so we can update our records and provide support.

Colin

rf1234

I never had a trial, Colin. I purchased the license right away and for some time I have used your subscription. JoseAlberto doesn't seem to be using Editor at all.

colin

Ah sorry all, I meant the license for JoseAlberto, but yep, that OP doesn't reference Editor.

Colin