how do display database data in datatables dynamically

vaishnavkoka

I want to display data in my datatable of webpage without reloading the webpage or click any button for the retrieval purpose, but i should be able to get more data using the pagination.
for example: https://datatables.net/examples/server_side/defer_loading.html
I need something of these kind but here the data is static, and even the ajax seems to pretty static in these example, i would like to know how would i achieve this task.
Thanks
Koka

kthorngren

The easy way is to use ajax.reload() to reload the data into the Datatable. Might not be the most efficient though. I have a Datatable that displays the contents of log messages that are pushed into a DB table. I perform the initial table load and get the highest unique ID. Then periodically query the DB for higher ID's and use rows.add() to add them to the Datatable.

Kevin

vaishnavkoka

https://datatables.net/examples/server_side/defer_loading.html

1.javascript part

line no: 5

"ajax": "scripts/server_processing.php",
ajax represents the location of the php file right ?

line no:6
"deferLoading": 57
how do i make this dynamic as there can be any number of records in the database

1. html part

initially you have added some records in the datatable and these would increase with every click of pagination button.

but i completely want to display data which is retrieved from the database( every single record) and hence would like to make it dynamic retrieval.

css part
its clear to me

server-side script part
line no :36
why you have separately written the format for start_date?
well in my case i have to dates a) start date b)end date
how should i write a array for dates part?

line no:53
though i have written the correct details in my program i still didnt get the results on my webpage

line no:66
how do i write a code for ssp.class.php ?
can you should be your code ?

line no:68
how do i make use of json_encode?
is there any separate code written for json for your datatable ?

line no:69
i have used the post method in my php program and changed to get method still it didnt work

can you please help to solve my issue ? and how do i write a ajax code for my issue as you have mentioned in your answer

Thanks
Koka

MSLtd

