Fetch Information From Database using PHP/AJAX/MySQL

PHP MySQL Ajax Demo Techhyme

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:

database_preview_techhyme

Also Read:

For any doubt or query, you can drop a mail us at contact@techhyme.com.

Related Posts

Python Variables Store Strings Techhyme

How to Store Strings in Variables in Python

In the world of programming, Python stands out as one of the most popular and versatile languages. One of its fundamental concepts is the use of variables,…

Active Connections Kali Linux Techhyme

How To Check Active Connections in Kali Linux

Kali Linux, known for its prowess in cybersecurity and ethical hacking, provides a powerful command-line tool called `netstat` for monitoring active network connections. This tool is indispensable…

Kali linux system information commands techhyme

Top 11 System Information Commands in Kali Linux

In the realm of ethical hacking and penetration testing, the post-exploitation phase is a critical juncture where an attacker seeks to establish and maintain control over a…

PHP operators Expressions Techhyme

Top 9 Popular PHP Operators and Expressions

In the world of PHP programming, operators play a crucial role in manipulating data and performing various operations. An operator in PHP can be described as a…

PHP Arrays Techhyme

Declaring an Array in PHP: A Comprehensive Guide

Arrays are one of the most versatile and widely used data structures in programming. They allow developers to store and organize multiple values under a single variable…

Node.js Object Properties Techhyme

Explanation of the Various fs.Stats Object Properties in Node.Js

When working with files and directories in Node.js, developers often need to gather information about these entities to perform various operations or make informed decisions. The `fs.Stats`…

Leave a Reply