Server Side Events with No Duplicates

Question

Using PHP and MySQL I am trying to display live data on a webpage.

So far I am able to retrieve records from the database and display them every 3 seconds. However the same record displays every 3 seconds until a new record has been inserted (i.e there are duplicates).

I think I need to have some more checks within my while loop?

I can't check for a unique TimeStamp as there will be multiple records with the same timestamp, this is perfectly valid. The SeriallNo field is primary, unique and increments by 1 after each insert - perhaps I can use this somehow?

My PHP;

$query = "SELECT TimeStamp, CardNo, SerialNo FROM transactions WHERE TimeStamp < ? ORDER BY TimeStamp DESC LIMIT 1";

$time = date('Y-m-d H:i:s');    
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $time);
$stmt->execute();
$result = $stmt->get_result();

while($row = $result->fetch_assoc()) {
    $cardNo = $row['CardNo'];
    echo "retry: 3000\n\n"; // every 3 seconds
    echo "data: ".$cardNo. ' '.$row['TimeStamp']. "\n\n"; // card no & timestamp
}

My HTML;

<script type="text/javascript">
    window.onload = function(){
        var source = new EventSource("data.php");
        source.onmessage = function(event){
            document.getElementById("result").innerHTML += "New transaction: " + event.data + "<br>";
        };
    };
</script>

A sample of the data output on the HTML page (as you can see, duplicates are output);

New transaction: 12916064 2017-08-10 10:52:03
New transaction: 12916064 2017-08-10 10:52:03
New transaction: 12916064 2017-08-10 10:52:03
New transaction: 12916064 2017-08-10 10:52:03
New transaction: 12916064 2017-08-10 10:52:03
New transaction: 12916064 2017-08-10 10:52:03
New transaction: 02723884 2017-08-10 10:54:39
New transaction: 02723884 2017-08-10 10:54:39
New transaction: 02723884 2017-08-10 10:54:39
New transaction: 02723419 2017-08-10 10:54:49
New transaction: 02723419 2017-08-10 10:54:49
New transaction: 02723419 2017-08-10 10:54:49
New transaction: 02723419 2017-08-10 10:54:49
New transaction: 02730552 2017-08-10 10:55:01
New transaction: 02730552 2017-08-10 10:55:01

I want it to look like this (no duplicates);

New transaction: 12916064 2017-08-10 10:52:03
New transaction: 02723884 2017-08-10 10:54:39
New transaction: 02723419 2017-08-10 10:54:49
New transaction: 02730552 2017-08-10 10:55:01

Any help is appreciated.

Answer 1

What you want to do is (in PHP):

If the SerialNo in the session (i.e. this should represent the last serialNo sent to the client) is the same as the one in your $row, then don't send the data to the client.

Else, if they aren't the same, send the data to the client, and store the new SerialNo in the session in place of the old one.

Something like this:

session_start(); //at the top of the script, ideally.

//...then, after your query, when you've fetched the row:
if ($_SESSION["lastSerialNo"] != $row["SerialNo"])
{
  $_SESSION["lastSerialNo"] = $row["SerialNo"]; //store new "last" serial no in the session
  //...send data to client as you do now
}
else
{
  //probably don't need to do anything here, just don't send the data.
}