Answers

• rf1234 Posts: 3,000Questions: 87Answers: 421

  March 20 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.
• rf1234 Posts: 3,000Questions: 87Answers: 421

  March 20 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

  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 Posts: 15,240Questions: 1Answers: 2,599

  March 20

  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 Posts: 3,000Questions: 87Answers: 421

  March 20 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.

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

  March 20

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

  Colin