Long Polling MySQL Database ONLY on UPDATE

Question

ok, maybe I'm missing something here, but for the life of me I can't quite grasp this. I'm going to be running this Ajax long-polling script on a localhost, so I'm not worried about the amount of requests, but I would still like to get this to work.

I'm running a score admin AND display scoreboard.

On one page, I have the admin, which, when you press a button, it adds 10 points to the "score" column of my table "roster" where class equals the class of the button. I am not having ANY issues Updating the database, we're just setting up whole picture.

On a second page. I have an ajax call that currently fires every second and gets the most recently updated row, and displays a popup notifying the user that 10 points were just added to So-And-So's score.

Two problems:

1. It's firing every second.
2. Even if the database hasn't been updated, it's still firing.

I KNOW I can do this with ajax long-polling, but I just can't figure out how to connect all the parts together correctly so that the ajax call will only fire when the database us updated.

JS

$(document).ready( function() {  
      pingScores();
    });

    var timestamp = null;

    function pingScores() {               
        $.ajax({ 
            type: "GET",
            url: "getUpdate.php?timestamp="+timestamp,
            cache: false,
            async: true,            
            success: function(data){
                var json = eval('(' + data + ')');  
                var notinyClass = json[2];
                var notinyName = json[3];
                //$.notiny({ text: notinyName+"<span class='addScore'> +10</span>", width: '100%', image: notinyClass }); - this is the popup API call that I am passing the class and the name to

                timestamp = json["timestamp"];  
                setTimeout("pingScores()", 1000);
            }
        });
    };

PHP (getUpdate.php)

<?php

 require "config.php"; //-- storing all login credentials elsewhere

 $db = mysql_connect($db_host, $db_user, $db_pass) or die(mysql_error());
 mysql_select_db($db_name) or die(mysql_error());

 $query = "SELECT * FROM roster ORDER BY last_updated DESC LIMIT 1";
 $result = mysql_query($query);
 $array = mysql_fetch_row( $result );

 echo json_encode($array);

 mysql_close();
?>

DATABASE

|id     | last_updated       | class | name  | score | active |
|-------|--------------------|-------|-------|-------|--------|
| 1     |2017-04-26 09:37:11 | alpha | Alpha | 10    | 1      |
|-------|--------------------|-------|-------|-------|--------|
| 2     |2017-04-26 09:32:59 | beta  | Beta  | 10    | 1      |
|-------|--------------------|-------|-------|-------|--------|
| 3     |2017-04-26 09:32:59 | delta | Delta | 10    | 1      |

When The database is updated from the series of buttons that ADD points to the score, the PHP looks like this:

<?php
    require "config.php";

    $conn = new mysqli($db_host, $db_user, $db_pass, $db_name);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $class = $_POST["class"];

    $sql = "UPDATE roster SET score = score + '10', last_updated = now() WHERE class = '".$class."'";

    if ($conn->query($sql) === TRUE) {
        echo "Updated data successfully\n";
    } else {
        echo $conn->error;
    }

    $conn->close();
?>

Every tutorial I have been able to find on how to do long polling is pulling its data from a txt file, and the instructions on how to pull from a database aren't the clearest. I'm hoping to get some clarification on how to do this.

Answer 1

If I understand correctly you only want to display a notification only if an update has occurred. You'll need to store the last notification displayed, something like:

var timestamp = null,
    lastNotificationIdentifier = '';

function pingScores() {               
    $.ajax({ 
        type: "GET",
        url: "getUpdate.php?timestamp="+timestamp,
        cache: false,
        async: true,            
        success: function(data){
            var json = eval('(' + data + ')');  
            var notinyClass = json[2];
            var notinyName = json[3];
            var localIdentifier = json.last_updated + json.id

            //check if we've displayed this record/timestamp before
            if (localIdentifier !== lastNotificationIdentifier) {

                //if not then go ahead and show the notification
                //$.notiny({ text: notinyName+"<span class='addScore'> +10</span>", width: '100%', image: notinyClass }); - this is the popup API call that I am passing the class and the name to

                //making sure to save some unique information about having shown this notification
                lastNotificationIdentifier = localIdentifier;
            }

            timestamp = json["timestamp"];  
            setTimeout("pingScores()", 1000);
        }
    });
};

You could use just the last_updated column as your unique identifier to make sure to not display a notification more than once but that's not technically unique as you can have two columns that have been updated in the same second. Which is why I went with a concoction of the timestamp and record id.

As far as this AJAX running once every second, that's how long you've set the timeout to be:

setTimeout("pingScores()", 1000);

Timeout delays are set in milliseconds, if you want 5 seconds you'd use 5000. You are likely seeing fast load times since this is running locally so the time it takes for each request to load is probably negligible.