dynamically creation of datatables (columns and data are differnt each query ) from Json

dynamically creation of datatables (columns and data are differnt each query ) from Json

nextartnextart Posts: 6Questions: 1Answers: 0

Hi, i use this code http://live.datatables.net/huyexejo/1/edit to create dynamically my datatables.
i use the function
crea_tabella_query("select* from DATABASE.TABLE;");

from a textarea i can write a new query to send to php file that extract the data in json format
but when i pass the data to the function i.e. CREA TABELLA_QUERY("ANOTHER SELECT FROM ANOTHER TABLE") i received the message

DataTables warning: table id=datatables_query - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3

how can i solve?

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794
    Answer ✓

    Have you followed the steps provided in the error link?
    http://datatables.net/tn/3

    If you are changing the number of columns then you need to use destroy() before reinitializing the Datatable.

    Kevin

  • nextartnextart Posts: 6Questions: 1Answers: 0

    [SOLVED]
    before i click the button to execute the query
    i need this:
    // verify if #datatables_query exist if Y delete it
    if ($.fn.DataTable.isDataTable( '#datatables_query' ) ) {
    var tableId = "#datatables_query";
    // clear first
    $('#datatables_query').DataTable().clear();
    $('#datatables_query').DataTable().destroy();
    //2nd empty html
    $('#datatables_query' + " tbody").empty();
    $('#datatables_query'+ " thead").empty();

    }
    then i create new $('#datatables_query') with new columns and new data

    thanks

  • nextartnextart Posts: 6Questions: 1Answers: 0

    just for the ages.... :-)

    1. btn to pass the sql query from textarea to function to create datatables

    $(document).on("click", "#esegui_qs", function () {
    var testo_qs = document.getElementById('campo_qs').value;

    console.log("text textarea : "+testo_qs);

    if ($.fn.DataTable.isDataTable( '#datatables_query' ) ) {
    var tableId = "#datatables_query";
    // clear first

    $('#datatables_query').DataTable().clear();
    $('#datatables_query').DataTable().destroy();
    //2nd empty html
    $('#datatables_query' + " tbody").empty();
    $('#datatables_query'+ " thead").empty();

    }

    crea_tabella_query(testo_qs);
    });

  • nextartnextart Posts: 6Questions: 1Answers: 0

    you can obtain more columns that you can see in the datatables so add this option:

    scrollX: true,

    bye Maurizio

  • nextartnextart Posts: 6Questions: 1Answers: 0
    edited April 2022

    just for the ages.... :-)

    1.
    //html datatables definition in your page

         <table id="datatables_query" name="datatables_query" class="table table-striped table- 
         no-bordered table-hover" width="100%" style="width:100%">
         </table>
    
    1. btn to pass the sql query from textarea to function to create datatables
    $(document).on("click", "#esegui_qs", function () {
          //retrieve the text in textarea i.e. "select * from [database_name.database_table]"
          var testo_qs = document.getElementById('campo_qs').value;
          
         //check if a datatables with id 'datatables_query' exist , if Y dlete table and all data
         if ($.fn.DataTable.isDataTable( '#datatables_query' ) ) {
              var tableId = "#datatables_query";
          // this sequence is very important.        
          // clear first
             $('#datatables_query').DataTable().clear();
         //destroy
             $('#datatables_query').DataTable().destroy();
         //and then empty html
             $('#datatables_query' + " tbody").empty();
             $('#datatables_query'+ " thead").empty();
          }
          //launch the function to execute ajax request and create the datatable
           crea_tabella_query(testo_qs);
    });
    
    1. function to execute ajax request and create the datatable
    function crea_tabella_query(label) {
        var columns = [];
        $.ajax({
     // put your json file here
         url :"assets/ajax/estrai_dati.php?testo_qs="+label,
          success: function (data) {
            data = JSON.parse(data);
            columnNames = Object.keys(data.data[0]);
            for (var i in columnNames) {
              columns.push({data: columnNames[i], 
                        title: capitalizeFirstLetter(columnNames[i])});
            }
            $('#datatables_query').DataTable( {
            dom: 'Blfrtip',
            buttons: ['copy', 'csv', 'excel', 'pdf', 'print'],
            ordering: false,
                select:true,
            data: data.data,
                columns: columns,
            language:
                {
                    searchPlaceholder: "Cerca nella tabella..",
                    emptyTable: "Non sono presenti dati da caricare",
                    info: "Visualizzati da _START_ a _END_ di _TOTAL_ record",
                    infoEmpty:"Visualizazzati 0 di 0 di 0 record",
                    infoFiltered:"(filtrati da _MAX_ record totali)",
                    infoPostFix:"",
                    lengthMenu: "Mostra _MENU_ righe",
                    loadingRecords: "Caricamento...",
                    processing:     "Analizzando i dati...",
                    search: "_INPUT_",
                    zeroRecords: "Nessun dato presente per i parametri di ricerca.",
                    paginate: {
                                first:      "Prima",
                                last:       "Ultima",
                                next:       "Successiva",
                                previous:   "Precedente"
                              },
                }
            } );
          }
        });
    }
       
    function capitalizeFirstLetter(string) {
        return string.charAt(0).toUpperCase() + string.slice(1);
    }
    

    hope it's useful for someone..
    bye
    Maurizio

    Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

Sign In or Register to comment.