JQuery Ajax comes in handy when you want to publish or fetch data from a PHP script without refreshing the page. Usually, you return an Ajax call string to update part of a web page.
However, sometimes data for an object or array needs to be fetched from a PHP file to return values in different ranges. With jQuery’s $.ajax() method, you can get the JSON data from a file and set it in an HTML element.
In this tutorial, we will show you how to process Ajax queries with jQuery and call a PHP script that returns JSON data. PHP script fetch data from MySQL database and returns JSON data to Ajax. The data returned is parsed with JavaScript and assigns a value to a specific element.
Create Mysql Database and Table
In the first step we need to create a database and tables, so here I have created a database of tables “webscodex” and ”customers” with identifiers and columns with names. You simply create a Users table as described in the SQL query.
SQL Query
-- -- Database: `webscodex` -- Table structure for table `customers` -- CREATE TABLE `customers` ( `id` int(100) NOT NULL PRIMARY KEY AUTO_INCREAMENT, `name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `username` varchar(100) NOT NULL, `dob` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Create Database Connectivity
config.php
<?php
$sernamename = "localhost";
$dbUsername = "root";
$dbPassword = "";
$databasename = "blog_database";
// Create database connection
$con = mysqli_connect($sernamename, $dbUsername, $dbPassword, $databasename);
// Check connection
if ($con->connect_error) {
die("Connection failed". $con->connect_error);
}
?>
Create HTML File
First, the select field is displayed in which the customer name whose data you want to retrieve is entered. change customer name than triggers an Ajax request and the corresponding customer data is displayed below the table form.
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>How Fetch a single record in a JSON with PHP mysql using jquery ajax</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>
<div class="jumbotron text-center">
<h3>How Fetch a single record in a JSON with PHP mysql using jquery ajax</h3>
</div>
<div class="container">
<div class="row">
<div class="col-sm-3"></div>
<div class="col-sm-6">
<form action="" method="POST">
<div class="form-group">
<select name="name" id="name" class="form-control">
<option value="">Select Name</option>
<?php
// Include database connection file
include 'config.php';
$query = "SELECT * FROM customers";
$result = $con->query($query);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) { ?>
<option value="<?php echo $row['id'] ?>"><?php echo ucwords($row['name']) ?></option>
<?php } } ?>
</select>
</div>
</form>
</div>
</div>
</div>
<div class="container" id="tableContain" style="display: none;">
<div class="row">
<table class="table">
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Username</th>
<th>Date of Birth</th>
</tr>
<tbody>
<tr>
<td><span id="id"></span></td>
<td><span id="cname"></span></td>
<td><span id="email"></span></td>
<td><span id="username"></span></td>
<td><span id="dob"></span></td>
</tr>
</tbody>
</table>
</div>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</body>
</html>
Javascript Code (Jquery & AJAX)
The $.ajax() method executes an Ajax query and publishes the customer name in a PHP file to get the customer data from the database. If the request is successful, the data is returned in JSON format. Parsed JSON records for the contents of the respective elements.
<script type="text/javascript">
$(document).ready(function(){
$("#name").change(function(){
var id = $("#name").val();
if (id !=="") {
$.ajax({
url : "get_data.php",
type: "POST",
cache: false,
data:{id:id},
success:function(result){
var data = JSON.parse(result);
$("#tableContain").show();
$("#id").html(data.id);
$("#cname").html(data.name);
$("#email").html(data.email);
$("#username").html(data.username);
$("#dob").html(data.dob);
},
});
}else{
$("#tableContain").hide();
}
});
});
</script>
Output
PHP Script(get_data.php)
This PHP script is call by an Ajax request. Based on the customer ID, the details are retrieved from the database using PHP and MySQL. Fetch data is returned in Ajax in JSON form.
get_data.php
<?php
//Include database connection file
include 'config.php';
if (isset($_POST['id']) && !empty($_POST['id'])) {
$id = $_POST['id'];
$query = "SELECT * FROM customers WHERE id = $id";
$result = $con->query($query);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$data = json_encode($row);
}
echo $data;
}else{
echo "No record found";
}
}
?>
You can always support by sharing on social media or recommending my blog to your friends and colleagues. If you have any suggestions or problems about this tutorial, please comment on the form below.😊
