Php

Bootstrap 5 Datatable : Ajax Pagination with Search and Sort

in this tutorial, We’ll learn how to create a dynamic listing using bootstrap 5 based datatable with PHP and MySQL. I am using the jQuery AJAX method to get records from MySQL. I also integrated ajax based sorting and searching using jquery datatable.

jQuery DataTables is a very popular JavaScript library to convert simple HTML tables into the feature-rich grid.jQuery will help to get records from MySQL in an AJAX manner.

We will cover the following functionality in this Datatable Tutorial:

  • Load data from the MySQL Database using PHP
  • Ajax based sorting into Datatable
  • Ajax based searching into the Datatable
  • Ajax Based pagination

jQuery Ajax Datatable Listing Using PHP and MySQL

In this tutorial, You will learn how to implement Ajax-based DataTables Listing Records, Pagination, Sorting and Searching.

There are following files will participate in his tutorial:

  • index.php: The main entry file of the project.
  • action.php: This file is used to define all actions.
  • Employee.php: This file will contain all action methods.
  • partials/header.php: This file is the header file of the project and includes all css/js.

Created Header File

Created a header.php file under /partials folder and add the below code into this file. We’ll include all css and js files into the header.php file:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.0.1/css/bootstrap.min.css">
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.0/css/dataTables.bootstrap5.min.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.5.0/font/bootstrap-icons.css"> 
<script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.11.0/js/jquery.dataTables.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.11.0/js/dataTables.bootstrap5.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js"></script>
<script src="js/common.js"></script>

We’ll create common.js file later on in this tutorial.

Related Post

Create Datatable HTML Listing

Create a index.php file and added an HTML table element with CSS styling, we’ll also add header.php into the top of the index.php file.

<?php 
include('partials/header.php');
?>
<title>PHPFLOW.COM : Datatables Add Edit Delete with Ajax, PHP & MySQL</title>
<style>
    .btn-group-xs > .btn,
    .btn-xs {
        padding: 0.25rem 0.4rem;
        font-size: 0.875rem;
        line-height: 0.5;
        border-radius: 0.2rem;
    }
</style>

<div class="container">
    <h3>Ajax Datatables Listing With Sorting & Searching</h3>
    <div class="col-lg-10 col-md-10 col-sm-9 col-xs-12">
        <div class="panel-heading">
            <div class="row">
                <div class="col-md-10">
                    <button type="button" name="add" id="addEmployee" class="btn btn-success btn-xs"><i class="bi bi-plus-circle-fill"></i> Add Employee</button>
                </div>

                <div class="col-md-2 pull-left"></div>
            </div>
        </div>

        <table id="dt-employee" class="table table-bordered table-striped">
            <thead>
                <tr>
                    <th>ID</th>

                    <th>Name</th>

                    <th>Age</th>

                    <th>Action</th>
                </tr>
            </thead>
        </table>
    </div>
</div>

Create Actions

We’ll define all actions with method details and added below code into the action.php file:

include('Employee.php');
$emp = new Employee();
$action = isset($_POST['action']) && $_POST['action'] != '' ? $_POST['action'] : '';
switch ($action) {
 case "listEmployee":
  $emp->employeeList();
   break;
 default:
   echo "Action not found!";
  }
?>

Javascript file for AJAX Operation

We ll create common.js file into the js/ folder.

$(document).ready(function(){
var employeeData = $('#dt-employee').DataTable({
  "lengthChange": false,
  "processing":true,
  "serverSide":true,
  "order":[],
  "ajax":{
   url:"action.php",
   type:"POST",
   data:{action:'listEmployee'},
   dataType:"json"
  },
  "columnDefs":[
   {
    "targets":[0, 2],
    "orderable":false,
   },
  ],
  "pageLength": 10
 }); 
});

dt-employee is the HTML table id, where the jquery datatable functionality will apply.

Define Action Method for Listing Records

The action has been defined, Now Added below code into the Employee.php file to get all records based on action.

public function employeeList(){
  $where = $sqlTot = $sqlRec = "";

  if( !empty($_POST['search']['value']) ) {   
   $where .=" WHERE ";
   $where .=" ( employee_name LIKE '".$_POST['search']['value']."%' ";    
   $where .=" OR employee_salary LIKE '".$_POST['search']['value']."%' ";
 
   $where .=" OR employee_age LIKE '".$_POST['search']['value']."%' )";
  }

  // getting total number records without any search
  $sql = "SELECT * FROM ".$this-&gt;empTable." ";
  $sqlTot .= $sql;
  $sqlRec .= $sql;

  //concatenate search sql if value exist
  if(isset($where) &amp;&amp; $where != '') {
   $sqlTot .= $where;
   $sqlRec .= $where;
  }
  
  if(!empty($_POST["order"])){
   $sqlRec .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
  } else {
   $sqlRec .= 'ORDER BY id DESC ';
  }
  if($_POST["length"] != -1){
   $sqlRec .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
  }

  $queryRecords = mysqli_query($this-&gt;dbConnect, $sqlRec);
  
  $queryTot = mysqli_query($this-&gt;dbConnect, $sqlTot);
  $numRows = mysqli_num_rows($queryTot);
  
  $employeeData = array(); 
  while( $employee = mysqli_fetch_assoc($queryRecords) ) {  
   $empRows = array();   
   $empRows[] = $employee['id'];
   $empRows[] = ucfirst($employee['employee_name']);
   $empRows[] = $employee['employee_age'];      
   $empRows[] = '
<div class="btn-group" role="group" aria-label="Basic mixed styles example">
   <button type="button" name="update" id="'.$employee[" id"].'"="" class="btn btn-warning btn-xs update"><i class="bi bi-pencil-square"></i> Edit</button>
   <button type="button" name="delete" id="'.$employee[" id"].'"="" class="btn btn-danger btn-xs delete"><i class="bi bi-trash"></i> Delete</button></div>
';
   
   $employeeData[] = $empRows;
  }
  $output = array(
   "draw"    =&gt; intval($_POST["draw"]),
   "recordsTotal"   =&gt;  $numRows,
   "recordsFiltered"  =&gt;  $numRows,
   "data"       =&gt;  $employeeData
  );
  echo json_encode($output);
 }

Conclusion:

We have created an Html listing data using Ajax from MySQL. Also, Applied datatable on that table to create a full feature-rich grid. We have also Added server-side sorting and searching data based on input user search string. All the functionality is ajax based so the page is not refreshed at the time of listing, searching, and sorting.

Recent Posts

Configure and Retrieve S3 Information Using Laravel PHP-AWS-SDK

This tutorial helps integrate a PHP SDK with Laravel. We'll install aws-php-sdk into laravel application and access all aws services… Read More

3 months ago

What is the Purpose of php_eol in PHP?

in this quick PHP tutorial, We'll discuss php_eol with examples. PHP_EOL is a predefined constant in PHP and represents an… Read More

9 months ago

Laravel Table Relationship Methods With Example

This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More

9 months ago

Exploring the Power of Laravel Eloquent Join?

We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More

9 months ago

Quick and Easy Installation of Laravel Valet

in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More

10 months ago

What is Laravel Soft Delete and How Does it Work?

I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More

10 months ago

Categories