Datatable carga 100.000 registros en una sola página

Datatable carga 100.000 registros en una sola página

JoseAlbertoJoseAlberto Posts: 1Questions: 1Answers: 0

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.

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421
    edited March 20

    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.

  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421
    Answer ✓

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

    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' });
        });
    
  • colincolin Posts: 15,240Questions: 1Answers: 2,599

    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

  • rf1234rf1234 Posts: 2,970Questions: 87Answers: 421
    edited March 20

    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.

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

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

    Colin

Sign In or Register to comment.