Menu
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;
Any help is appreciated.
443
Server-Sent Events (SSE) is a server push technology enabling a client to receive automatic updates from a server via an HTTP connection, and describes how servers can initiate data transmission towards clients once an initial client connection has been established.
So what are Server-Sent Events? A client subscribes to a “stream” from a server and the server will send messages (“event-stream”) to the client until the server or the client closes the stream. It is up to the server to decide when and what to send the client, for instance, as soon as data changes.
You can create such things with the HTML5 server-sent events. It allows a web page to hold an open connection to a web server so that the web server can send a new response automatically at any time, there's no need to reconnect, and run the same server script from scratch over and over again.
An EventSource instance opens a persistent connection to an HTTP server, which sends events in text/event-stream format. The connection remains open until closed by calling EventSource. close() . Once the connection is opened, incoming messages from the server are delivered to your code in the form of events.
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);
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
