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
 |
W3Schools' Online Certification Program
The perfect solution for professionals who need to balance work, family, and career building.
More than 4000 certificates already issued!
|
The HTML Certificate documents your knowledge of HTML, XHTML, and CSS.
The JavaScript Certificate documents your knowledge of JavaScript and HTML DOM.
The XML Certificate documents your knowledge of XML, XML DOM and XSLT.
The ASP Certificate documents your knowledge of ASP, SQL, and ADO.
The PHP Certificate documents your knowledge of PHP and SQL (MySQL).
| 
