0

Good morning I'm having this little issue using Datatables inside a bootstrap modal that should be showing a SQLServer query (just a select), its required to be able to print as an Excel file or other formats but I'm receiving the error in console "DataTables Cannot read property 'aDataSort' of undefined" And in front of that error it shows "jquery-3.5.1.js:4055" so I assume this should be the issue.

Since I need to export results as a format, I found this https://datatables.net/extensions/buttons/examples/initialisation/export.html

And I was reading through different websites and also here and looks like the problem is how dependencies are defined but I've tried different ways and its not working so I'm not sure what could be the problem...

These are my dependencies:

        <meta charset="utf-8">
        <meta name="viewport" content="width-device-width, initial-scale=1.0">
        <link rel="stylesheet" href="../../bootstrap-4.5.0-dist/css/bootstrap.css" crossorigin="anonymous">
        <script src="https://code.jquery.com/jquery-3.5.1.js" integrity="sha256-QWo7LDvxbWT2tbbQ97B53yJnYU3WhH/C8ycbRAkjPDc=" crossorigin="anonymous"></script>
        <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.13.0/css/all.css" integrity="sha384-Bfad6CLCknfcloXFOyFnlgtENryhrpZCe29RTifKEixXQZ38WheV+i/6YWSzkz3V" crossorigin="anonymous">
        <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js" integrity="sha384-OgVRvuATP1z7JjHLkuOU7Xw704+h835Lr+6QL9UvYjZE3Ipu6Tp75j7Bh/kR0JKI" crossorigin="anonymous"></script>
        <script src="js/functions.js"></script>
        <script src="https://unpkg.com/sweetalert/dist/sweetalert.min.js"></script>
        <script src="js/datatables.js"></script>
        <script src="js/Buttons-1.6.2/js/buttons.bootstrap4.js"></script>
        <script src="js/DataTables-1.10.21/js/jquery.dataTables.js"></script>
        <script src="js/JSZip-2.5.0/jszip.js"></script>
        <script src="js/pdfmake-0.1.36/pdfmake.js"></script>
        <script src="js/pdfmake-0.1.36/vfs_fonts.js"></script>

This is the table inside the modal

 <table id="table_to_show"></table>

This is the function in the same file that should be showing the table.

<script type="text/javascript">
        $(document).ready(function() {
    $('#table_to_show').DataTable( {
        dom: 'Bfrtip',
        buttons: [
            'copy', 'csv', 'excel', 'pdf', 'print'
        ]
        
    } );
} );
</script>

And this is the table itself that's generated for the SELECT:

$table="";

if ($result) {
    $data = sqlsrv_has_rows($result);
    if ($data === true)  {
        while ($data = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)){

            $table=$table.'<tr>
            <td>'.$data['ID'].'</td>
            <td>'.$data['NAME'].'</td>
            <td>'.$data['LNAME'].'</td>
            <td>'.$data['number1'].'</td>
            <td>'.$data['number2'].'</td>
            <td>'.$data['MOBILE'].'</td>
            <td>'.$data['MRETIR'].'</td>
            </tr>';
        }
    }
}
    sqlsrv_close($conn);

    echo '<table class="table table-stripped">
    <thead>
    <th>Id</th>
    <th>Name</th>
    <th>Last Name</th>
    <th>Number 1</th>
    <th>Number 2</th>
    <th>Mobile #</th>
    <th>Retired?</th>
    </thead>
    <tbody>'.$table.'
    </tbody>';

That's kinda all I have... anyone can help me with this issue?, I've been trying to solve it for 2 days, maybe 3...

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dante
  • 3
  • 2

1 Answers1

0

Try using json_encode for your PHP output Here is some of my MYSQLI Sample

  $sqlSelect = "SELECT * FROM table";
  $result = mysqli_query($conn, $sqlSelect);
        $someArray = [];
  if (mysqli_num_rows($result) > 0) {

while ($row = mysqli_fetch_array($result)) {
     array_push($someArray, [
  'ID'   => $row['ID'],
  'NAME' => $row['NAME'],
   'LNAME'   => $row['LNAME'],
  'ETC' => $row['ETC']
]);
}
}
 
 $json = json_encode($someArray);
echo ($json);

Then The JQuery

 $.ajax({
            url: 'php_file.php',
            type: "GET",
            success: function(res){
                var obj = JSON.parse(res);
                console.log(obj);
                $('#table_to_show').DataTable({
                    dom: 'Bfrtip',
                    destroy: true,
                    processing: true,
                    "data"  : obj,  
                "columnDefs": [{
                "defaultContent": "-",
                "targets": "_all"
                    }], 
            buttons : [ 'copy', 'csv', 'excel', 'pdf', 'print' ],                   
                    "columns": [    
                        { "data": "ID" },
                        { "data": "NAME" },
                        { "data": "LNAME" },
                        { "data": "ETC" }
                    ]  
                 })

;
                    
                },
                error: function(xhr){
                    alert("Error Occured!", "error")
                }
            }); 
        
    
HTML

    <table id="table_to_show" class="display" style="width:100%">
        <thead>
            <tr>
                <th>ID</th>
                <th>NAME</th>
                 <th>LNAME</th>
                <th>ETC</th>   
            </tr>
        </thead>
    </table>

DataTable Button CDN

https://code.jquery.com/jquery-3.5.1.js
https://cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js
https://cdn.datatables.net/buttons/1.6.2/js/dataTables.buttons.min.js
https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js
https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js
https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js
https://cdn.datatables.net/buttons/1.6.2/js/buttons.html5.min.js
Temidayo Dtuzzy Omotayo
  • 159
  • 3
  • 12