PHP Example - AJAX and MySQL
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 technology.
Person info will be listed here.
Example explained - The MySQL Database
The database table we use in this example looks like this:
| id |
FirstName |
LastName |
Age |
Hometown |
Job |
| 1 |
Peter |
Griffin |
41 |
Quahog |
Brewery |
| 2 |
Lois |
Griffin |
40 |
Newport |
Piano Teacher |
| 3 |
Joseph |
Swanson |
39 |
Quahog |
Police Officer |
| 4 |
Glenn |
Quagmire |
41 |
Quahog |
Pilot |
Example explained - The HTML page
The HTML page contains a link to an external JavaScript, an HTML form, and a
div element:
<html>
<head>
<script type="text/javascript" src="selectuser.js"></script>
</head>
<body>
<form>
Select a User:
<select name="users" onchange="showUser(this.value)">
<option value="1">Peter Griffin</option>
<option value="2">Lois Griffin</option>
<option value="3">Glenn Quagmire</option>
<option value="4">Joseph Swanson</option>
</select>
</form>
<br />
<div id="txtHint"><b>Person info will be listed here.</b></div>
</body>
</html>
|
As you can see it is just a simple HTML form with a drop down box called "customers".
The <div> below the form will be
used as a placeholder for info retrieved from the web server.
When the user selects data, a function called "showUser()" 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 showUser() is called.
Example explained - The JavaScript code
This is the JavaScript code stored in the file "selectuser.js":
var xmlhttp;
function showUser(str)
{
xmlhttp=GetXmlHttpObject();
if (xmlhttp==null)
{
alert ("Browser does not support HTTP Request");
return;
}
var url="getuser.php";
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()
{
if (window.XMLHttpRequest)
{
// code for IE7+, Firefox, Chrome, Opera, Safari
return new XMLHttpRequest();
}
if (window.ActiveXObject)
{
// code for IE6, IE5
return new ActiveXObject("Microsoft.XMLHTTP");
}
return null;
} |
The stateChanged() and GetXmlHttpObject functions are the same as in the
PHP AJAX Suggest chapter, you can go
to there for an explanation of those.
The showUser() Function
When a person in the drop-down box is selected, the showUser() function
executes the following:
- Calls the GetXmlHttpObject() function to create an XMLHTTP object
- Defines an URL (filename) to send to the server
- Adds a parameter (q) to the URL with the content of the drop-down box
- Adds a random number to prevent the server from using a cached file
- Each time the readyState property changes, the stateChanged() function
will be executed
- Opens the XMLHTTP object with the given URL
- Sends an HTTP request to the server
Example explained - The PHP Page
The PHP page called by the JavaScript, is called
"getuser.php".
The PHP script runs an SQL query against a MySQL database, and returns the result as HTML:
<?php
$q=$_GET["q"];
$con = mysql_connect('localhost', 'peter', 'abc123');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("ajax_demo", $con);
$sql="SELECT * FROM user WHERE id = '".$q."'";
$result = mysql_query($sql);
echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";
while($row = mysql_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>";
mysql_close($con);
?>
|
When the query is sent from the JavaScript to the PHP page, the following
happens:
- PHP opens a connection to a MySQL server
- The correct person is found
- An HTML table is created, and filled with data, and sent back to the "txtHint"
placeholder
The Creators of XMLSpy are Pleased to Announce
| 
