Tuesday, September 15, 2015

AJAX - Client Side and Server Side PHP Database Operations

·        AJAX stands for Asynchronous JavaScript and XML. AJAX is a new technique for creating better, faster, and more interactive web applications with the help of XML, HTML, CSS, and Java Script.
·        Ajax uses XHTML for content, CSS for presentation, along with Document Object Model and JavaScript for dynamic content display.
·        Conventional web applications transmit information to and from the sever using synchronous requests. It means you fill out a form, hit submit, and get directed to a new page with new information from the server.
·        With AJAX, when you hit submit, JavaScript will make a request to the server, interpret the results, and update the current screen. In the purest sense, the user would never know that anything was even transmitted to the server.
·        XML is commonly used as the format for receiving server data, although any format, including plain text, can be used.
·        AJAX is a web browser technology independent of web server software.
·        A user can continue to use the application while the client program requests information from the server in the background.
·        Intuitive and natural user interaction. Clicking is not required, mouse movement is a sufficient event trigger.
·        Data-driven as opposed to page-driven.

Rich Internet Application Technology

AJAX is the most viable Rich Internet Application (RIA) technology so far. It is getting tremendous industry momentum and several tool kit and frameworks are emerging. But at the same time, AJAX has browser incompatibility and it is supported by JavaScript, which is hard to maintain and debug.

AJAX is Based on Open Standards

AJAX is based on the following open standards:
  • Browser-based presentation using HTML and Cascading Style Sheets (CSS).
  • Data is stored in XML format and fetched from the server.
  • Behind-the-scenes data fetches using XMLHttpRequest objects in the browser.
  • JavaScript to make everything happen.

AJAX - Database Operations
To clearly illustrate how easy it is to access information from a database using AJAX, we are going to build MySQL queries on the fly and display the results on "ajax.html". But before we proceed, let us do the ground work. Create a table using the following command.
NOTE: We are assuming you have sufficient privilege to perform the following MySQL operations
CREATE TABLE 'ajax_example' (
   'name' varchar(50) NOT NULL,
   'age' int(11) NOT NULL,
   'sex' varchar(1) NOT NULL,
   'wpm' int(11) NOT NULL,
   PRIMARY KEY  ('name')
Now dump the following data into this table using the following SQL statements:
INSERT INTO 'ajax_example' VALUES ('Jerry', 120, 'm', 20);
INSERT INTO 'ajax_example' VALUES ('Regis', 75, 'm', 44);
INSERT INTO 'ajax_example' VALUES ('Frank', 45, 'm', 87);
INSERT INTO 'ajax_example' VALUES ('Jill', 22, 'f', 72);
INSERT INTO 'ajax_example' VALUES ('Tracy', 27, 'f', 0);
INSERT INTO 'ajax_example' VALUES ('Julie', 35, 'f', 90);

Client Side HTML File

Now let us have our client side HTML file, which is ajax.html, and it will have the following code:
<script language="javascript" type="text/javascript">
//Browser Support Code
function ajaxFunction(){
   var ajaxRequest;  // The variable that makes Ajax possible!
      // Opera 8.0+, Firefox, Safari
      ajaxRequest = new XMLHttpRequest();
   }catch (e){
      // Internet Explorer Browsers
         ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
      }catch (e) {
            ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
         }catch (e){
            // Something went wrong
            alert("Your browser broke!");
            return false;
   // Create a function that will receive data
   // sent from the server and will update
   // div section in the same page.
   ajaxRequest.onreadystatechange = function(){
      if(ajaxRequest.readyState == 4){
         var ajaxDisplay = document.getElementById('ajaxDiv');
         ajaxDisplay.innerHTML = ajaxRequest.responseText;
   // Now get the value from user and pass it to
   // server script.
   var age = document.getElementById('age').value;
   var wpm = document.getElementById('wpm').value;
   var sex = document.getElementById('sex').value;
   var queryString = "?age=" + age ;
   queryString +=  "&wpm=" + wpm + "&sex=" + sex;
   ajaxRequest.open("GET", "ajax-example.php" + queryString, true);
<form name='myForm'>
   Max Age: <input type='text' id='age' /> <br />
   Max WPM: <input type='text' id='wpm' /> <br />
   <select id='sex'>
      <option value="m">m</option>
      <option value="f">f</option>
   <input type='button' onclick='ajaxFunction()' value='Query MySQL'/>
<div id='ajaxDiv'>Your result will display here</div>
NOTE: The way of passing variables in the Query is according to HTTP standard and have formA.
The above code will give you a screen as given below:
NOTE: This is dummy screen and would not work
Max Age:  
Max WPM: 
Your result will display here in this section after you have made your entry.
NOTE: This is a dummy screen.

Server Side PHP File

Your client-side script is ready. Now, we have to write our server-side script, which will fetch age, wpm, and sex from the database and will send it back to the client. Put the following code into the file "ajax-example.php".
$dbhost = "localhost";
$dbuser = "dbusername";
$dbpass = "dbpassword";
$dbname = "dbname";
//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
//Select Database
mysql_select_db($dbname) or die(mysql_error());
// Retrieve data from Query String
$age = $_GET['age'];
$sex = $_GET['sex'];
$wpm = $_GET['wpm'];
// Escape User Input to help prevent SQL Injection
$age = mysql_real_escape_string($age);
$sex = mysql_real_escape_string($sex);
$wpm = mysql_real_escape_string($wpm);
//build query
$query = "SELECT * FROM ajax_example WHERE sex = '$sex'";
   $query .= " AND age <= $age";
   $query .= " AND wpm <= $wpm";
//Execute query
$qry_result = mysql_query($query) or die(mysql_error());
//Build Result String
$display_string = "<table>";
$display_string .= "<tr>";
$display_string .= "<th>Name</th>";
$display_string .= "<th>Age</th>";
$display_string .= "<th>Sex</th>";
$display_string .= "<th>WPM</th>";
$display_string .= "</tr>";
// Insert a new row in the table for each person returned
while($row = mysql_fetch_array($qry_result)){
   $display_string .= "<tr>";
   $display_string .= "<td>$row[name]</td>";
   $display_string .= "<td>$row[age]</td>";
   $display_string .= "<td>$row[sex]</td>";
   $display_string .= "<td>$row[wpm]</td>";
   $display_string .= "</tr>";
echo "Query: " . $query . "<br />";
$display_string .= "</table>";
echo $display_string;
Now try by entering a valid value (e.g., 120) in Max Age or any other box and then click Query MySQL button.
Max Age:  
Max WPM: 
Your result will display here in this section after you have made your entry.
If you have successfully completed this lesson, then you know how to use MySQL, PHP, HTML, and Javascript in tandem to write AJAX applications.

AJAX can be used for interactive communication with a database.

AJAX Database Example

The following example will demonstrate how a web page can fetch information from a database with AJAX:



Person info will be listed here...

Example Explained - The MySQL Database

The database table we use in the example above looks like this:
Piano Teacher
Police Officer

Example Explained

In the example above, when a user selects a person in the dropdown list above, a function called "showUser()" is executed.
The function is triggered by the onchange event.
Here is the HTML code:


function showUser(str) {
    if (str == "") {
        document.getElementById("txtHint").innerHTML = "";
    } else { 
        if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        xmlhttp.onreadystatechange = function() {
            if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                document.getElementById("txtHint").innerHTML = xmlhttp.responseText;

<select name="users" onchange="showUser(this.value)">
  <option value="">Select a person:</option>
  <option value="1">Peter Griffin</option>
  <option value="2">Lois Griffin</option>
  <option value="3">Joseph Swanson</option>
  <option value="4">Glenn Quagmire</option>
<div id="txtHint"><b>Person info will be listed here...</b></div>

Code explanation:
First, check if no person is selected (str == ""). If no person is selected, clear the content of the txtHint placeholder and exit the function.
If a person is selected, do the following:
  • Create an XMLHttpRequest object
  • Create the function to be executed when the server response is ready
  • Send the request off to a file on the server
  • Notice that a parameter (q) is added to the URL (with the content of the dropdown list)

The PHP File

The page on the server called by the JavaScript above is a PHP file called "getuser.php".
The source code in "getuser.php" runs a query against a MySQL database, and returns the result in an HTML table:
<!DOCTYPE html>
table {
    width: 100%;
    border-collapse: collapse;

table, td, th {
    border: 1px solid black;
    padding: 5px;

th {text-align: left;}

$q = intval($_GET['q']);

$con = mysqli_connect('localhost','peter','abc123','my_db');
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));

$sql="SELECT * FROM user WHERE id = '".$q."'";
$result = mysqli_query($con,$sql);

echo "<table>
while($row = mysqli_fetch_array($result)) {
    echo "<tr>";
    echo "<td>" . $row['FirstName'] . "</td>";
    echo "<td>" . $row['LastName'] . "</td>";
    echo "<td>" . $row['Age'] . "</td>";
    echo "<td>" . $row['Hometown'] . "</td>";
    echo "<td>" . $row['Job'] . "</td>";
    echo "</tr>";
echo "</table>";
Explanation: When the query is sent from the JavaScript to the PHP file, the following happens:
  1. PHP opens a connection to a MySQL server
  2. The correct person is found
  3. An HTML table is created, filled with data, and sent back to the "txtHint" placeholder
The XMLHttpRequest object is the key to AJAX. It has been available ever since Internet Explorer 5.5 was released in July 2000, but was not fully discovered until AJAX and Web 2.0 in 2005 became popular.
XMLHttpRequest (XHR) is an API that can be used by JavaScript, JScript, VBScript, and other web browser scripting languages to transfer and manipulate XML data to and from a webserver using HTTP, establishing an independent connection channel between a webpage's Client-Side and Server-Side.
The data returned from XMLHttpRequest calls will often be provided by back-end databases. Besides XML, XMLHttpRequest can be used to fetch data in other formats, e.g. JSON or even plain text.
You already have seen a couple of examples on how to create an XMLHttpRequest object.
Listed below is listed are some of the methods and properties that you have to get familiar with.

XMLHttpRequest Methods

  • abort()
Cancels the current request.
  • getAllResponseHeaders()
Returns the complete set of HTTP headers as a string.
  • getResponseHeader( headerName )
Returns the value of the specified HTTP header.
  • open( method, URL )
open( method, URL, async )
open( method, URL, async, userName )
open( method, URL, async, userName, password )
Specifies the method, URL, and other optional attributes of a request.
The method parameter can have a value of "GET", "POST", or "HEAD". Other HTTP methods, such as "PUT" and "DELETE" (primarily used in REST applications) may be possible.
The "async" parameter specifies whether the request should be handled asynchronously or not. "true" means that the script processing carries on after the send() method without waiting for a response, and "false" means that the script waits for a response before continuing script processing.
  • send( content )
Sends the request.
·        setRequestHeader( label, value )
Adds a label/value pair to the HTTP header to be sent.

XMLHttpRequest Properties

  • onreadystatechange
An event handler for an event that fires at every state change.
  • readyState
The readyState property defines the current state of the XMLHttpRequest object.
The following table provides a list of the possible values for the readyState property:
The request is not initialized.
The request has been set up.
The request has been sent.
The request is in process.
The request is completed.
readyState = 0 After you have created the XMLHttpRequest object, but before you have called the open() method.
readyState = 1 After you have called the open() method, but before you have called send().
readyState = 2 After you have called send().
readyState = 3 After the browser has established a communication with the server, but before the server has completed the response.
readyState = 4 After the request has been completed, and the response data has been completely received from the server.
  • responseText
Returns the response as a string.
  • responseXML
Returns the response as XML. This property returns an XML document object, which can be examined and parsed using the W3C DOM node tree methods and properties.
  • status
Returns the status as a number (e.g., 404 for "Not Found" and 200 for "OK").
  • statusText
Returns the status as a string (e.g., "Not Found" or "OK").

AJAX Security: Server Side

·        AJAX-based Web applications use the same server-side security schemes of regular Web applications.
·        You specify authentication, authorization, and data protection requirements in your web.xml file (declarative) or in your program (programmatic).
·        AJAX-based Web applications are subject to the same security threats as regular Web applications.

AJAX Security: Client Side

·        JavaScript code is visible to a user/hacker. Hacker can use JavaScript code for inferring server-side weaknesses.
·        JavaScript code is downloaded from the server and executed ("eval") at the client and can compromise the client by mal-intended code.
·        Downloaded JavaScript code is constrained by the sand-box security model and can be relaxed for signed JavaScript.