Praneeth Samarasinghe Praneeth Samarasinghe - 3 months ago 15
MySQL Question

Mysql with Set Timeout function, While loop to fetch data with specific time interval

I want to fetch data from the database table with specific time interval. Data base is being filled every 5 seconds time so I need to fetch data 5 second intervals.

This is my code without the setTimeout function it displays the whole result up to the value the table is filled, but have to refresh the page to get other data filled after that.

<?php
$con = mysql_connect("localhost","shsas","");

if (!$con) {
die('Could not connect: ' . mysql_error());
}
else
{
<script type="text/javascript">
mysql_select_db("shsas", $con);
$sth = mysql_query("SELECT Gaslevel FROM gas");
$rows = array();
$rows['name'] = 'Gaslevel';

setTimeout(function(){

while($r = mysql_fetch_array($sth)) {
$rows['data'][] = $r['Gaslevel'];
$result = array();
array_push($result,$rows);
print json_encode($result, JSON_NUMERIC_CHECK);

},5000);
}
</script>
}
mysql_close($con);

?>

Answer

The constant need to refresh data seems to me like an ideal candidate for using SSE like I mentioned in a comment. The idea is you have a php script ( gas_sse.php ) that begins working in an endless loop once your javascript & html page initialises a connection. The endless loop has a sleep call and will do nothing for a prescribed period of time ( 5s ) - inside the loop you will query the database for the latest information ( and you would probably want to edit the sql so that it does not return a bigger recordset on each iteration if the db is being updated with new rows every 5 seconds )

Once the query has run you can process the recordset prior to sending as a message to the javascript listener and process it on the client side however you see fit.

What follows is an untested example of the basic idea - if there are errors ( and there usually are if it's not tested ) then I apologise.

<?php
    /*
        gas_sse.php
    */

    set_time_limit( 0 );

    ini_set('auto_detect_line_endings', 1);
    ini_set('mysql.connect_timeout','7200');
    ini_set('max_execution_time', '0');

    /* -- Edit to suit your location -- */
    date_default_timezone_set( 'Europe/London' );

    ob_end_clean();
    gc_enable();


    /* -- set headers -- */
    header('Content-Type: text/event-stream'); /* !important! */
    header('Cache-Control: no-cache');
    header('Access-Control-Allow-Credentials: true');
    header('Access-Control-Allow-Methods: GET');
    header('Access-Control-Expose-Headers: X-Events');  



    /* -- utility function to send formatted sse message -- */
    if( !function_exists('sse_message') ){
        function sse_message( $evtname='gas', $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";    
            }
        }
    }



    /* -- How often to send messages -- */
    $sleep=5;




    /* You should use mysqli or PDO instead!!! */
    $con = mysql_connect( "localhost", "shsas", "" );
    mysql_select_db( "shsas", $con );




    while( true ){
        if( connection_status() != CONNECTION_NORMAL or connection_aborted() ) {
            break;
        }
        /* Infinite loop is running - perform actions you need */



        /* -- Query database -- */
        $sql='select `Gaslevel` from`gas` order by `id` desc limit 100;';
        $res=mysql_query( $sql );
        $payload=array();

        while( $rs=mysql_fetch_assoc( $res ) ){
            $payload[]=$rs;
        }


        /* -- prepare sse message -- */
        sse_message( 'gas', $payload );



        /* -- Send output -- */
        if( @ob_get_level() > 0 ) for( $i=0; $i < @ob_get_level(); $i++ ) @ob_flush();
        @flush();


        /* wait */
        sleep( $sleep );
    }



    if( @ob_get_level() > 0 ) {
        for( $i=0; $i < @ob_get_level(); $i++ ) @ob_flush();
        @ob_end_clean();
    }
?>

And on your html page that initialises the connection to the sse script the general idea would be along these lines:

<script type='text/javascript'>
    var evtSource = new EventSource( "gas_sse.php" );

    evtSource.onmessage = function(e) {
        var json=JSON.parse( e.data );
        /* do stuff with json data */
    }
</script>