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

nextart

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?

kthorngren

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

nextart

[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

nextart

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);
});

nextart

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

scrollX: true,

bye Maurizio

nextart

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.