Ajax and PHP with SQL

Tuesday, July 31, 2012

In this article, we are going to look at how to implement an Ajax solution that uses a PHP page to pull data from a back-end database. In this tutorial, we have a table stored in a MySQL database. You can replace the PHP code on this page with any other server-side scripting languages that you are familiar with such as ASP or ASP.NET. You can refer to the other tutorials in this series for more help using a different server-side scripting language. In addition, the back-end data source does not have to be MySQL. You can modify the database connection in the example for accessing other database platforms. The concept remains the same.


ajax php

HTML Example

<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
     function showEmployee(str) {
        if (str==""){
            document.getElementById("div1").innerHTML="Select an Employee for more details!";
            return;
        }
        var xhr = false;
        if (window.XMLHttpRequest) {
            // IE7+, Firefox, Chrome, Opera, Safari
            xhr = new XMLHttpRequest();
        } 
        else {
            // IE5/IE6
            xhr = new ActiveXObject("Microsoft.XMLHTTP");
        }
        if (xhr) {
            xhr.onreadystatechange = function () {
                if (xhr.readyState == 4 && xhr.status == 200) {
                    document.getElementById("div1").innerHTML = xhr.responseText;
                }
            }
            xhr.open("GET", "/demo/ajax_dbquery.php?q="+str, true);
            xhr.send(null);
        }
    }
</script>
</head>
<body>
<div>
    <select name="employees" onchange="showEmployee(this.value)">
        <option value="">Select an Employee:</option>
        <option value="3">Frank Ford</option>
        <option value="1">John Smith</option>
        <option value="4">Lisa Stark</option>
        <option value="2">Sally Smart</option>
    </select>
    <div id="div1">Select an Employee for more details!</div>
</div>
</body>
</html>


PHP Example

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>Ajax and PHP</title>
</head>
<body>
<table>
<?php
header("Cache-Control: no-cache, no-store, must-revalidate");
header("Pragma: no-cache");
header("Expires: Sat, 14 Jan 2012 01:00:00 GMT");
$hostname="dbserver.com";
$username="dbUserName";
$password="dbPassword";
$dbname="databaseName";
$usertable="employees";
$field1 = "empName";
$field2 = "empTitle";
$field3 = "empOffice";

$conn = mysql_connect($hostname,$username, $password);
if (!$conn) {
	die('Could not connect: ' . mysql_error());
}

mysql_select_db($dbname);

$query = "SELECT * FROM $usertable WHERE empID = '" .$_GET['q']."'";

$result = mysql_query($query);

if($result){
	while($row = mysql_fetch_array($result)){
		echo "<tr><td style='width:100px;'>Name:</td><td>".$row["$field1"]."</td></tr>";
		echo "<tr><td style='width:100px;'>Title:</td><td>".$row["$field2"]."</td></tr>";
		echo "<tr><td style='width:100px;'>Office:</td><td>".$row["$field3"]."</td></tr>";
	}
}
mysql_close($conn);
?> 
</table>
</body>
</html>


In this demo, we use a front-end HTML web page that loads data from a PHP (.php) page via Ajax. When a user selects an item in the dropdown list, the selection is sent to the PHP page using Ajax and the receiving page uses the information passed in the query string. The value of the query string parameter is sent to the database and the results are sent back to the HTML page on the XmlHttpResponse.responseText property.

Demo


Select an Employee for more details!


Did you find the page informational and useful? Share it using one of your favorite social sites.

Recommended Books & Training Resources

Ajax for Dummies Bulletproof Ajax The Complete Reference Ajax