Tzach Solomon's blog: How to alert user on change in database using AJAX, MYSQL, PHP?

Hi,

I have a simple web page where multiple users are editing the same rows at the same time.
I needed a simple way to alert the users, if the page they were working on is no longer valid.
Meaning, it was updated in the database and they should refresh the page (MY NEXT TASK IS TO AUTO UPDATE).

This example document how to alert the user that the database has been changed and he/she should refresh the page.

Assumptions:
1. You have a working MySQL database with the database named checkupdate
2. In the checkupdate database there is a table named tbl1 with one integer column named _id

Let's start...

Create the following files:
index.html

<!DOCTYPE html>
<html>
<head>
 <script type="text/javascript" src="checkupdate.js"> </script>
</head>
 <body onload="process()">
                Last update:
  <div id="underInput" />
 </body>
</html>

checkupdate.js

var xmlHttp = createXmlHttpRequestObject();
var nIntervalId;
var underInput;

function process () {
 nIntervalId = setInterval(checkUpdate1, 10000);
 checkUpdate1 ();
}

function checkUpdate1 () {

 xmlHttp.open("GET", "checkupdate.php?t="+Math.random(), true);
 xmlHttp.onreadystatechange = handleServerResponse;
 xmlHttp.send();

}

function handleServerResponse () {

 //console.log(xmlHttp.readyState);
 underInput = encodeURIComponent(document.getElementById("underInput").innerHTML);
 if ( xmlHttp.readyState==4 )
  if ( xmlHttp.status==200) {
  xmlResponse = xmlHttp.responseXML;
  xmlDocumentElement = xmlResponse.documentElement;
  message = xmlDocumentElement.firstChild.textContent;
  message1 = parseInt(message);
  underInput = parseInt(underInput);
  if ( message1 > underInput ) {
   alert ("Change was made in db, please refresh the page");
  }
  document.getElementById("underInput").innerHTML = message;
 }
}

function createXmlHttpRequestObject() {

 var xmlHttp;

 if (window.ActiveXObject){
  try{
   xmlHttp = new ActiveXObject("Microsofot.XMLHTTP");
  } catch (e) {
   xmlHttp = false;
  }
 }else{
  try{
   xmlHttp = new XMLHttpRequest();
  } catch (e) {
   xmlHttp = false;
  }
 }

 if (!xmlHttp) {
  alert("Could not create XML Object");
 } else {
  return xmlHttp;
 }
}

checkupdate.php

<?php

$conn = mysql_connect('localhost','root','helloworld');

mysql_select_db('checkupdate', $conn);

$query = "SELECT MAX(_id) FROM tbl1";

$result = mysql_query($query,$conn);

$row = mysql_fetch_array($result);

mysql_close($conn);
 header('Content-Type: text/xml');

 echo '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>';
 echo '<response>';
 echo "{$row['MAX(_id)']}";
 echo '</response>';

?>

Tzach Solomon's blog

Saturday, February 9, 2013

How to alert user on change in database using AJAX, MYSQL, PHP?

1 comment: