This tutorial is helpful to fetch data from the database using ajax in PHP. Here you will learn it with a basic example. If you create ajax based project and you also want to developed everything without reloading the page then this example is best for you. Once you read this complete tutorial, you will be able to quickly display other different & complex data.
Contents
Fetch Data From Database using ajax in PHP
I have shared this tutorial with a basic example. This example will display some data like full name, email address, city & country on clicking a button. After learning this example, you can easily implement it in your project.
Read Also –
Insert data into a database using ajax in PHP
How to update data using ajax in PHP
Before Getting Started with this Script, You must configure the following basic requirement.
Create your own project folder. Otherwise, you can use the following folder structure for testing purposes.
codingstatus.com/ |__data-list.php |__database.php |__ajax-script.js |__backend-script.php
Create a table usertable in codingstatus database and Insert Data. if you don’t know how to insert data using ajax. you can get it on this blog site.
1. Connect PHP to MySQL Database
First of all, you need to connect PHP to the MYSQL database.
File Name – database.php
<?php
$hostname = "localhost";
$username = "root";
$password = "";
$databasename = "codingstatus";
// Create connection
$conn = mysqli_connect($hostname, $username, $password,$databasename);
// Check connection
if (!$conn) {
die("Unable to Connect database: " . mysqli_connect_error());
}
?>
2. Create HTML Button to display data on click
Configure the following steps –
- Include jquery CDN to execute jquery ajax code.
- Include external ajax script file
ajax-script.js. This file contains a custom ajax code to display data without reloading the page. Don’t worry, you will get a complete guide about this file in the next step - Create an HTML button with
id="showData". Ajax script will execute on click this button. - Create a
divwithid="table-container". This div will use to show data while you click the button.
File Name – data-list.php
<!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width, initial-scale=1"> </head> <body> <button id="showData">Show User Data</button> <div id="table-container"></div> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script> <script type="text/javascript" src="ajax-script.js"></script> </body> </html>
3. Display data without reloading the page using ajax
You have to configure the following steps to display data without reloading the page –
- First of all, apply click event on the HTML button with id
#showData. - Send
GETrequest to get data from PHP code. - Declare URL
backend-script.php. This URL contains PHP code to fetch data from the database. Don’t worry, It will explain in the next step. - Display data in
divwith id#table-container.
File Name – ajax-script.js
$(document).on('click','#showData',function(e){
$.ajax({
type: "GET",
url: "backend-script.php",
dataType: "html",
success: function(data){
$("#table-container").html(data);
}
});
});
4. Fetch Data From MySQL table using PHP
To fetch data from the MySQL database, configure the following steps –
- First, Include database connection file
database.php - Assign connection variable
$connto a new variable$db - Create a custom function
fetch_data(). This function will return data to fetch from the database. - Then call
fetch_data()and assign it to a new variable$fetchData. - Also, Create another custom function
show_data($fetchData). This function returns data with an HTML table. - Call
show_data($fetchData). This function accepts a parameter to get fetched data.
File Name – backend-script.php
<?php
include("database.php");
$db=$conn;
// fetch query
function fetch_data(){
global $db;
$query="SELECT * from usertable ORDER BY id DESC";
$exec=mysqli_query($db, $query);
if(mysqli_num_rows($exec)>0){
$row= mysqli_fetch_all($exec, MYSQLI_ASSOC);
return $row;
}else{
return $row=[];
}
}
$fetchData= fetch_data();
show_data($fetchData);
function show_data($fetchData){
echo '<table border="1">
<tr>
<th>S.N</th>
<th>Full Name</th>
<th>Email Address</th>
<th>City</th>
<th>Country</th>
<th>Edit</th>
<th>Delete</th>
</tr>';
if(count($fetchData)>0){
$sn=1;
foreach($fetchData as $data){
echo "<tr>
<td>".$sn."</td>
<td>".$data['fullName']."</td>
<td>".$data['emailAddress']."</td>
<td>".$data['city']."</td>
<td>".$data['country']."</td>
<td><a href='crud-form.php?edit=".$data['id']."'>Edit</a></td>
<td><a href='crud-form.php?delete=".$data['id']."'>Delete</a></td>
</tr>";
$sn++;
}
}else{
echo "<tr>
<td colspan='7'>No Data Found</td>
</tr>";
}
echo "</table>";
}
?>
Tutorial Summary
Dear Developers, I hope that you have got the above Ajax Script. Now, you are able to Fetch Data From Database Using Ajax in PHP.
If you have any queries related to web technology programming, Ask me through the below comment box. Even you can suggest the coding topics for sharing tutorials.
I regularly share my coding knowledge with you. So, you should continue to visit my blog and share this tutorial with your friends.
Thanks for giving the time to this tutorial.
Hello. Can I fetch data from Database using Ajax in PHP and MySQL without creating HTML button to display data On-Click?
You can apply put load event instead of click and body tag instaed of button id like .on(‘load’,’body’,function(e){})
thanks for nice script using ajax
how can i give row limit per page for the above. Like 20 rows in a page and click next for the next 20 rows and pagewise selection?
It will be share in another post soon