Fetch Information From Database using PHP/AJAX/MySQL

In this tutorial, we will see how to make Ajax work with PHP and MySQL. We will create a small web application which will fetch the information from Database using PHP and Ajax.

Demo | Download Code

In the example, when a user selects any name in the dropdown list, a function called “showUser()” is executed as mentioned in index.php file. This function is triggered by an onchange event.

First, to make it convenient, we will create a new PHP file (config.php) for database configuration that holds all configured parameters such as DB_NAME, DB_USER, DB_PASSWORD and DB_HOST with proper exception handling.

config.php

<?php
define('DB_NAME', 'techhyme_testing');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');

$database = DB_NAME;
$hostname = DB_HOST;
$user = DB_USER;
$password = DB_PASSWORD;
global $conn;

$conn = new PDO("mysql:host=$hostname;dbname=$database", $user, $password);
try
{
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
echo "Connection failed: " . $e->getMessage();
}

?>

index.php

First, check if person is selected.

If no person is selected (str == “”), clear the content of txtHint and exit the function.

If a person is selected, it will perform the following things:

Create an XMLHttpRequest object
Create the function to be executed when the server response is ready
Send the request off to a file on the server
Notice that a parameter (id) is added to the URL (with the content of the dropdown list)

<html>
<head>
<script>
function showUser(str) {
if (str == "") {
document.getElementById("txtHint").innerHTML = "";
return;
} else {
var xmlhttp = new XMLHttpRequest();
xmlhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
document.getElementById("txtHint").innerHTML = this.responseText;
}
};
xmlhttp.open("GET","users.php?id="+str,true);
xmlhttp.send();
}
}
</script>
</head>
<body>
<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Choose Person:</option>
<option value="1">Clara Redman</option>
<option value="2">Fiona Graham</option>
<option value="3">Vickey Tammaro</option>
<option value="4">Kelley Allen</option>
</select>
</form>
<br>
<div id="txtHint"><b>Person info will be listed here...</b></div>

</body>
</html>

users.php

This page on the server called by the JavaScript above is a PHP file called “users.php“. The source code in “users.php” runs a query against a MySQL database (PDO connection), and returns the result in an HTML table as shown below:

When the query is sent from the JS to the PHP file, the following things happen:

PHP opens a connection to a MySQL server (config.php has already been included)
The correct person name is found
An HTML table is created and filled with data, and further sent back to the “txtHint” placeholder

<!DOCTYPE html>
<html>
<head>
<style>
table {width: 100%; border-collapse: collapse; }
table, td, th {border: 1px solid black; padding: 5px; }
th {text-align: left; }
.container{width: 1000px; margin-top: 10px; padding: 10px;}
</style>
</head>
<body>
<?php
error_reporting(0);
include('config.php');
$getid=htmlspecialchars($_GET['id']);
?>
<div class="container">
<table>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Country</th>
<th>Date</th>
</tr>
<?php
$query = "SELECT * FROM users WHERE id=? AND deleted='0'";
$stmt = $conn->prepare($query);
$stmt->execute(array($getid));
while($row=$stmt->fetch(PDO::FETCH_ASSOC)){ ?>
<tr>
<td><?php echo $row['firstname']; ?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['age']; ?></td>
<td><?php echo $row['country']; ?></td>
<td><?php echo $row['datetime']; ?></td>
</tr>
<?php } ?>
</table>
</div>
</body>
</html>

db.sql – The database table we use in this example looks like this: