The XMLHttpRequest object is used to send and receive data between a web browser and web server. Up to now, we have been retrieving data from data sources that remain relatively static, such as text or XML files.
In this tutorial, we will learn how to use the same Ajax concepts that we have learned so far and pull data from your database. We will be using an ASP file to handle the server-side scripting and MySQL as our database.
You can replace ASP with any other server-side scripting language that you are familiar with such as PHP, or ASP.NET. The back-end data source can be swapped for MS Access, as well as MSSQL. The concept remains the same.
HTML Example
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
function showEmployee(str) {
if (str==""){
document.getElementById("div1").innerHTML="Select an Employee for more details!";
return;
}
var xhr = false;
if (window.XMLHttpRequest) {
// IE7+, Firefox, Chrome, Opera, Safari
xhr = new XMLHttpRequest();
} else {
// IE5/IE6
xhr = new ActiveXObject("Microsoft.XMLHTTP");
}
if (xhr) {
xhr.onreadystatechange = function () {
if (xhr.readyState == 4 && xhr.status == 200) {
document.getElementById("div1").innerHTML = xhr.responseText;
}
}
xhr.open("GET", "/demo/ajax_dbquery.asp?q="+str, true);
xhr.send(null);
}
}
</script>
</head>
<body>
<div>
<select name="employees" onchange="showEmployee(this.value)">
<option value="">Select an Employee:</option>
<option value="3">Frank Ford</option>
<option value="1">John Smith</option>
<option value="4">Lisa Stark</option>
<option value="2">Sally Smart</option>
</select>
<div id="div1">Select an Employee for more details!</div>
</div>
</body>
</html>ASP Example
<% Response.AddHeader "Cache-Control", "no-cache" %>
<% Response.AddHeader "Cache-Control", "no-store" %>
<% Response.AddHeader "Cache-Control", "must-revalidate" %>
<% Response.AddHeader "Pragma", "no-cache" %>
<% Response.AddHeader "Expires", "Sat, 14 Jan 2012 01:00:00 GMT" %>
<%
Dim oConn, oRs
Dim qry, cstr
Dim db_name, db_username, db_userpassword
Dim db_server
db_server = "dbserver.com"
db_name = "databaseName"
db_username = "dbUserName"
db_userpwd = "dbPassword"
tablename = "employees"
fieldname1 = "empName"
fieldname2 = "empTitle"
fieldname3 = "empOffice"
q = request.querystring("q")
cstr = "Driver={MySQL ODBC 3.51 Driver};SERVER=" & db_server & ";DATABASE=" & db_name & ";UID=" & db_username & ";PWD=" & db_userpwd
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open cstr
qry = "SELECT * FROM " & tablename & " WHERE empID = " & q
Set oRS = oConn.Execute(qry)
response.write("<table>")
if not oRS.EOF then
while not oRS.EOF
response.write ("<tr><td>Name:</td><td>" & oRs.Fields(fieldname1) & "</td></tr>")
response.write ("<tr><td>Title:</td><td>" & oRs.Fields(fieldname2) & "</td></tr>")
response.write ("<tr><td>Office:</td><td>" & oRs.Fields(fieldname3) & "</td></tr>")
oRS.movenext
wend
oRS.close
end if
response.write("</table>")
Set oRs = nothing
Set oConn = nothing
%>
