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.

your client side code could keep track of the last ID it displayed, and don't display it again if the data from the server is <= that ID. Or, the server could keep track (e.g. via the session) of the last ID it sent, and don't send another message until the ID from the database is > the ID in the session. The latter is probably a bit more "correct", in that it stops the server from sending duplicate data at all to a specific user. — ADyson, Aug 10 '17 at 10:04
@Sand the query only returns 1 row at a time. And anyway OP stated that multiple rows with the same timestamp is perfectly valid. — ADyson, Aug 10 '17 at 10:09
Also why do you use a `while` loop in your PHP when you know there will only be one row?? — ADyson, Aug 10 '17 at 10:10
He need to make the question a bit more clear this is what I think his doing his collecting transactions from several places and putting them in to a table from which he is pulling the data to be displayed as a the shown list but his getting duplicates when the script runs after 3 seconds. It lists all the data in the table regardless of it new or not. — S4NDM4N, Aug 10 '17 at 10:14
I think you should try grouping them according to time stamp or card number. — S4NDM4N, Aug 10 '17 at 10:20
keep tack of your serial number and just search where serial number is greater than the last serial number. Since you are only returning one result keeping track shouldn't be hard. Keep in mind that if you have multiple records the same in the database for each this won't work... p.s. you answered your own question in the question. — krisph, Aug 10 '17 at 10:25
You do it by keeping the last serial number in a separate variable and then comparing it to the one that you retrieve from the database. I already suggested that in the first comment — ADyson, Aug 10 '17 at 11:15
well, you know how to declare a variable? And you know how to assign a value to a variable? And you know how to compare two variables? I think you must, otherwise you couldn't have got the code you have now. All you need now is how to store something in the PHP session. `session_start(); $_SESSION["lastSerialNo"] = $row["SerialNo"];` is the general idea to store something in the session, but first you want to check that the value already in the session (if any) is different the value in the row, or not. — ADyson, Aug 10 '17 at 11:38
well you must make the comparison _before_ you assign the new $row value to the session. otherwise yes they'll always be equal. What you want to do is: 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. 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. If you're still struggling, update your question with the code you've got so far and I'll take a look. — ADyson, Aug 10 '17 at 12:20
Thank you! I've managed to get it working thanks you your (for dummies!) explanation :) I removed the where clause form my query and added the `if/else` as you suggested. It's still probably not 100% but it definitley seems to be working. My current code is https://pastebin.com/PcDhNyzy If you have any suggestions that would be great! Currently i'm checking every second, as I can sometimes receive multiple transactions with the exact same timestamp I may need to increase this? If you want to add an answer i'll gladly accept! Thanks again — TheOrdinaryGeek, Aug 10 '17 at 13:29

score 2 · Accepted Answer · answered Aug 10 '17 at 14:05

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.
}

Server Side Events with No Duplicates

1 Answers1