How to do live table updates with HTML5 SSE?

tisawyer

I have a php script which produces json like so:

summary: [{"area_name":"Area 51","device_name":"001 - Test Device 001","device_no":"40","sensor_name":"psBatteryVoltage1","sensor_value":"13.5"},{"area_name":"Area 51","device_name":"001 - Test Device 001","device_no":"40","sensor_name":"psBatteryVoltage2","sensor_value":"13.32"},{"area_name":"Area 51","device_name":"001 - Test Device 001","device_no":"40","sensor_name":"psBatteryVoltage3","sensor_value":"13.45"},{"area_name":"Area 51","device_name":"001 - Test Device 001","device_no":"40","sensor_name":"psOutputCurrent","sensor_value":"2.8"}]

I'm attempting to process it with this snippet.

// Start SSE
var source=new EventSource("db/sse.summary.php?area=" + area + "&type=" + type);

// Fires when data comes in. 
source.addEventListener('summary', function(event) {
    console.log('summary: ' + event.data);
    //var summary = JSON.parse(event.data);
    //$('#countAll').html(summary.length)
            
    $('#summary').bootstrapTable({
        ajax: event.data,
        columns: [
            {'data': 'device_name'},
            {'data': 'sensor_name'},
            {'data': 'sensor_value'}
        ]
    });
});

The are no javascript errors. The event is firing and I can print the data to the console. What I don't know how to do is pull the data from the SSE listener and get it into my table's columns. Thanks for helping.

Tim

allan

Looks like you might want to use data to tell DataTables what data to display (ajax tells it where to get the data, but you've already got it...). So you might use data: event.data.summary.

Allan

tisawyer

I got it to work by doing 2 things. First was to properly handle a flat array data source as documented https://datatables.net/examples/ajax/custom_data_flat.html

Then I had to change my php code by removing the forever loop. What I want to do is this typical SSE php. But dataTables didn't like the nonstop data and the SSE format.

So the question remains. Is there a way to continuously update a dataTable with Server Sent Events?

// SSE runs forever
while(TRUE){
    $dataArray=array();
    
    // Get list of sensors
    $SQL = "some SQL";
    $sth=$dbh->prepare($SQL);
    $prepare = array(0 => $area);
    $sth->execute($prepare);
    $rows = $sth->fetchAll();

    // Return in SSE json format
    echo "event: summary\n";
    echo 'data: ' . json_encode($rows) . "\n\n";
    ob_flush();
    flush();
    sleep(1);
}  

allan

So the question remains. Is there a way to continuously update a dataTable

Yes, but you need to handle the SSE part yourself. Then use API, such as row.add(), row().data() and row().remove() to update the DataTables with the incoming data.

Allan

tisawyer

I've decided to use rows.add() to update a DataTable from SSE data. But DataTables gives a warning.

DataTables warning: table id=summary - Requested unknown parameter '0' for row 0. For more information about this error, please see http://datatables.net/tn/4

However the array of objects (below) looks Ok to me. What am I missing?

$(document).ready(function() {
        // Hidden text to pass parameters from page to php
        var area = $('#hidden-area').text();
        var type = $('#hidden-type').text();

        // Start SSE
        var source=new EventSource("db/sse.summary.php?area=" + area + "&type=" + type);

        var table = $('#summary').DataTable();

        // Fires when php data comes in.
        source.addEventListener('summary', function(event) {
            console.log(event.data);
            sseData = JSON.parse(event.data);
            table.rows.add(sseData).draw();
});

sseData from php is an array of objects like so:

[
{"area_name":"Area 51","device_name":"001 - Test Device 001","device_no":"40","sensor_name":"psBatteryVoltage1","sensor_value":"13.5"},
{"area_name":"Area 51","device_name":"001 - Test Device 001","device_no":"40","sensor_name":"psBatteryVoltage2","sensor_value":"13.36"},
{"area_name":"Area 51","device_name":"001 - Test Device 001","device_no":"40","sensor_name":"psBatteryVoltage3","sensor_value":"13.52"},
{"area_name":"Area 51","device_name":"001 - Test Device 001","device_no":"40","sensor_name":"psOutputCurrent","sensor_value":"2.8"}
]

php code above post has not changed.

tisawyer

I figured out what I was missing. I needed to use columns.data like so:

var table = $('#summary').DataTable({
    "columns": [
        {'data': 'device_name'},
        {'data': 'sensor_name'},
        {'data': 'sensor_value'}
     ]
});