website-developer-in-meerut
website-designer-in-meerut
freelance-webite-developer

Need an Eye-caching Website - Contact Me Today !

live-ajax-datatable-in-php-with-search-sort-pagination-export-buttons

DataTable AJAX Pagination with search, export buttons and sort in PHP

  • 90
  • 27-Feb-2023
  • Details

    index.html

    <!doctype html>
    <html>
    
        <head>
            <title>DataTable AJAX Pagination with search and sort - PHP</title>
            <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.3/jquery.min.js"></script>
            <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.2.3/css/bootstrap.min.css"
                integrity="sha512-SbiR/eusphKoMVVXysTKG/7VseWii+Y3FdHrt0EpKgpToZeemhqHeZeLWLhJutz/2ut2Vw1uQEj2MbRF+TVBUA=="
                crossorigin="anonymous" referrerpolicy="no-referrer" />
            <link rel="stylesheet" href="https://cdn.datatables.net/1.13.2/css/dataTables.bootstrap5.min.css" />
            <link rel="stylesheet"
                href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.2.0/css/bootstrap.min.css">
            <link rel="stylesheet" href="style.css">
        </head>
    
        <body class="bg-light">
    
            <div class="my-5 container-fluid">
                <!-- Table -->
                <h4 class="text-center">Live <span class="text-success">Auto-refresh</span> DataTable <span
                        class="text-success">AJAX</span> Pagination with search and sort - <span
                        class="text-warning">PHP</span></h4>
                <table id='empTable' class="table table-striped display responsive nowrap" width="100%">
                    <thead>
                        <tr>
                            <th>S.No</th>
                            <th>Employee name</th>
                            <th>Designation</th>
                            <th>Department</th>
                            <th>Office</th>
                            <th>Age</th>
                            <th>Joining Date</th>
                            <th>Salary</th>
                        </tr>
                    </thead>
    
                </table>
            </div>
    
            <script src="https://cdn.datatables.net/1.13.2/js/jquery.dataTables.min.js"></script>
            <script src="https://cdn.datatables.net/1.13.2/js/dataTables.bootstrap5.min.js"></script>
    
            <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
            <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
            <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
            <script type="text/javascript"
                src="https://cdn.datatables.net/v/bs5/dt-1.13.2/af-2.5.2/b-2.3.4/b-colvis-2.3.4/b-html5-2.3.4/b-print-2.3.4/cr-1.6.1/date-1.3.0/datatables.min.js"></script>
            <script>
                $(document).ready(function () {
                    $('#empTable').DataTable({
                        'processing': false,
                        'serverSide': true,
                        dom: 'lBfrtip',
                        buttons: [
                            'csv', 'excel', 'pdfHtml5', 'print'
                        ],
    
                        'responsive': true,
                        'serverMethod': 'post',
                        'ajax': {
                            'url': 'ajaxfile.php'
                        },
                        'columns': [
                            { data: 'EmpID' },
                            { data: 'EmpName' },
                            { data: 'Designation' },
                            { data: 'Department' },
                            { data: 'Office' },
                            { data: 'Age' },
                            { data: 'JoiningDate' },
                            { data: 'Salary' },
                        ]
                    });
                    setInterval(function () {
                        $('#empTable').DataTable().ajax.reload(null, false);
                    }, 1000);
                });
            </script>
        </body>
    
    </html>

    config.php

    <?php
    $host = "localhost"; /* Host name */
    $user = "root"; /* User */
    $password = ""; /* Password */
    $dbname = "test"; /* Database name */
    $con = mysqli_connect($host, $user, $password, $dbname);
    // Check connection
    if (!$con) {
        die("Connection failed: " . mysqli_connect_error());
    }

    ajaxfile.php

    <?php
    include 'config.php';
    
    ## Read value
    $draw = $_POST['draw'];
    $row = $_POST['start'];
    $rowperpage = $_POST['length']; // Rows display per page
    $columnIndex = $_POST['order'][0]['column']; // Column index
    $columnName = $_POST['columns'][$columnIndex]['data']; // Column name
    $columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
    $searchValue = mysqli_real_escape_string($con, $_POST['search']['value']); // Search value
    
    ## Search 
    $searchQuery = " ";
    if ($searchValue != '') {
    	$searchQuery = " and (EmpName like '%" . $searchValue . "%' or 
            Designation like '%" . $searchValue . "%' or 
            Department like'%" . $searchValue . "%' ) ";
    }
    
    ## Total number of records without filtering
    $sel = mysqli_query($con, "select count(*) as allcount from employee");
    $records = mysqli_fetch_assoc($sel);
    $totalRecords = $records['allcount'];
    
    ## Total number of records with filtering
    $sel = mysqli_query($con, "select count(*) as allcount from employee WHERE 1 " . $searchQuery);
    $records = mysqli_fetch_assoc($sel);
    $totalRecordwithFilter = $records['allcount'];
    
    ## Fetch records
    $empQuery = "select * from employee WHERE 1 " . $searchQuery . " order by " . $columnName . " " . $columnSortOrder . " limit " . $row . "," . $rowperpage;
    $empRecords = mysqli_query($con, $empQuery);
    $data = array();
    
    while ($row = mysqli_fetch_assoc($empRecords)) {
    	$data[] = array(
    		"EmpID" => $row['EmpID'],
    		"EmpName" => $row['EmpName'],
    		"Designation" => $row['Designation'],
    		"Department" => $row['Department'],
    		"Office" => $row['Office'],
    		"Age" => $row['Age'],
    		"JoiningDate" => date('d M Y h:i:s A'),
    		"Salary" => $row['Salary'],
    	);
    }
    
    ## Response
    $response = array(
    	"draw" => intval($draw),
    	"iTotalRecords" => $totalRecords,
    	"iTotalDisplayRecords" => $totalRecordwithFilter,
    	"aaData" => $data
    );
    
    echo json_encode($response);
    

    test.sql

    -- phpMyAdmin SQL Dump
    -- version 5.2.0
    -- https://www.phpmyadmin.net/
    --
    -- Host: 127.0.0.1:3306
    -- Generation Time: Feb 27, 2023 at 12:58 PM
    -- Server version: 8.0.27
    -- PHP Version: 8.1.0
    
    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    START TRANSACTION;
    SET time_zone = "+00:00";
    
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8mb4 */;
    
    --
    -- Database: `test`
    --
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `employee`
    --
    
    DROP TABLE IF EXISTS `employee`;
    CREATE TABLE IF NOT EXISTS `employee` (
      `EmpID` int NOT NULL AUTO_INCREMENT,
      `EmpName` varchar(50) NOT NULL,
      `Designation` varchar(50) DEFAULT NULL,
      `Department` varchar(50) DEFAULT NULL,
      `Office` varchar(50) NOT NULL,
      `Age` int NOT NULL,
      `JoiningDate` datetime DEFAULT NULL,
      `Salary` float NOT NULL,
      PRIMARY KEY (`EmpID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
    
    --
    -- Dumping data for table `employee`
    --
    
    INSERT INTO `employee` (`EmpID`, `EmpName`, `Designation`, `Department`, `Office`, `Age`, `JoiningDate`, `Salary`) VALUES
    (1, 'Tiger Nixonasdasda', 'System Architect', 'IT', 'Edinburgh', 61, '2011-04-25 00:00:00', 320800),
    (2, 'Shobha Saini', 'Senior Manager', 'HR', 'India', 38, '2011-07-25 00:00:00', 190750),
    (3, 'Ashton Cox', 'Junior Technical Author', 'Engineering', 'San Francisco', 53, '2009-01-12 00:00:00', 86000),
    (4, 'Aakash Kumar', 'Senior Javascript Developer', 'IT', 'Edinburgh', 28, '2012-03-29 00:00:00', 433060),
    (5, 'Sagar Kumar', 'Accountant', 'Accounts', 'Tokyo', 32, '2008-11-28 00:00:00', 175700),
    (6, 'Brielle Williamson', 'Integration Specialist', 'IT', 'New York', 55, '2012-12-02 00:00:00', 354780),
    (7, 'Herrod Chandler', 'Sales Assistan', 'Sales', 'San Francisco', 39, '2012-08-06 00:00:00', 234500),
    (8, 'Rhona Davidson', 'Integration Specialist', 'IT', 'Tokyo', 55, '2010-10-14 00:00:00', 327900),
    (9, 'Colleen Hurst', 'Talent Aquision', 'HR', 'San Francisco', 40, '2009-09-15 00:00:00', 205500),
    (10, 'Sonya Frost', 'Software Engineer', 'IT', 'Edinburgh', 22, '2008-12-13 00:00:00', 110600),
    (11, 'Jena Gaines', 'Office Manager', 'Admin', 'London', 30, '2008-12-19 00:00:00', 99800);
    COMMIT;
    
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    

whatsapp

Sagar Kumar
Typically replies within an hour

Atechseva
Hi there 👋

How can I help you?
×
Chat