In this article, we will access information from MySQL database tables using AJAX. Assuming we have a table named products containing information of products of different categories. The categories may be Mobile, Camera, Book etc. and are displayed on the screen in the form of hyperlinks. On selecting a category, all the products under that category will be displayed. Like, if Camera hyperlink is selected, all the cameras in products table will be displayed. The structure of the table is shown in below given figure.
Let’s place an asynchronous request to access the desired data from the database on server.
For this article, we will be creating three files :
- ajaxform.php – PHP script that will display two hyperlinks representing the two category of products like Camera and Mobile
- ajaxform.js – JavaScript file that create XMLHttpReqest object and that send the category selected by the user to the server asynchronously
- ajaxdata.php – Server side PHP script to display information of the products falling under selected category as a server response
Coding for “Accessing Database tables using AJAX”
The code in ajaxform.php file is as under :
ajaxform.php
<head>
<script language=”JavaScript” type=”text/JavaScript” src=”ajaxform.js” >
</script>
</head>
<body>
<div id=”links”>
<a href=”javascript:showdata(‘Camera’)”>Camera</a> <a href=”javascript:showdata(‘Mobile’)”>Mobile</a>
</div>
<div id=”content”>
</div>
</body>
</html>
Above program includes the JavaScript file : ajaxform.js (so that methods in it can be invoked from this web page). It also contains two <div> elements with id : links and content respectively. The two hyperlinks : Camera and Mobile are placed inside the links element and the content element is left for displaying server response. if either of the links is selected, a JavaScript method : showdata() is executed and the name of the link selected (Category of item selected) is passed to it as argument.
The code in ajaxform.js file is as under :
ajaxform.js
function makeRequestObject(){
var xmlhttp=false;
try {
xmlhttp = new ActiveXObject(‘Msxml2.XMLHTTP’);
} catch (e) {
try {
xmlhttp = new
ActiveXObject(‘Microsoft.XMLHTTP’);
} catch (E) {
xmlhttp = false;
}
}
if (!xmlhttp && typeof XMLHttpRequest!=’undefined’) {
xmlhttp = new XMLHttpRequest();
}
return xmlhttp;
}
function showdata(cat)
{
var xmlhttp=makeRequestObject();
var file = ‘ajaxdata.php?Category=’;
xmlhttp.open(‘GET’, file + cat, true);
xmlhttp.onreadystatechange=function() {
if (xmlhttp.readyState==4) {
document.getElementById(‘content’).innerHTML = xmlhttp.responseText;
}
}
xmlhttp.send(null);
return;
}
Above program is making an XMLHttpRequest object for sending request to the server (by GET method) for the file ajaxdata.php. The category of product selected by the user (through hyperlink) is passed to the ajaxdata.php file. The category is retrieved in ajaxdata.php file using $_GET array.
The state of the request is checked. If the value of readyState property becomes 4 (meaning the request is complete), the response is then retrieved from the response stream and is assigned to the innerHTML property (property used to display results) of the element with id : “content” .
Since, the response generated by the server is based on the execution of the file : ajaxdata.php.
Lets analyze what it returns
ajaxdata.php
1. <?php
2. mysql_connect(“localhost”, “root”, “mce”);
3. mysql_select_db(“shopping”);
4. $cat = $_GET[“Category”];
5. $sql = “SELECT * FROM products WHERE Category like ‘$cat'”;
6. $query = mysql_query($sql) or die(mysql_error());
7. echo ‘<table border=”1″>’;
8. echo ‘<th>Item Code</th><th>Name of Item</th><th>Description</th><th>Price</th>’;
9. while ($r = mysql_fetch_array($query)) {
10. echo ‘<tr><td>’ . $r[“item_code”] . ‘</td><td>’ . $r[“item_name”] . ‘</td><td>’ . $r[“description”] . ‘</td><td>’ . $r[“price”] . ‘</td></tr>’;
11. }
12. echo ‘</table>’;
13. ?>
Above program is connecting to the MySQL server with user id : “root” and password: “mce”. Then the database “shopping” is selected (as our products table is in this database). The Category of the item selected by the user is retrieved using $_Get array and stored in variable : $cat. Then, a SQL statement is executed to fetch all the rows from the products table with the category specified in $cat variable. The rows retrieved from the products table are then returned in the form of a table to be displayed on the client
Output of “Accessing Database tables using AJAX”
On executing the file ajaxform.php file, we find two hyperlinks on the screen each representing a category of product as shown in below given figure:
>
Output displaying Hyperlinks
If Camera link is selected, all the products under Camera category will be displayed as shown in below given figure.
Information of all items under Camera category if selected
If Mobile link is selected, all the products under Mobile category are displayed as shown in below given figure.
All products under mobile category are shown if selected
