PHP Example AJAX and MySQL

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:

   1. Calls the GetXmlHttpObject() function to create an XMLHTTP object
   2. Defines an URL (filename) to send to the server
   3. Adds a parameter (q) to the URL with the content of the drop-down box
   4. Adds a random number to prevent the server from using a cached file
   5. Each time the readyState property changes, the stateChanged() function will be executed
   6. Opens the XMLHTTP object with the given URL
   7. 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:

$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:

1. PHP opens a connection to a MySQL server
2. The correct person is found
3. An HTML table is created, and filled with data, and sent back to the "txtHint" placeholder