Hi @vaishnavkoka - I don't think the example you have referenced is correct for what you're trying to achieve (correct me if I'm wrong). What exactly is it you're trying to do with your data tables?

Here's some links I think could help you with this:
Paging//This is what enables the pagination control
dom//This attribute is where you put the option to include pagination in the table
(Note most importantly the relationship between the 'p' in the dom string, and pagination)

vaishnavkoka

@MSLtd ,

I have used the ajax code from here :
https://datatables.net/examples/server_side/post.html

But if the number of records of my datatable are less than 10 i need to reload the page to fetch the records dynamically from the database and moreover if the new records are added to the database(or the row values get changed in the database) , it doesnt shows up in my datatable until i reload the page.
can you help me out with there ?
Here is my code:
$(document).ready(function() { var t= $('#example').DataTable( { "lengthMenu": [[4, 8, 12, -1], [4, 8, 12, "All"]], autoWidth:true, paging: true, "pagingType": "full_numbers", "processing": true, serverSide: true, "ajax":"serverSide.php", } ); } );

MSLtd

@vaishnavkoka , are the new records being added at the same page and are you using the editor to submit data?
if that is the case I'd recommend putting this:

//(When information has successfully been submitted to the database) 
editor.on( 'submitSuccess', function ( e, type ) {
//Completely reload the table from the data source - with new data
t.ajax.reload();
} );

In your $(document).ready(function(){/*Your code here + new function })

When you've done that it should look something like this:

$(document).ready(function() { 
        var t= $('#example').DataTable( { 
                "lengthMenu": [[4, 8, 12, -1], [4, 8, 12, "All"]], autoWidth:true, paging: true, 
                "pagingType": "full_numbers", "processing": true, serverSide: true, 
                "ajax":"serverSide.php", 
                //I assume you're declaring your columns here?
        } ); 
        //(When information has successfully been submitted to the database) 
        editor.on( 'submitSuccess', function ( e, type ) {
                //Completely reload the table from the data source - with new data
                t.ajax.reload();
        } );
} );

vaishnavkoka

I am not declaring any column in that script and when i directly used your code i.e,
//(When information has successfully been submitted to the database) editor.on( 'submitSuccess', function ( e, type ) { //Completely reload the table from the data source - with new data t.ajax.reload(); } );
I didnt get any output.
My datatabase table are :
http://prntscr.com/k5j4ol
My datatable is :
http://prntscr.com/k5j52t

allan

Can you use the debugger to give us a trace of the table's state if you aren't able to link to a test case.

Thanks,
Allan

vaishnavkoka

Here is the link :
https://debug.datatables.net/ucopob

Thanks,
Koka

allan

Hi Koka,

The debug trace shows that the table is populated with 9 rows. But your image above shows zero rows. That doesn't appear to equate with the debugger results.

I think I'd need a link to a test case showing the issue.

Allan

vaishnavkoka

@MSltd,
I am not using editor to submit data, i am using php to insert data in my mysql database and i am using ajax code as you have seen earlier to retrieve the data from database and display it in datatable.

@allan,
Here is the new report :
https://debug.datatables.net/oyigef
I am surprised to see the report.

script i have used:

$(document).ready(function() {
 var t= $('#example').DataTable( {
        "lengthMenu": [[4, 8, 12, -1], [4, 8, 12, "All"]],  
         autoWidth:true,  
         paging: true, 
         "pagingType": "full_numbers",
         "processing": true,
          serverSide: true,
          "ajax":"serverSide.php",
    columnDefs: [
      {
        targets: [1,2], 
        render: function (data, type, row, meta) {
          if (type === 'display') {
            var label = 'label-success';
            if (data === 'failed') {
              label = 'label-danger';
            } else if (data === 'running') {
              label = 'label-success';
            }
            else if (data === 'halted') {
              label = 'label-warning';
            }
            return '<span class="label ' + label + '">' + data + '</span>';
          }
          return data;
        }
      }
    ]
    } );
    //(When information has successfully been submitted to the database)
        
        //Completely reload the table from the data source - with new data
            t.ajax.reload();
        
    } );

Serverside code:

<?php
 
/*
 * DataTables example server-side processing script.
 *
 * Please note that this script is intentionally extremely simply to show how
 * server-side processing can be implemented, and probably shouldn't be used as
 * the basis for a large complex system. It is suitable for simple use cases as
 * for learning.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * Easy set variables
 */
 
// DB table to use
$table = 'networkstatus';
 
// Table's primary key
$primaryKey = 'ipaddress';
 
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
    array( 'db' => 'ipaddress', 'dt' => 0 ),
    array( 'db' => 'pingstatus', 'dt' => 1 ),
    array( 'db' => 'ftpstatus', 'dt' => 2 ),
    array(
        'db'        => 'date',
        'dt'        => 3,
        'formatter' => function( $d, $row ) {
            return date( 'Y-m-j', strtotime($d));
        }
    )
    
);
 
// SQL server connection information
$sql_details = array(
    'user' => 'root',
    'pass' => '',
    'db'   => 'deg',
    'host' => 'localhost'
);
 
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */
 
require( 'ssp.class.php' );
 
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

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

allan

It is currently showing that you have a display length of 4, and 4 records (out of 21) are being shown:

{
    "draw": 6,
    "recordsTotal": 21,
    "recordsFiltered": 21,
    "data": [
        ["172.168.0.1", "---------", "-------", "2018-06-28"],
        ["192.168.0.0", "ppp", "qqqq", "2018-06-28"],
        ["192.168.0.1", "running", "running", "2018-06-28"],
        ["192.168.0.10", "running", "running", "2018-06-28"]
    ]
}

Since you are using server-side processing, all you need to do to refresh the data is call draw(). You don't need to call ajax.reload() - although it basically does the same thing for server-side processing.

Allan

vaishnavkoka

I used the draw API, and i insert a record in my database just to check whether my datatable counter gets incremented , unfortunately to my surprise i found that until and unless i use the pagination buttons i couldn't the see the increment in counter of the page(Showing 1 to 4 of 26 entries). I used ajax reload and even in that case it didn't work out for me. The only thing worked for me is using below code:
setInterval( function () { t.ajax.reload(); }, 5000); t.draw();
But i dont think this as a feasible solution, moreover whenever a new entry is made into database, it would reflect on the last pagination button, Is it possible to show the new record on the first page of datatable?

Thanks
Koka

allan

The ordering of the rows is entirely defined by the ordering applied to the column contents. It sounds like you might have reverse ordering from what you want. The order option will define the default order (to be applied by your server-side processing script).

Allan

vaishnavkoka

So you mean i need to use default ordering ?
To have a immediate effect of changes in the database which would reflect my datatable ?

allan

The default for order is [[0, 'asc']]. It sounds like you might want [[0, 'desc']]. If its not thatn, then I've misunderstood and a test case showing the issue would be useful.

Allan