When you need to continually fetch data from the database without reloading the page there are several options available - ajax, websockets and Server Sent Events probably being the most commonly used.
As the database is being updated by your c# app you do not need the full bi-directional capabilities offered by websockets nor does the querying of the database require parameters to be constantly sent so of these possible options my favourite would be SSE simply because it is so easy to setup and doesn't require constant ping-pong traffic ( request/response )
A very simple Server Sent Events script to query the database every n seconds and send the recordset as a payload to the javascript client.
<?php
/* sse.php */
set_time_limit( 0 );
ini_set('auto_detect_line_endings', 1);
ini_set('max_execution_time', '0');
ob_end_clean();
/*-------------------------------------------------------------------------*/
$refresh=!empty( $_GET['refresh'] ) ? intval( $_GET['refresh'] ) : 1;
$evtname=!empty( $_GET['evtname'] ) ? $_GET['evtname'] : 'sse';
/*-------------------------------------------------------------------------*/
$dbhost = 'localhost';
$dbuser = 'root';
$dbpwd = 'xxx';
$dbname = 'xxx';
$db = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );
/* get last record ~ assumed `id` column being auto-increment */
$sql='select * from `makinebilgi` order by `id` desc limit 1;';
function sse( $evtname='sse', $data=null, $retry=1000 ){
if( !is_null( $data ) ){
echo "event:".$evtname."\r\n";
echo "retry:".$retry."\r\n";
echo "data:" . json_encode( $data, JSON_FORCE_OBJECT | JSON_HEX_QUOT | JSON_HEX_TAG | JSON_HEX_AMP | JSON_HEX_APOS );
echo "\r\n\r\n";
}
}
header('Content-Type: text/event-stream');
header('Cache-Control: no-cache');
header('Access-Control-Allow-Credentials: true');
header('Access-Control-Allow-Methods: GET');
header('Access-Control-Expose-Headers: X-Events');
while( true ){
if( connection_status() != CONNECTION_NORMAL or connection_aborted() ) break;
$payload=array();
$result = $db->query( $sql );
while( $rs=$result->fetch_object() ){
$payload[]=$rs;
}
/* send the payload */
call_user_func( 'sse', $evtname, $payload );
/* -- Send output -- */
if( @ob_get_level() > 0 ) for( $i=0; $i < @ob_get_level(); $i++ ) @ob_flush();
@flush();
/* wait for defined period before repeating loop */
sleep( $refresh );
} //end infinite loop
if( @ob_get_level() > 0 ) {
for( $i=0; $i < @ob_get_level(); $i++ ) @ob_flush();
@ob_end_clean();
}
?>
The client side code that uses some simple javascript to invoke the Server Sent Events script
<?php
?>
<!doctype html>
<html>
<head>
<title>sse display of data from c# app to db</title>
<script>
(function(){
var options={capture:false,once:false,passive:false};
document.addEventListener('DOMContentLoaded',function(event){
var url='/path/to/sse.php?refresh=1&evtname=sse';
var d1=document.getElementById('d1');
var d2=document.getElementById('d2');
var d3=document.getElementById('d3');
var d4=document.getElementById('d4');
if ( !!window.EventSource ) {
var evtSource = new EventSource( url );
evtSource.addEventListener( 'sse',function(e){
var json=JSON.parse( e.data );
var keys=Object.keys( json );
var obj=json[ keys[0] ];
console.info( json,keys );
d1.innerHTML=obj.data1;
d2.innerHTML=obj.data2;
d3.innerHTML=obj.data3;
d4.innerHTML=obj.data4;
},options );
}
},options );
})();
</script>
</head>
<body>
<div class='container'>
<img src='ahol.PNG' alt='Norway' style='width:100%;'>
<div id='d1' class='data1'></div>
<div id='d2' class='data2'></div>
<div id='d3' class='data3'></div>
<div id='d4' class='data4'></div>
</div>
</body>
</html>
None of the above is tested but should be ok - though given the state of my hangover one never knows.
