Server Side Events with PHP & MySQL

231
August 08, 2017, at 8:05 PM

I am building a (very basic) application using PHP and MySQL. The purpose of this application is to display 'real time' data transactions on a web page. These transactions come from a transactions table in a MySQL database.

So far I can retrieve and display the data on the webpage. However I was expecting to see the data refresh only when new transactions were inserted into the transactions table?

Currently the live feed displays the last record repeatedly until a new transaction is inserted, this loops.

My code so far is;

transactions.html

<!DOCTYPE html>
<html lang="en">
<head>
    <title>HTML5 Server-Sent Events</title>
    <script type="text/javascript">
        window.onload = function(){
            var source = new EventSource("transactions.php");
            source.onmessage = function(event){
                document.getElementById("result").innerHTML += "New transaction: " + event.data + "<br>";
            };
        };
    </script>
</head>
<body>
    <div id="result">
        <!--Server response will be inserted here-->
    </div>
</body>
</html>

transactions.php

<?php 
include 'conn.php'; // database connection
header("Content-Type: text/event-stream");
header("Cache-Control: no-cache");
$query = "SELECT TimeStamp, CardNo FROM transactions ORDER BY TimeStamp DESC LIMIT 1";
// `TimeStamp` is in the format YYYY-MM-DD H:i:s 
if ($result = $conn->query($query)) {
    $row = $result->fetch_assoc();
    echo "data: " . $row['CardNo'] . "\n\n";
}
flush();
?>

I have been following this tutorial if that makes any difference.

My questions;

  • how can I refresh the live feed only when new transactions are inserted?
  • the live feed currently refreshes approximately every 3 seconds, where is this set?

Any help is appreciated.

Answer 1

You are missing a couple of things on the server side to make this work.

First, as @RiggsFilly pointed out, you need to use a WHERE clause in the statement. The condition should be to look for transactions that are newer than the last sent.

For that, you need to keep track of the timestamp of the last sent message.

The server should only send a message if the query, now with the condition, returns a result.

Finally, the entire routine to check for new transactions and send a message if found must be kept in a loop.

<?php 
include 'conn.php'; // database connection
header("Content-Type: text/event-stream");
header("Cache-Control: no-cache");
$query = "SELECT TimeStamp, CardNo FROM transactions WHERE TimeStamp > ?";
$stmt = $conn->prepare($query);
$ts = time();
while(true) 
{
    if ($result = $stmt->execute([$ts])) {
        $row = $result->fetch_assoc();
        echo "data: " . $row['CardNo'] . "\n\n";
        $ts = $row['TimeStamp'];
        flush();
    }
    sleep(2);
}
Answer 2

My questions;

  1. how can I refresh the live feed only when new transactions are inserted? I am not clear with this question.

       transactions.php returns always last record of transactions table.
       if u want to show newly inserted record from transactions table,you have to add a validation to ur script, either client or server side
      Here is the query to check that has transactions table modified?
      **SELECT update_time FROM information_schema.tables WHERE  table_schema='mydb' AND table_name='mytable' and update_time = 'your validation time';**
    
  2. The live feed currently refreshes approximately every 3 seconds, where is this set? Normally, the browser auto-reconnects to the event source when the connection is closed, but that behavior can be canceled from either the client or server. To cancel a stream from the client, simply call: source.close();

Rent Charter Buses Company
READ ALSO
Yii2 debug toolbar error &#39;Unable to find debug data tagged with &hellip; &#39;

Yii2 debug toolbar error 'Unable to find debug data tagged with … '

In a page on my site I receive this error about debug toolbar, as suggested in other answera at similar question:

525
How to dynamically add and get total of time from database?

How to dynamically add and get total of time from database?

I'm trying to get the total time duration of all time variables from the databaseBut I don't seem to get the right total or result

179
Only last row data is inserting in database and not all rows data

Only last row data is inserting in database and not all rows data

I am implementing clothing shopping cart and i am using session array for storing items to add to cartnow on place order button click i want that this cart table all rows which contains items on each row insert in to database

198
Encrypt &amp; Decrypt C# function to PHP function

Encrypt & Decrypt C# function to PHP function

I would like to convert C# function to PHP functionHere is link to Function in C# : https://stackoverflow

405