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
How to Fetch Data From Database Using Ajax
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
Change URL without reloading the page using ajax
Load more data on infinite scroll using ajax
Before Getting Started 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
usertableincodingstatusdatabase 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 MYSQL database.
<?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 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 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>
You can also use the following CSS code in the head section to the design the data table.
<style>
* {box-sizing: border-box;}
.user-detail {
height: 100vh;
border: 2px solid #f1f1f1;
padding: 16px;
background-color: white;
width: 30%;}
input{
width: 100%;
padding: 15px;
margin: 5px 0 22px 0;
display: inline-block;
border: none;
background: #f1f1f1;}
input[type=text]:focus, input[type=password]:focus {
background-color: #ddd;
outline: none;}
button[type=submit] {
background-color: #434140;
color: #ffffff;
padding: 10px 20px;
margin: 8px 0;
border: none;
cursor: pointer;
width: 100%;
opacity: 0.9;
font-size: 20px;}
label{
font-size: 18px;;}
button[type=submit]:hover {
background-color:#3d3c3c;}
.form-title a, .form-title h2{
display: inline-block;}
.form-title a{
text-decoration: none;
font-size: 20px;
background-color: green;
color: honeydew;
padding: 2px 10px;}
table, td, th {
border: 1px solid #ddd;
text-align: left;}
table {
border-collapse: collapse;
width: 50%;}
.table-data{
position: relative;
left:50px;
top:50px;}
th, td {
padding: 15px;
}
body{
overflow-x: hidden;}
.list-title a, .list-title h2{
display: inline-block;
margin: 0px 35px;}
.list-title a{
text-decoration: none;
font-size: 20px;
background-color: green;
color: honeydew;
padding: 2px 10px;}
</style>
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 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);
?>
<?php
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.
Leave a Reply