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.