AJAX Database

From http://www.w3schools.com (Copyright Refsnes Data)

# AJAX Database Example

AJAX can be used for interactive communication with a database.

## AJAX Database Example

In the AJAX example below we will demonstrate how a web page can fetch information from a database using AJAX technology.

## Select a Name in the Box Below

Select a Customer:
Customer info will be listed here.

## AJAX Example Explained

The example above contains a simple HTML form and a link to a JavaScript:

    <html>
    <head>
    <script src="selectcustomer.js"></script>
    </head>

    <body>

    <form>
    Select a Customer:
    <select name="customers" onchange="showCustomer(this.value)">
      <option value="ALFKI">Alfreds Futterkiste</option>
      <option value="NORTS ">North/South</option>
      <option value="WOLZA">Wolski Zajazd</option>
    </select>
    </form>

    <p>
    <div id="txtHint"><b>Customer info will be listed here.</b></div>
    </p>

    </body>
    </html>

As you can see it is just a simple HTML form with a drop down box called "customers".

The paragraph below the form contains a div called "txtHint". The div is used as a placeholder for info retrieved from the web server.

When the user selects data, a function called "showCustomer()" is executed. The execution of the function is triggered by the "onchange" event. In other words: Each time the user change the value in the drop down box, the function showCustomer is called.

The JavaScript code is listed below.

## The AJAX JavaScript

This is the JavaScript code stored in the file "selectcustomer.js":

    var xmlHttp

    function showCustomer(str)
    {
    xmlHttp=GetXmlHttpObject();
    if (xmlHttp==null)
      {
      alert ("Your browser does not support AJAX!");
      return;
      }
    var url="getcustomer.asp";
    url=url+"?q="+str;
    url=url+"&sid="+Math.random();
    xmlHttp.onreadystatechange=stateChanged;
    xmlHttp.open("GET",url,true);
    xmlHttp.send(null);
    }

    function stateChanged()
    {
    if (xmlHttp.readyState==4)
    {
    document.getElementById("txtHint").innerHTML=xmlHttp.responseText;
    }
    }

    function GetXmlHttpObject()
    {
    var xmlHttp=null;
    try
      {
      // Firefox, Opera 8.0+, Safari
      xmlHttp=new XMLHttpRequest();
      }
    catch (e)
      {
      // Internet Explorer
      try
        {
        xmlHttp=new ActiveXObject("Msxml2.XMLHTTP");
        }
      catch (e)
        {
        xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");
        }
      }
    return xmlHttp;
    }

## The AJAX Server Page

The server page called by the JavaScript, is a simple ASP file called "getcustomer.asp".

The page is written in VBScript for an Internet Information Server (IIS). It could easily be rewritten in PHP, or some other server language. Look at a corresponding example in PHP.

The code runs an SQL against a database and returns the result as an HTML table:

    <%
    response.expires=-1
    sql="SELECT * FROM CUSTOMERS WHERE CUSTOMERID="
    sql=sql & "'" & request.querystring("q") & "'"

    set conn=Server.CreateObject("ADODB.Connection")
    conn.Provider="Microsoft.Jet.OLEDB.4.0"
    conn.Open(Server.Mappath("/db/northwind.mdb"))
    set rs = Server.CreateObject("ADODB.recordset")
    rs.Open sql, conn

    response.write("<table>")
    do until rs.EOF
      for each x in rs.Fields
        response.write("<tr><td><b>" & x.name & "</b></td>")
        response.write("<td>" & x.value & "</td></tr>")
      next
      rs.MoveNext
    loop

    response.write("</table>")
    %>

From http://www.w3schools.com (Copyright Refsnes Data)