Fetch Data From Database using ajax in PHP

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.

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.

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 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 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 div with id="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 GET request 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 div with 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 $conn to 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.