Bookmark and Share

Ajax - MySQL Database

We already know how to run an external PHP script with AJAX, so let's take it to the next level and pull some data down from a MySQL database. Our "order.html" file and PHP script will have to be updated and we also need to make a new database.

Advertise on

Create the MySQL Table

To clearly illustrate how easy it is to access information from a database using Ajax, we are going to build MySQL queries on the fly and display the results on "order.html".

Create a new database or use an existing one and then import the table ajax_example.sql to that database. This sql file will create the table ajax_example and insert all the data rows. The table has four columns:

  • ae_name - The name of the person
  • ae_age - Person's age
  • ae_sex - The gender of the person
  • ae_wpm - The words per minute that person can type

Update order.html

We want to be able to build queries from our HTML file, so there are a few form elements that will need to be added. The three inputs we are going to implement are:

  • Maximum Age (Text Input) - Let the user select the maximum age to be returned.
  • Maximum WPM (Text Input) - Let the user select the maximum wpm to returned.
  • Gender (Select Input) - Let the user select the gender of a valid person.

order.html HTML/Javascript Code:


<script language="javascript" type="text/javascript">
//Browser Support Code
function ajaxFunction(){
	var ajaxRequest;  // The variable that makes Ajax possible!
		// Opera 8.0+, Firefox, Safari
		ajaxRequest = new XMLHttpRequest();
	} catch (e){
		// Internet Explorer Browsers
			ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
		} catch (e) {
				ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
			} catch (e){
				// Something went wrong
				alert("Your browser broke!");
				return false;
	// Create a function that will receive data sent from the server
	ajaxRequest.onreadystatechange = function(){
		if(ajaxRequest.readyState == 4){
			document.myForm.time.value = ajaxRequest.responseText;
	var age = document.getElementById('age').value;
	var wpm = document.getElementById('wpm').value;
	var sex = document.getElementById('sex').value;
	var queryString = "?age=" + age + "&wpm=" + wpm + "&sex=" + sex;"GET", "ajax-example.php" + queryString, true);


<form name='myForm'>
Max Age: <input type='text' id='age' /> <br />
Max WPM: <input type='text' id='wpm' />
<br />
Sex: <select id='sex'>
<input type='button' onclick='ajaxFunction()' value='Query MySQL' />

If the new Javascript code is foreign to you, be sure to check out our lesson on Javascript's getElementById Function.

With our new Javascript code
var queryString = "?age=" + age + "&wpm=" + wpm + "&sex=" + sex;
we have built a query string to pass along the information from our HTML form to our PHP script.

Ajax - Passing Variables via Query String

A query string is a way of passing information by appending data onto the URL. You may have often seen it on the web, it's all the information that appears after a question mark "?". When you submit a form using GET it builds a query string, all we're doing here is manually building our own.


The left side of the equals operator is the variable name and the right side is the variable's value. Also, each variable is separated with an ampersand &.

For example, if we wanted to send the variables age, sex, and wpm with values 20, f, 40 to our PHP script ajax-example.php then our URL would look like:


Now we need to build a new PHP script to take these variables and run a MySQL query for us.

Ajax - Create ajax-example.php Script

We already changed the destination URL in our method, now we need to make a script to grab those variables from the query string and execute a MySQL Query. We're also going to use a special function mysql_real_escape_string to prevent any harmful user input from doing something they aren't supposed to (we're going to take steps against SQL Injection).

ajax-example.php Code:

$dbhost = "localhost";
$dbuser = "dbusername";
$dbpass = "dbpassword";
$dbname = "dbname";
	//Connect to MySQL Server
mysql_connect($dbhost, $dbuser, $dbpass);
	//Select Database
mysql_select_db($dbname) or die(mysql_error());
	// Retrieve data from Query String
$age = $_GET['age'];
$sex = $_GET['sex'];
$wpm = $_GET['wpm'];
	// Escape User Input to help prevent SQL Injection
$age = mysql_real_escape_string($age);
$sex = mysql_real_escape_string($sex);
$wpm = mysql_real_escape_string($wpm);
	//build query
$query = "SELECT * FROM ajax_example WHERE ae_sex = '$sex'";
	$query .= " AND ae_age <= $age";
	$query .= " AND ae_wpm <= $wpm";
	//Execute query
$qry_result = mysql_query($query) or die(mysql_error());

	//Build Result String
$display_string = "<table>";
$display_string .= "<tr>";
$display_string .= "<th>Name</th>";
$display_string .= "<th>Age</th>";
$display_string .= "<th>Sex</th>";
$display_string .= "<th>WPM</th>";
$display_string .= "</tr>";

	// Insert a new row in the table for each person returned
while($row = mysql_fetch_array($qry_result)){
	$display_string .= "<tr>";
	$display_string .= "<td>$row[ae_name]</td>";
	$display_string .= "<td>$row[ae_age]</td>";
	$display_string .= "<td>$row[ae_sex]</td>";
	$display_string .= "<td>$row[ae_wpm]</td>";
	$display_string .= "</tr>";
echo "Query: " . $query . "<br />";
$display_string .= "</table>";
echo $display_string;

The Next Step - Updating order.html

We have completed our initial order.html and ajax-example.php setup, now we just need our order.html page to update correctly when a query is returned. We'll be using a couple advanced Javascript functions to update a segment of order.html with the MySQL result display_string.

Bookmark and Share

Found Something Wrong in this Lesson?

Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time!

Advertise Here

More Tutorials!
Microsoft Office Tutorials Artist Tutorials