How to display JSON Data in DataTable using AJAX in PHP

Tech Area
April 13, 2024

In this post, We will discuss how to display JSON data in jQuery DataTable using AJAX in PHP and MySQL. If you want to show fetch json data from database and show in table form on web page, then you can achieve this task using jQuery DataTable plugin.

Files used in this tutorial:

1- connection.php (database connection file)

2- index.php (initialise datatable)

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

Below are the step by step process of how to display json data in jQuery DataTable using ajax in PHP and MySQL.

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 a file for initialise DataTable

Now create a new file index.php This is the main file used for initialise datatable.

index.php

First of all include necessary CSS and JavaScript library.

<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/2.0.3/css/dataTables.dataTables.min.css">
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script src="//cdn.datatables.net/2.0.3/js/dataTables.min.js"></script>

Now create body for Table.

<body>  
    <div class="container">    
   <br>
   <h2 class="text-center">Display JSON Data in DataTable using AJAX</h2>
   <br>
   <table class="table table-bordered" id="myTable">
    <thead>
      <th>Id</th>
      <th>Name</th>
      <th>Course</th>
      <th>Country</th>
    </thead>
   </table>
</div>
</body>

After this, create script for initialise datatable with ajax call.

<script>
    $(document).ready(function(){
      $('#myTable').DataTable({
        ajax:{
          url:'show-data.php',
          dataSrc:'',
        }
      });
    });
  </script>

Step 3: Create a file to fetch data

Now create a new file show-data.php This file used for fetch data from database in JSON.

show-data.php

<?php
include('connection.php');
$fetch_query = mysqli_query($connection, "select * from tbl_student");
$row = mysqli_num_rows($fetch_query);
if($row>0)
{
	$data = array();
	while($res = mysqli_fetch_array($fetch_query))
	{
		$res_array = array();
		$res_array[] = $res['id'];
		$res_array[] = $res['name'];
		$res_array[] = $res['course'];
		$res_array[] = $res['country'];
		$data[] = $res_array;
	}
}
echo json_encode($data);
?>

Source Code

Here is the full code that we have written for index.php.

<html>  
<head>  
    <title>JSON Data in DataTable</title>  
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css"/>  
    <link rel="stylesheet" href="//cdn.datatables.net/2.0.3/css/dataTables.dataTables.min.css">
</head>
<body>  
    <div class="container">    
   <br>
   <h2 class="text-center">Display JSON Data in DataTable using AJAX</h2>
   <br>
   <table class="table table-bordered" id="myTable">
    <thead>
      <th>Id</th>
      <th>Name</th>
      <th>Course</th>
      <th>Country</th>
    </thead>
   </table>
   <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
   <script src="//cdn.datatables.net/2.0.3/js/dataTables.min.js"></script>
   <script>
    $(document).ready(function(){
      $('#myTable').DataTable({
        ajax:{
          url:'show-data.php',
          dataSrc:'',
        }
      });
    });
  </script>
  </div>
 </body>  
</html>

Output