Answers

• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  March 2020

  This example shows how to apply Datatables to a DOM based table.

  Kevin
• antoniocib Posts: 277Questions: 62Answers: 1

  March 2020

  Maybe I was wrong to express myself, those two html tables are connected to the db of the table below, how can I make those tables datatables too?

  I leave an example:
• antoniocib Posts: 277Questions: 62Answers: 1

  March 2020

  
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  March 2020

  What do you want to do with the tables? It looks like they are already Datatables. Are you trying to insert them into the main table?

  Kevin
• antoniocib Posts: 277Questions: 62Answers: 1

  March 2020

  as I showed you in the photo I would like to create those two tables which are now only in html, with the datatables editor, because then I have to add the sum in the footer and a control that when a data is inserted in the column in the main table "Autista Linea "the data in the two tables above must change
• antoniocib Posts: 277Questions: 62Answers: 1

  March 2020

  In a nutshell, those two tables are a sort of only visual control panel, to see the sum of the "Epal, Ind" columns grouped by the "scarico" column.

  The query that those tables use is as follows:

  SELECT scarico, sum(epal + perd), sum(ind) From scrivania1 where dlinea = '' and scarico !='' GROUP BY scarico

  And

  SELECT scarico_m, sum(epal + perd), sum(ind) From scrivania1 where dlinea = '' and scarico_m !='' GROUP BY scarico_m
