To fetch data from the database in PHP using jQuery ajax. In this tutorial, you will learn how to fetch and show data from a database in PHP using ajax jQuery.
In this post, we will create a customer list and add view button in this customer list. After that, will fetch data from the database on view button click in PHP. And display data in the web page without reloading the whole web page using jQuery ajax.
How to Retrieve and Display Data From Database using jQuery Ajax without Page Refresh
Use the following steps to fetch/retrieve and show data from MySQL database in PHP using ajax jQuery without reloading the web page:
- Create Database And Table
- mydbCon.php – Connecting to MySQL database
- customers.php – Fetch All Customers from the database
- ajax-fetch-record.php – Fetch/Retrieve and Display Using jQuery Ajax Without Reload Page
1 – Create Database And Table
First of all, navigate to your phpmyadmin panel and create database and table using the following sql queries:
CREATE DATABASE my_db;
CREATE TABLE `customers` (
`id` int(10) UNSIGNED NOT NULL,
`fname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`lname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
2 – Connecting to MySQL database
In this step, you will create a file name mydbCon.php and update the below code into your file.
The following code is used to create a MySQL database connection in PHP. And also, you can use connecting database PHP code while fetch, insert, update or delete records from MySQL database with and without ajax:
<?php
$host='localhost';
$username='root';
$password='';
$dbname = "my_db";
$conn=mysqli_connect($host,$username,$password,"$dbname");
if(!$conn)
{
die('Could not Connect MySql Server:' .mysql_error());
}
?>
3 – Fetch List data from the database
In this step, display data in an HTML table.
So, create customers.php file and add the following code into customers.php file:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Code to Fetch All Data from MySQL Database and Display in html Table</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" >
<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
</head>
<body>
<div class="container mt-2">
<div class="page-header">
<h2>Customers List</h2>
</div>
<div class="row">
<div class="col-md-8">
<table class="table">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">First</th>
<th scope="col">Last</th>
<th scope="col">Email</th>
<th scope="col">Action</th>
</tr>
</thead>
<tbody>
<?php
include 'mydbCon.php';
$query="select * from customers limit 200"; // Fetch all the data from the table customers
$result=mysqli_query($dbCon,$query);
?>
<?php if ($result->num_rows > 0): ?>
<?php while($array=mysqli_fetch_row($result)): ?>
<tr>
<th scope="row"><?php echo $array[0];?></th>
<td><?php echo $array[1];?></td>
<td><?php echo $array[2];?></td>
<td><?php echo $array[3];?></td>
<td>
<a href="javascript:void(0)" class="btn btn-primary view" data-id="<?php echo $array[0];?>">View</a>
</tr>
<?php endwhile; ?>
<?php else: ?>
<tr>
<td colspan="3" rowspan="1" headers="">No Data Found</td>
</tr>
<?php endif; ?>
<?php mysqli_free_result($result); ?>
</tbody>
</table>
</div>
<div class="col-md-4">
<span id="fname"></span><br>
<span id="lname"></span><br>
<span id="email"></span><br>
</div>
</div>
</div>
<script type="text/javascript">
$(document).ready(function($){
$('body').on('click', '.view', function () {
var id = $(this).data('id');
// ajax
$.ajax({
type:"POST",
url: "ajax-fetch-record.php",
data: { id: id },
dataType: 'json',
success: function(res){
$('#fname').html(res.fname);
$('#lname').html(res.lname);
$('#email').html(res.email);
}
});
});
});
</script>
</body>
</html>
4 – Fetch/Retrieve and Display Using jQuery Ajax Without Reload Page
In this step, fetch data from the database using ajax request. So create ajax-fetch-record.php file and fetch and display data from the database using ajax without refresh or reload the whole web page.
So, update the following code into ajax-fetch-record.php file:
<?php
include "mydbCon.php";
$id = $_POST['id'];
$query="SELECT * from customers WHERE id = '" . $id . "'";
$result = mysqli_query($dbCon,$query);
$cust = mysqli_fetch_array($result);
if($cust) {
echo json_encode($cust);
} else {
echo "Error: " . $sql . "" . mysqli_error($dbCon);
}
?>
The how to fetch data from database in php using ajax and jquery will be look like in following image:
Conclusion
To fetch the data from the MySQL database in PHP using jQuery ajax. Here you have learned how to fetch data from MySQL table in PHP using jQuery ajax without reload or refresh the whole web page.
If you have any questions or thoughts to share, use the comment form below to reach us.
