Datatable plugin to autorefresh datas on a table

Nico90 · November 2017

Hi there,

I have this table:

<?php  
 $connect = mysqli_connect("dbexample.com", "dboexample", "password", "dbexample");  
 $query ="SELECT * FROM te_lb_load_board, te_lb_load_board_cstm WHERE te_lb_load_board.id = te_lb_load_board_cstm.id_c AND te_lb_load_board_cstm.load_status_c LIKE '%open%' ORDER BY shipping_date_c DESC, shipping_time_c ASC";  
 $result = mysqli_query($connect, $query);  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Load Board</title>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>  
           <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
           <script src="https://cdn.datatables.net/plug-ins/1.10.15/api/fnReloadAjax.js"></script>       
           <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
           <script>  
 $(document).ready(function(){  
      $('#employee_data').DataTable();  
 });  
 setInterval(function () {
      table.fnReloadAjax();
}, 3000);
 </script>  
      </head>  
      <body>  
           <br /><br />  
           <div class="container">  
                <h3 align="center">Load Board</h3>  
                <br />  
                <div class="table-responsive">  
                     <table id="employee_data" class="table table-striped table-bordered">  
                          <thead>  
                               <tr>    
                                    <td>Date</td>  
                                    <td>Time</td> 
                                    <td>Zip</td>  
                                    <td>City</td>  
                                    <td>Country</td>  
                                    <td>Date</td>
                                    <td>Time</td> 
                                    <td>Zip</td>
                                    <td>City</td>
                                    <td>Country</td>
                                    <td>Description</td>
                               </tr>  
                          </thead>  
                          <?php  
                          while($row = mysqli_fetch_array($result))  
                          {  
                               echo '  
                               <tr>    
                                    <td>'.$row['shipping_date_c'].'</td>  
                                    <td>'.$row['shipping_time_c'].'</td>
                                    <td>'.$row["billing_address_postalcode"].'</td>  
                                    <td>'.$row["billing_address_city"].'</td>  
                                    <td>'.$row["billing_address_country"].'</td>
                                    <td>'.$row['arrival_date_c'].'</td>
                                    <td>'.$row['arrival_time_c'].'</td>
                                    <td>'.$row["shipping_address_postalcode"].'</td> 
                                    <td>'.$row["shipping_address_city"].'</td> 
                                    <td>'.$row["shipping_address_country"].'</td> 
                                    <td>'.$row["description"].'</td>  
                               </tr>  
                               ';  
                          }  
                          ?>  
                     </table>  
                </div>  
           </div>  
      </body>  
 
 </html>

I want that the new data I insert on mysql get automatically uploaded after 3 seconds without refreshing the page. I am running this table on live server. It is not working.

Could someone please help me?

Thanks,
Nico

kthorngren · November 2017

I see a couple issues...

You have this:

 $(document).ready(function(){ 
      $('#employee_data').DataTable(); 
 }); 
 setInterval(function () {
      table.fnReloadAjax();
}, 3000);

You aren't assigning the variable table to the Datatables API so table.fnReloadAjax(); isn't going to work. You could do something like this:

 setInterval(function () {
      var table = $('#employee_data').DataTable();
      table.fnReloadAjax();
}, 3000);

However fnReloadAjax() is deprecated when using DT 1.10 in favor of the built-in APIs. The instructions in the plugin state this:

 * DataTables 1.10 provides the `dt-api ajax.url()` and `dt-api ajax.reload()`
 * methods, built-in, to give the same functionality as this plug-in. As such
 * this method is marked deprecated, but is available for use with legacy
 * version of DataTables. Please use the new API if you are used DataTables 1.10
 * or newer.

The biggest problem is that fnReloadAjax() nor ajax.reload() are going to work in your case because you need to use ajax within your Datatables init code for either of these to work. Instead of using this loop, while($row = mysqli_fetch_array($result)), to build the initial table you would define the ajax option and let Datatables retrieve the data and populate the table.

Kevin

Nico90 · November 2017

Hi Kevin,

Thanks a lot for your help.

I found some code here: https://wiki.khairulazam.net/index.php?title=Simple_Jquery_Ajax_Auto_Refreshing_DIV

I create an index.php file with this code:

<title>Glastopf Observation Center</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>

<script type="text/javascript">// <![CDATA[
$(document).ready(function() {
$.ajaxSetup({ cache: false }); // This part addresses an IE bug. without it, IE will only load the first number and will never refresh
setInterval(function() {
$('#results').load('table.php'); // table.php is where I have my table
}, 3000); // the "3000" here refers to the time to refresh the div. it is in milliseconds.
});
// ]]></script>

<div id="results">Loading data ...</div>

It is working, I got the autrefresh of my data on the table but I have every 3 seconds this css problem:

This is how it looks as usual:
https://imgur.com/b8bRauP

This is how it looks after 3 seconds:
https://imgur.com/iCMxB5H

Could be a solution to put the php files on my server as I suggested on this Topic?:
https://datatables.net/forums/discussion/45330/html-arrows-on-datatables-not-displayed#latest

I put the css links into the index.php file I mentioned before but I still have the same css problems:

<title>Glastopf Observation Center</title>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>  
           <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>
           <script src="https://cdn.datatables.net/plug-ins/1.10.15/api/fnReloadAjax.js"></script>       
           <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />

<script type="text/javascript">// <![CDATA[
$(document).ready(function() {
$.ajaxSetup({ cache: false }); // This part addresses an IE bug. without it, IE will only load the first number and will never refresh
setInterval(function() {
$('#results').load('table.php');
}, 3000); // the "3000" here refers to the time to refresh the div. it is in milliseconds.
});
// ]]></script>

<div id="results">Loading data ...</div>

Datatable plugin to autorefresh datas on a table

Datatable plugin to autorefresh datas on a table

Answers

Howdy, Stranger!

Categories

DataTables

Datatable plugin to autorefresh datas on a table

Datatable plugin to autorefresh datas on a table

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables