PHP and AJAX MySQL 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 MySQL database using AJAX technology.
Select a Name in the Box Below
User info will be listed here.
This example consists of four elements:
- a MySQL database
- a simple HTML form
- a JavaScript
- a PHP page
The Database
The database we will be using 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 |
The HTML Form
The example above contains a simple HTML form and a link to a JavaScript:
<html>
<head>
<script 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>
<p>
<div id="txtHint"><b>User info will be listed here.</b></div>
</p>
</body>
</html>
|
Example Explained - The HTML Form
As you can see it is just a simple HTML form with a drop down box called
"users" with names and the "id" from the database as option values.
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 "showUser()" is executed.
The execution of the function is triggered by the "onchange" event.
In other
words: Each time the user changes the value in the drop down box, the function
showUser() is called.
The JavaScript
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 || xmlHttp.readyState=="complete")
{
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;
}
|
Example Explained
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
If an item in the drop down box is selected the function
executes the following:
- Calls on the GetXmlHttpObject function to create an XMLHTTP object
- Defines the url (filename) to send to the server
- Adds a parameter (q) to the url with the content of the dropdown box
- Adds a random number to prevent the server from using a cached file
- Call stateChanged when a change is triggered
- Opens the XMLHTTP object with the given url.
- Sends an HTTP request to the server
The PHP Page
The server page called by the JavaScript, is a simple PHP file called
"getuser.php".
The page is written in PHP and uses a MySQL databse.
The code runs a SQL query against a database and returns the result as an HTML
table:
<?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);
?>
|
Example Explained
When the query is sent from the JavaScript to the PHP page the following
happens:
- PHP opens a connection to a MySQL server
- The "user" with the specified name is found
- A table is created and the data is inserted and sent to the "txtHint"
placeholder
Make your web applications look like a million bucks
|
|
Most web applications today use boring methods to present data to their viewers using grids or simple HTML tables. FusionCharts induces "life" into the web applications by converting monotonous data into lively charts, gauges & maps.
FusionCharts works with all technologies like ASP, ASP.NET, PHP, ColdFusion, Ruby on Rails, JSP, HTML pages etc.
and connects to any database to render animated & interactive charts. It takes less than 15 minutes and no expertise
whatsoever to build your first chart and just a glance of it to captivate your audience. This fact is endorsed by our
12,000 customers and 150,000 users which include a majority of the Fortune 500 companies.
And yeah, your applications could look like a million bucks by spending just $69.
So go ahead, download your
copy of FusionCharts and start "wow-ing" your customers now!
|
| 
