AJAX is powerful and rather widespread, so it's not surprising some beginners might get the wrong idea that it is an independent programming language. Actually, AJAX stands for Asynchronous JavaScript And XML.
Basically, it requests data from a web server using an XMLHttpRequest object that is built into the browser. Then, it uses JavaScript and HTML DOM to use or display said data. It means that you don't need to reload the webpage to see updates.
MySQL is considered to be the most popular database management system in the world. The reasons it is so widespread are simple: it is free, open-source and runs on almost any system, including but not limited to Windows, Linux, and Mac OS.
Using MySQL, AJAX, and PHP together will let you effectively communicate with a database and display interactive data. Let's see how it's done, using a simple PHP AJAX example.
Contents
PHP - AJAX and MySQL: Main Tips
- MySQL is extremely popular in the world for storing data. It's reliable, free, and runs on any system.
- AJAX acts as a communicator that lets the browser and server communicate behind the scenes.
Example Of a MySQL Database
In this PHP AJAX tutorial, we will explain how to access and sort data using a AJAX MySQL connection. But before we try, we have to first have a database to access. For the sake of simplicity and learning, we will keep it short for now. Here we have a table that looks like this:
| id | FirstName | LastName | Age | Birthplace | Occupation |
|---|---|---|---|---|---|
| 1 | Mark | Dooley | 32 | London | Programmer |
| 2 | Lewis | Kirkbride | 25 | New York | Artist |
| 3 | Jack | Lee | 19 | California | Firefighter |
| 4 | Mary | Jefferson | 46 | Quebec | Army officer |
Making of Web Application: HTML Example
When trying to build a PHP AJAX example web application, we first have to make a dropdown list of names. It will then access and use information directly from the chosen database. When someone selects one particular person that is on the list, their personal information will be displayed in a table.
To accomplish this, upon selecting a person from the dropdown, the show_user() function is called, which is triggered by the onchange event.
Below is the required HTML:
<html>
<head>
<script>
function showUser(str) {
if (str == '') {
document.getElementById("txt_hint").innerHTML = "";
return;
} else {
if (window.XMLHttpRequest) {
// script for browser version above IE 7 and the other popular browsers (newer browsers)
xmlhttp = new XMLHttpRequest();
} else {
// script for the IE 5 and 6 browsers (older versions)
xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
// get the element in which we will use as a placeholder and space for table
document.getElementById("txt_hint").innerHTML = this.responseText;
}
};
xmlhttp.open("GET", "getuser.php?q="+str, true);
xmlhttp.send();
}
}
</script>
</head>
<body>
<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Select a person:</option>
<option value="1">Mark Dooley</option>
<option value="2">Lewis Kirkbride</option>
<option value="3">Jack Lee</option>
<option value="4">Mary Jefferson</option>
</select>
</form>
<br>
<div id="txt_hint"><b>This is where info about the person is displayed.</b></div>
</body>
</html>Example Explained
Firstly, we should check in the PHP AJAX example if anyone is selected at all. If it is true what we suspected and a selection has not been made (str == ""), we clear the txt_hint placeholder and exit the function.
However, if a particular person is selected, this is what happens in our PHP AJAX example:
- First, we create an XMLHttpRequest object to make a server request.
- Then we create a function for when the server response has been prepared.
- Finally, we make a request to the server that holds our database.
Note: Notice how a parameter
qwith the dropdown selection is added to the URL.
Fill HTML Table With Data: PHP Example
The page that the Javascript above calls is get_user.php.
The source code found inside the get_user.php file will run a query against our MySQL database, then return the result. We will see it formatted as an HTML table:
<!DOCTYPE html>
<html>
<body>
<?php
$q = intval($_GET['q']);
$conn = mysqli_connect('localhost', 'johny', 'abcde12345', 'db');
if (!$conn) {
die('Could not connect: ' . mysqli_error($con));
}
mysqli_select_db($conn, "ajax_");
$sql = "SELECT * FROM users WHERE id = '".$q."'";
$result = mysqli_query($conn,$sql);
echo "<table>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Age</th>
<th>Birthplace</th>
<th>Occupation</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['FirstName'] . "</td>";
echo "<td>" . $row['LastName'] . "</td>";
echo "<td>" . $row['Age'] . "</td>";
echo "<td>" . $row['Birthplace'] . "</td>";
echo "<td>" . $row['Occupation'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($conn);
?>
</body>
</html>Comments on the Example
The following happens after calling the PHP script in get_user.php
- An AJAX MySQL connection is established.
- A corresponding user is found.
- The script creates and fills the HTML table with data, which is then sent back to the
txt_hintplaceholder.
Carefully study the AJAX PHP examples and HTML codes portrayed and try to make a similar one yourself. Not only you will find it rather easy, you will find a whole new world of functionality that a combination of MySQL, AJAX and PHP provides.
PHP - AJAX and MySQL: Summary
- AJAX is powerful tool used to create dynamic applincations. It establishes connection between a browser and a server.
- MySQL is the most popular database management system in the world due to its reliability, security and force.