How to Fetch Data From Database in PHP using Ajax

July 7, 2022July 7, 2022 Rabiya 0 Comments

To fetch data from the database in PHP using jQuery ajax.

How to fetch and display data from database in PHP using ajax.

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 Ajax without Page Refresh

If you want to fetch and display data from MySQL database in PHP using ajax 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 and Display Using 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 and Display Using 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:

fetch data from database in php and display in html table using ajax

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.