How to Fetch & Display JSON Data in Table using AJAX in PHP & MySQL

Tech Area
October 8, 2023

In this tutorial, We will learn how to fetch JSON data from database and display in table using AJAX in PHP and MySQL.

Files used in this tutorial:

1- connection.php (database connection file)

2- index.php (table and AJAX script)

3- show-data.php (fetch JSON data from database)

Below are the step by step process of how to fetch and display JSON data in table using Jquery AJAX.

Step 1: Create a Database connection

In this step, create a new file connection.php to create database connection.

connection.php

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "college_db";
$connection = mysqli_connect("$server","$username","$password");
$select_db = mysqli_select_db($connection, $database);
if(!$select_db)
{
	echo("connection terminated");
}
?>

Step 2: Create index.php

In this step, create a new file index.php. This is the main file used to implement AJAX to fetch json data.

This screenshot shows the table heading.

index.php

<html>  
<head>  
    <title>Registration Form</title>  
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
</head>
<body>  
    <div class="container">  
    <h2 class="text-center">Display Data using Jquery AJAX</h2>
   <br>
   <button id="showdata" class="btn btn-primary">Show Data</button>
   <table class="table table-bordered">
    <thead>
      <th>Id</th>
      <th>Name</th>
      <th>Email</th>
      <th>Phone</th>
      <th>Address</th>
    </thead>
    <tbody id="result">
    </tbody>
   </table>
  </div>
 </body>  
</html>

Now use jquery AJAX script.

<script src="https://code.jquery.com/jquery-3.7.1.js"></script>
<script type="text/javascript">
  $(document).ready(function(){
      $("#showdata").on("click", function(e){
      $.ajax({
        url: 'show-data.php',
        method: 'post',
        success: function(result){
          //console.log(result);
          var jsondata = $.parseJSON(result);
          $.each(jsondata, function(key, value){
          $("#result").append('<tr>'+
           '<td>'+value['id']+'</td>\
           <td>'+value['name']+'</td>\
           <td>'+value['email']+'</td>\
           <td>'+value['phone']+'</td>\
           <td>'+value['address']+'</td>\
            </tr>');
        });
        }
      });
    })
  });
</script>

Step 3: Create new file for fetch JSON data

In this step, create a new file show-data.php. This file used to fetch json data from database.

show-data.php

<?php
include('connection.php');
$res_arr = [];
$fetch_query = mysqli_query($connection, "select * from tbl_registration");
$row = mysqli_num_rows($fetch_query);
if($row>0)
{
	foreach($fetch_query as $res)
	{
		array_push($res_arr, $res);
	}
	echo json_encode($res_arr);
}
?>

Output