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...
This thread is more than a year old. Please don't revive it unless you have something important to add.

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.