Jump to content

PHP MYSQL AJAX LONG POLLING


jaybo
Go to solution Solved by Barand,

Recommended Posts

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!!

 

 

Link to comment
Share on other sites

  • Solution
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

 

Link to comment
Share on other sites

  • 3 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.