Question

96

jquery DataTable search and sort is not working

rated 0 times [ 103] [ 7] / answers: 1 / hits: 17471 / 10 Years ago, fri, july 4, 2014, 12:00:00

I have an issues with datatable sorting and filter basically all the JS function not working.
I've already include the JS files.
some details:
I'm connecting to the database to retrieve data in json.
The php file:('district.php')

    <?php  

  include_once('db.php');

  $db = mysql_select_db('mor_app',$con);  

  if(!$db){   die('Database selection failed '.mysql_error());  }   

  $sql = 'SELECT *FROM users';  

  $result = mysql_query($sql,$con);   

  $data = array();

  while($row = mysql_fetch_array($result)){

  $row_data = array(  

  'id' => $row['id'],    

  'date' => $row['date'],   

  'username' => $row['username'], 

  'Q1' => $row['Q1']    );  

  array_push($data, $row_data);  } 

  echo json_encode($data); 

  ?>

the html file:

<!DOCTYPE html>

<html>

    <head>

    <!-- DataTables CSS -->

    <link rel=stylesheet type=text/css href=//cdn.datatables.net/1.10.0/css/jquery.dataTables.css>

    <!-- DataTables -->

    <script type=text/javascript charset=utf8 src=//cdn.datatables.net/1.10.0/js/jquery.dataTables.js></script>

 <script src=js/jquery-1.9.1.min.js></script>

    </head>

    <body>

 <table id=table_id class=display>  

 <caption>Try</caption>  

 <thead> <tr>  

 <th>ID</th>  

 <th>Date</th>  

 <th>user name</th>   

 <th>Q1</th> </tr>

 </thead>   

 <tbody id=tablebody> 

 </tbody>

 </table>





       <script>



        $(function() {



  var url = 'district.php';    

  $.getJSON(url, function(data) {     

  $.each(data, function(index, data) {     

  $('#tablebody').append('<tr>');      

  $('#tablebody').append('<td>'+data.id+'</td>');    

  $('#tablebody').append('<td>'+data.date+'</td>');  

  $('#tablebody').append('<td>'+data.username+'</td>');   

  $('#tablebody').append('<td>'+data.Q1+'</td>');    

  $('#tablebody').append('</tr>');    

  });   

  });



 });



      $('#table_id').DataTable();  





    </script>





  </body>

    </html>

now I can see the table but I just can't sorting or searching.
if I write something in the search box the table is empty. the same happen with sorting.
it say: No data available in table.
I thought it's happening because I'm retrieving data. any suggestion?
Thank a lot, Mor

Answers

Only authorized users can answer the question. Please sign in first, or register a free account.

kourtney

Add To Favorites

Follow

Total Points: 368

Total Questions: 103

Total Answers: 85

Location: Bonaire

Member since Sat, May 1, 2021

3 Years ago

kourtney questions

1 Problem with typescript while making request to SWR

Sun, Oct 4, 20, 00:00, 4 Years ago

1 JS DateTimePicker onChange event not working

Tue, Oct 29, 19, 00:00, 5 Years ago

1 NodeJS and Sharp, BMP to PNG Error: Input file contains unsupported image format

Fri, May 24, 19, 00:00, 5 Years ago

1 React Slick slider map not showing slides?

Thu, Apr 4, 19, 00:00, 5 Years ago

1 Cypress - Add custom header for all XHR requests

Fri, Mar 1, 19, 00:00, 5 Years ago

View All

answered 10 Years ago tatianah · Accepted Answer

The problem I found is, you have to use DataTable's fnAddData function to add data in your HTML table. Otherwise, it is not adding your data which is being fetched on the fly. I tested with hard coded data in HTML which works fine, but whenever I tried to get data from other source through AJAX, it shows no data found or something.

I found the solution in another stack overflow question .

I used AJAX for your solution, but the main catch is to use fnAddData.

Here is the solution (took help from above link) which works fine:

<script>

$('#table_id').DataTable();

                $(document).ready(function() {



                    $.ajax({

                            type: POST,

                            url: district.php,

                            data: {data : },

                            cache: false,

                            success: function(result){

                                  data = JSON.parse(result);    

                                  $.each(data, function(index, data) {     

                                  //!!!--Here is the main catch------>fnAddData

                                  $('#table_id').dataTable().fnAddData( [

                                                                          data.id,

                                                                          data.date,

                                                                          data.username,

                                                                          data.Q1 ]

                                                                        );      



                                });



                            }



                    });



                });



    </script>

IMPORTANT: My answer considers that your PHP code is correctly returning data. I made some dummy array as suggested in the comments. My php file returns like this:

  $data = array(); 

  $row_data = array( 'id' => 1, 'date' => 2014-01-01, 'username' => mou, 'Q1' => muhahaha ); 

  array_push($data, $row_data);   



  $row_data = array( 'id' => 9, 'date' => 2013-02-02, 'username' => sadi, 'Q1' => hii ); 

  array_push($data, $row_data);   



  echo json_encode($data);