Server Side Events with PHP & MySQL

Question

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.

Your query looks like it will get ALL rows from the transaction table. But you only every fetch one row from the result set?????? Which will likely always be the same row, but not necessarily the first or last entered into the table — RiggsFolly, Aug 08 '17 at 12:28
Based in this piece of code, you are prob going to have to store the latest id each time you retrieve values, and check if the id is < than newly added.. Also based in your current code, I don't see any code that refreshes every 3 seconds atm.. — Naruto, Aug 08 '17 at 12:28
Your server PHP code should be in a loop. The loop should be checking for a new entry somehow and only sending the new entry when it is found. It is the server that provides a _Message Pump_ by continually looping. The javascript just reacts to an incomming message from the server PHP code — RiggsFolly, Aug 08 '17 at 12:40
Dont think that was a very good tutorial. Strike it off you list of good tutorials — RiggsFolly, Aug 08 '17 at 12:50
The answer by @marekful is pretty good. That should be all you need to get your pump working — RiggsFolly, Aug 09 '17 at 09:18

marekful · Answer 1 · 2017-08-08T12:50:56.933

2

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);
}

edited Aug 08 '17 at 12:50

answered Aug 08 '17 at 12:46

marekful

14,986
6
37
59

Yea, thats more like it – RiggsFolly Aug 08 '17 at 12:49
Thanks for this detailed answer @marekful, I appreciate it. But when I use your provided code I don't see any data on screen, simply a white page. Would you have any suggestions as to how I could troubleshoot this issue? The `TimeStamp` in my database is in the format `YYYY-MM-DD H:i:s` if that makes any difference? – TheOrdinaryGeek Aug 08 '17 at 13:23
Also, do I not need to `bind` something to the placeholder `?` in the query? Thanks – TheOrdinaryGeek Aug 09 '17 at 10:27
nice, thanks a lot. What about garbage? The script in php lives meanwhile connection is alive? And after close the site? – Leandro Bardelli Aug 17 '18 at 20:55

score -1 · Answer 2 · edited Aug 09 '17 at 01:59

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 you want to show newly inserted record from transactions table, you have to add a validation to your 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';
```
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();

Server Side Events with PHP & MySQL

2 Answers2

Linked