Saturday, February 9, 2013

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>';

?>



Thursday, February 7, 2013

AJAX, First Example (Based on the new Boston website)

Hi,

As I mentioned, this blog is mostly for me to remember the code I'm learning so I won't be doing much explaining of the code BUT however, I will place links in case I took the examples from some source.

Sources

1. Example was basically taken from http://www.youtube.com/watch?v=tp3Gw-oWs2k, I just change'd it a bit since it wasn't really working.
2. http://www.w3schools.com/ajax/

Assumptions:
1. You have a working web server supporting php
2. You are familiar with PHP
3. You are familiar with JavaScript

So lets start!
1. Create a file name index.html and copy the code below to the file

<!DOCTYPE html>
<html>
<head>
 <script type="text/javascript" src="foodstore.js"> </script>
</head>
 <body>
  <input type="text" id="userInput" onkeyup="process()" />
  <div id="underInput" />
 </body>
</html>

2. Create a file name foodstore.js and copy the code below to the file

var xmlHttp = createXmlHttpRequestObject();

function createXmlHttpRequestObject() {

 var xmlHttp;

 if (window.ActiveXObject){
  try{
   xmlHttp = new ActiveXObject("Microsoft.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;
 }
}

function process() {


  food = encodeURIComponent(document.getElementById("userInput").value);
  //alert(food);
  xmlHttp.open("GET", "foodstore.php?food="+food, true);
  xmlHttp.onreadystatechange = handleServerResponse;
  xmlHttp.send();

}

function handleServerResponse () {

 if ( xmlHttp.readyState==4 )
  if ( xmlHttp.status==200 || xmlHttp.status == 206) {
  xmlResponse = xmlHttp.responseXML;
  xmlDocumentElement = xmlResponse.documentElement;
  message = xmlDocumentElement.firstChild.textContent;
  document.getElementById("underInput").innerHTML = '<span style="color:blue">' + message + '</span>';
 }
}

3. Create a file name foodstore.php and copy the code below to the file

<?php
 header('Content-Type: text/xml');
 echo '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>';

 echo '<response>';
  $food=$_GET['food'];
  $foodArray = array('tuna','bacon','beef','loaf','ham');

  if (in_array($food,$foodArray)) {
   echo "We do have ".$food;
  } elseif ($food==''){
   echo "Enter food";
  } else {
   echo "Sorry, we do not have ".$food;
  }

 echo '</response>';
?>

4. Place the 3 files in a directory under your web server (meaning you can browse it)

5. Surf to the index.html file.

6.You should see text box. Type your name in it and you should see something like this below the text box:
Sorry, we do not have Tzach

7. Type in tune and you should see the following:
We do have tuna

Hello World?!

Hi,

My name is Tzach, 30 years old and I know it's kinda corny but I though to just type Hello World as a good example of posts in the blog.

The whole purpose of this blog is to save and remember  code examples or solutions to problems I've encountered during work or coding at home.

I hope that the code and explanations will be coherent enough so that it might use others.

Till next time,
Tzach