• antoniocib Posts: 277Questions: 62Answers: 1

  March 2020 edited March 2020

  (function($){
  $(document).ready(function() {
  $.fn.dataTable.moment('DD/MM/YYYY');
  var editor = new $.fn.dataTable.Editor( {
  ajax: 'php/table.scrivania1.php',
  table: '#scrivania1',

      fields: [
          {
              "label": "DDT/ORDINE n°:",
              "name": "ddt"
          },
          {
              "label": "Cliente:",
              "name": "cliente"
          },
          {
              "label": "Magazzino Carico:",
              "name": "carico"
          },
          {
              "label": "Destinazione:",
              "name": "destinazione"
          },
          {
              "label": "PR:",
              "name": "pr"
          },
          {
              "label": "Magazzino Scarico:",
              "name": "scarico"
          },
          {
              "label": "Scarico M:",
              "name": "scarico_m"
          },
          {
              "label": "Data di scarico:",
              "name": "data_di_scarico",
              "type": "datetime",
              "format": "DD/MM/YYYY"
          },
          {
              "label": "EPAL:",
              "name": "epal"
          },
          {
              "label": "IND:",
              "name": "ind"
          },
          {
              "label": "PERD:",
              "name": "perd"
          },
          {
              "label": "Dati di scarico:",
              "name": "dati_di_scarico"
          },
          {
              "label": "Autista Ritiro:",
              "name": "drit"
          },
          {
              "label": "Targa Ritiro:",
              "name": "trit"
          },
          {
              "label": "Autista Linea:",
              "name": "dlinea"
          },
          {
              "label": "Targa Linea:",
              "name": "tlinea"
          },
          {
              "label": "Note:",
              "name": "note"
          }
  /*      {
                      label:     "Conferma:",
                      name:      "active",
                      type:      "checkbox",
                      separator: "|",
                      options:   [
                          { label: '', value: 1 }
                      ]
                  }*/
  

  ],

  i18n: {
  create: {
  button: "Nuova Spedizione",
  title: "Inserisci una nuova spedizione",
  submit: "Conferma",
  },
  edit: {
  button: "Modifica Spedizione",
  title: "Modifica Spedizione",
  submit: "Conferma"
  },
  remove: {
  button: "Elimina Spedizione",
  title: "Elimina Spedizione",
  submit: "Elimina",
  confirm: {
  _: "Sei sicuro di voler eliminare %d riga?",
  1: "Sei sicuro di voler eliminare questa riga?"
  }
  },
  error: {
  system: "Errore di sistema, contattare lo sviluppatore"
  },
  datetime: {
  previous: 'Precedente',
  next: 'Successivo',
  months: [ 'Gennaio', 'Febbraio', 'Marzo', 'Aprile', 'Maggio', 'Giugno', 'Luglio', 'Agosto', 'Settembre', 'Ottobre', 'Novembre', 'Dicembre' ],
  weekdays: [ 'Dom', 'Lun', 'Mar', 'Mer', 'Gio', 'Ven', 'Sab' ]
  }

  }
  } );

  var table = $('#scrivania1').DataTable( {
  //  serverSide: 'true',
  paging: false,
      dom: 'PBfrtip',
  //  dom: 'Pfrtip',
      ajax: 'php/table.scrivania1.php',
      order: [5, 'desc'],
  
      columns: [
          {
              "data": "ddt"
          },
          {
              "data": "cliente"
          },
          {
              "data": "carico"
          },
          {
              "data": "destinazione"
          },
          {
              "data": "pr"
          },
      {
  
              "data": "scarico",
                  visible: false,
          },
          {
  
              data: null, render: function ( data, type, row ) {
             return data.scarico+' '+data.scarico_m;
           }},
      {
  
              "data": "scarico_m",
              visible: false,
          },
          {
              "data": "data_di_scarico"
          },
          {
              "data": "epal"
          },
          {
              "data": "ind"
          },
          {
              "data": "perd"
          },
          {
              "data": "dati_di_scarico"
          },
          {
              "data": "drit"
          },
          {
              "data": "trit"
          },
          {
              "data": "dlinea"
          },
          {
              "data": "tlinea"
          },
          {
              "data": "note"
          },
  
          {
              "data": "active",
                  render: function ( data, type, row ) {
                          if ( type === 'display' ) {
                                  return '<input type="checkbox" class="editor-active" >';
                          }
                          return data;
                  },
              className: "dt-body-center",
  
          }
  
  ],
  

  searchPanes:{
  columns:[5,7]

  },

  select: {
          style: 'os',
  //      selector: 'td:first-child',
          selector: 'td:not(:last-child)' // no row selection on last column
  },
  //  ],
  

  // select: true,
  // lengthChange: true,

  lengthMenu: [
  [10, 20, 25, 50, -1],
  [10 , 20, 25, 50, 'Tutte']
  ],
  buttons: [

          { extend: 'create', editor: editor, text: 'Nuova Spedizione'},
          { extend: 'edit',   editor: editor, text: 'Modifica Spedizione' },
          { extend: 'remove', editor: editor, text: 'Elimina Spedizione' },
          { extend: 'excelHtml5', exportOptions: {
  

  columns: [ 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10] ,
  modifier: {
  page: 'current'
  }
  }
  },
  {extend: 'pageLength', editor: editor, text:'Mostra righe'}

      ],
  
      rowCallback: function ( row, data ) {                                                                                                                                           //SCRIPT PER RITORNARE LE RIGHE EVIDENZIATE
              // Set the checked state of the checkbox in the table
              $('input.editor-active', row).prop( 'checked', data.active == 1 );
          if ( data.active == 1 ) {
                      $(row).addClass('myHighlight')
          }
       else {
              $(row).removeClass('myHighlight')
      }
      },
  
      footerCallback: function ( row, data, start, end, display ) {                                                                                                   // SOMMA PER PEDANE
              var api = this.api(), data;
  
              var intVal = function ( i ) {
                      return typeof i === 'string' ?
                              i.replace(/[\$,]/g, '')*1 :
                              typeof i === 'number' ?
                                      i : 0;
                                  };
  
                      total = api
                      .column( 9 )
                      .data()
                      .reduce( function (a, b) {
                              return intVal(a) + intVal(b);
                      }, 0 );
  
              // Total over this page
              var rows = api.rows( {page: 'current', selected: true} ).indexes();
              pageTotal = api
                      .cells( rows, 9 )
                      .data()
                      .reduce( function (a, b) {
                              return intVal(a) + intVal(b);
                      }, 0 );
  
              // Update footer
              $( api.column( 9  ).footer() ).html(
                  ''+pageTotal +' ('+ total +'TOT)'
              );
  
              total = api
              .column( 10 )
              .data()
              .reduce( function (a, b) {
                  return intVal(a) + intVal(b);
              }, 0 );
  
  
              var rows = api.rows( {page: 'current', selected: true} ).indexes();
                  pageTotal = api
                      .cells( rows, 10 )
                      .data()
                      .reduce( function (a, b) {
                              return intVal(a) + intVal(b);
                      }, 0 );
  
              // Update footer
              $( api.column( 10 ).footer() ).html(
                      pageTotal +' ('+ total +'TOT)'
              );
  
              total = api
              .column( 11 )
              .data()
              .reduce( function (a, b) {
              return intVal(a) + intVal(b);
              }, 0 );
  
  
              var rows = api.rows( {page: 'current', selected: true} ).indexes();
              pageTotal = api
                      .cells( rows, 11 )
                      .data()
                      .reduce( function (a, b) {
                              return intVal(a) + intVal(b);
                      }, 0 );
  
              // Update footer
              $( api.column( 11 ).footer() ).html(
                      pageTotal +' ('+ total +'TOT)'
              );
  
  
      },
  
      language: {
      select: {
              rows: {
                  _: "%d righe selezionate",
                  0: "",
                  1: "1 riga selezionata"
              }
          },
      processing:     "Processando...",
      search:         "Cerca :",
      lengthMenu:     "Mostra _MENU_ spedizioni",
      info:           "Mostra _START_ a _END_ di _TOTAL_ spedizioni",
      infoEmpty:      "Mostra 0 su 0 di 0 spedizioni",
      infoFiltered:   "(Filtrato da _MAX_ totale spedizioni )",
      infoPostFix:    "",
      loadingRecords: "Caricamento in corso...",
      zeroRecords:    "Nessuna spedizione",
      emptyTable:     "Nessuna spedizione disponibile",
      paginate: {
              first:      "Prima",
              previous:   "Precedente",
              next:       "Successiva",
              last:       "Ultima"
      },
      aria: {
              sortAscending:  ": Attivare per ordinare la colonna in modo crescente",
              sortDescending: ": Attivare per ordinare la colonna in modo decrescente"
      }
  

  }

  } );
  
  $('#scrivania1').on( 'click', 'tbody td', function (e) {                 //Editor per RIGHE 11 A 15
          if ( $(this).index() > 10 && $(this).index() < 16 ) {
                  editor.bubble( this );
          }
  });
  
  $('#scrivania1').on( 'change', 'input.editor-active', function () {  //SCRIPT PER CHECKBOX RITIRO
  
           editor
                   .edit( $(this).closest('tr'), false )
                   .set( 'active', $(this).prop( 'checked' ) ? 1 : 0 )
                   .submit();
  
  
   } );
  
           $('#scrivania1 tfoot th').each( function () {                                                                                                                                                                      //SCRIPT PER RICERCA TFOOT
           var title = $(this).text();                                                                                                                                                                                                            //SCRIPT PER RICERCA TFOOT
           $(this).html( '<input type="text"  style="text-align:center ; width:100%" placeholder="'+title+'" />' );                                                   //SCRIPT PER RICERCA TFOOT
   } );
  
   // DataTable
   var table = $('#scrivania1').DataTable();
  
   // Apply the search
   table.columns().every( function () {
           var that = this;
  
           $( 'input', this.footer() ).on( 'keyup change clear', function () {
                   if ( that.search() !== this.value ) {
                           that
                                   .search( this.value )
                                   .draw();
                   }
           } );
  
           table.on('select deselect', function () {
  

  table.draw(false);
  });
  } );

   var table = $('#scrivania1').DataTable( {
      //  serverSide: 'true',
          dom: 'Bfrtip',
      //  dom: 'Pfrtip',
          ajax: 'php/table.scrivania1.php',
  
   columns:[
      {
          "data": "scarico"
      },
      {
          "data": "epal"
      },
      {
          "data": "ind"
      }
   ],
  

  } );

  var table = $('#scrivania1').DataTable( {
  // serverSide: 'true',
  dom: 'Bfrtip',
  // dom: 'Pfrtip',
  ajax: 'php/table.scrivania1.php',

  columns:[
  {
  "data": "scarico_m"
  },
  {
  "data": "epal"
  },
  {
  "data": "ind"
  }
  ],

  } );

  } );

  }(jQuery));

  I try this way but return me error tn/3 and idk how to remove it i really dont understand..
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  March 2020 edited March 2020

  The Cannot reinitialise DataTable error you are getting is due to initializing the same table multiple times. You are using $('#scrivania1').DataTable( { ... } ); for 3 different tables. scrivania1 is the 'id' of your main table. What are the id's of the other two tables? You need to change the selector ( '#scrivania1' ) to the id of the table you want initialized.

  Kevin
• antoniocib Posts: 277Questions: 62Answers: 1

  March 2020

  Kevin but i cant change the selector the 3 tables must pick the same data in the same db
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  March 2020

  The id selector has nothing to do with the data retrieved from the server. The id is a unique identifier for HTML elements like table. For your main table you have something like <table id="scrivania1" ....></table>. For your other two tables assign unique id's to them, for example, <table id="scrivania2" ....></table> and use those to assign the Datatable instance, for example $('#scrivania2').DataTable( { ... } );.

  Read more about the id attribute here.

  Kevin
• antoniocib Posts: 277Questions: 62Answers: 1

  March 2020

  i think "#scrivania1" was for db..ehm sorry man, just try ur way
• antoniocib Posts: 277Questions: 62Answers: 1

  March 2020

  @kthorngren yeees work, now how do this query in the table?

  SELECT scarico, sum(epal + perd), sum(ind) From scrivania1 where dlinea = '' and scarico !='' GROUP BY scarico
• antoniocib Posts: 277Questions: 62Answers: 1

  March 2020

  Code table:

  $('#scrivania2').DataTable( {
  // serverSide: 'true',
  // dom: 'Bfrtip',
  // dom: 'Pfrtip',
  ajax: 'php/table.scrivania1.php',
  columns:[
  {
  "data": "scarico"
  },
  {
  "data": "epal"
  },
  {
  "data": "ind"
  }
  ],
  } );