Server events with PHP and MySQL
I am creating 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 table transactions
in a MySQL database.
So far, I can get and display the data in the webpage. However, I was expecting to see data update only when new transactions were inserted into the table transactions
?
Currently, the live feed re-writes the last record until a new transaction is inserted. This means that it is happening.
My code so far:
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 followed this tutorial if it matters.
My questions;
- How can I update the live feed only when new transactions are inserted?
- the live feed is updated approximately every 3 seconds, where is it?
Any help is appreciated.
source to share
You are missing a couple of things on the server side to make this work.
First, as @RiggsFilly pointed out, you need to use the WHERE clause in the statement. The condition should be to look for transactions that are newer than the last ones sent.
To do this, you need to track the timestamp of the last message sent.
The server should only send a message if the request, now with a condition, returns a result.
Finally, the whole procedure for checking new transactions and sending a message, if found, should 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);
}
source to share
-
how to update live feed only when new transactions are inserted?
I don't understand this question.
transaction.php always returns the last record of the transaction table.
If you want to show the recently inserted record from the transaction table, you need to add an acknowledgment to the script, client or server side
Here is a query to check if the transaction table has changed?
SELECT update_time FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable' and update_time = 'your validation time';
-
The live feed refreshes roughly every 3 seconds, where is this set?
Typically, the browser automatically connects to the event source when the connection is closed, but this behavior can be reversed from either the client or the server. To cancel a stream from a client, simply call:
source.close();
source to share