PHP MYSQL AJAX LONG POLLING

[jaybo]

I have some code that is using Ajax long polling, connecting to a mysql DB and then outputting the current data upon a change to the table. I am implementing this page so that while 2 or more people are on the same admin page at the same time they will see any changes the other(s) makes real time.

Here is the sql:

CREATE TABLE `customers` (
  `id` int(55) NOT NULL,
  `lastModified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `firstName` int(55) NOT NULL,
  `lastName` int(55) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`);

FILE 1: cusDetLongPoll.php

<?php 
ini_set('display_errors', 1);
error_reporting(E_ALL); ?>


<!-- (A) HTML SCOREBOARD -->
<div id="sTime"></div>
<div id="sBoard">
  <div id="sFirst"></div>
  <div id="sLast"></div>
  <div id="First">First</div>
  <div id="Last">Last</div>
</div>
 
<script>
// (B) LAST UPDATED TIMESTAMP
var last = 0;
 
// (C) AJAX LONG POLL
function poll () {
  // (C1) FORM DATA
  let data = new FormData();
  data.append("last", last);
  console.log("Fetch run", last);
 
  // (C2) FETCH UPDATE ON SERVER RESPONSE
  fetch("modules/customer/cusDetLongPoll2.php", { method:"POST", body:data })
  .then(res => res.json())
  .then(data => {
    //console.log(data);
    
    // (C2-1) UPDATE HTML DISPLAY
    document.getElementById("sTime").innerHTML = data.lastModified;
    document.getElementById("sFirst").innerHTML = data.firstName;
    document.getElementById("sAway").innerHTML = data.lastName;
 
    // (C2-2) NEXT ROUND
    last = data.unix;
    poll();
  }) 
 
// (C3) CATCH ERROR - LOOP ON TIMEOUT
  .catch(err => poll());
}
 
// (D) GO!
window.onload = poll;

</script>

FILE 2: cusDetLongPoll2.php

<?php 
ini_set('display_errors', 1);
error_reporting(E_ALL);

class CustomerDetails {
  
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  protected $pdo = null;
  protected $stmt = null;
  function __construct () { try {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  } catch (Exception $ex) { exit($ex->getMessage()); }}

  // (B) DESTRUCTOR - CLOSE CONNECTION
  function __destruct () {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  // (C) GET LATEST CUSTOMER DETAILS
  function getCusDetails() {
    $this->stmt = $this->pdo->prepare(
      "SELECT *, UNIX_TIMESTAMP(`lastModified`) AS `unix`
       FROM `customers` WHERE id = 1"
    );
    $this->stmt->execute();
    return $this->stmt->fetch();
  }
}

// (D) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "NAME");
define("DB_CHARSET", "utf8");
define("DB_USER", "USER");
define("DB_PASSWORD", "PASSWORD");

// (E) CHECK FOR CUSTOMER UPDATES
// ******** THIS IF STATEMENT IS NOT WORKING - WHEN REMOVED WE CAN PRINT_R THE CUSTOMER DETAILS AS ARRAY. WHEN USED THE IF STATEMENT RETURNS NO DATA. *********
if (isset($_POST["last"])) {

  // (E1) SET TIME LIMIT
  set_time_limit(30); // set an appropriate time limit
  ignore_user_abort(false); // stop when long polling breaks

  // (E2) LOOP UNTIL THERE ARE UPDATES OR TIMEOUT
  $_DETAILS = new CustomerDetails();
  //print_r($_DETAILS->getCusDetails());
  while (true) {
    $details = $_DETAILS->getCusDetails();
    if (isset($details["unix"]) && $details["unix"] > $_POST["last"]) {
      echo json_encode($details);
      break;
    }
    sleep(1); // short pause to not break server
  }

}

From my testing I have found that in the code above at > if (isset($_POST["last"])) { ...

When this is taken out and I run the PRINT_R on the $_DETAILS->getCusDetails(); I see the output on the screen. When I add in the if statement then I do not get any output or data at all. I ran this code the first time and it worked, changed the data and timestamp in the DB and it auto updated. But then my browser ran a refresh through my code and it hasn't worked since. May be totally unrelated but this was the order of events. Any help appreciated!!

 

 

[requinix]

Any $_POST data you would be able to see in your browser's developer console where it handles network monitoring. Pull that up, run your script, and see what is or isn't being sent in the requests.

[Barand]

2 hours ago, jaybo said:

`lastModified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

Even though it is named "lastmodified", that will record the time the record was inserted. If you want to record when it was modified you need

DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT TIMESTAMP

 

[jaybo]

Thank you Barand - that helped! Appears to work now.