Datatable 1.10.9 has been released now, Datatable is most popular grid system in web development.Main benefit of Data-table is open source and light weighted.You can use this plugin with other css framework like bootstrap and foundation .There are a lot of datatable plugin are available which useful to improve UI experience.
I got huge response from reader on previous datatable tutorial which was DataTables Example – Server-side Processing with PHP.I have used json data in code instead of fetch data from mysql database, So i have got many request to share code with php,mysql using ajax.
Also Checkout other tutorial of Datatable,
- Part 1: Introduction of Datatable
- Part 2- DataTables Example – Server-side Processing with PHP
- Part 3 – How to add RowId and RowClass on each record using Datatable
- Part 4 – Datatable Pagination, Sorting and Search – Server Side (PHP/MySQl) Using Ajax
- Part5 – Export the jQuery Datatable data to PDF,Excel,CSV and Copy
- Part 6 – Datatable Responsive – Using PHP and Mysql with Ajax
We are using below Files,The details are:
Index.php - This file will responsible to create html and instance datatable using jquery code.
response.php - This file responsible to create database connection string and convert records into json string and returns data to Ajax method as response.
connection.php - This file responsible to create mysql database connection.
Datatable Example with PHP,MySql server side Using Ajax
Step 1: we will include jquery data-table and jquery library.
1 2 3 4 5 | <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.css"/> <script type="text/javascript" src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script> <script type="text/javascript" charset="utf8" src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.2.min.js"></script> |
Step 2: Create HTML layout in your web page (index.php).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | <div class="container"> <div class=""> <h1>Data Table</h1> <div class=""> <table id="employee_grid" class="display" width="100%" cellspacing="0"> <thead> <tr> <th>Empid</th> <th>Name</th> <th>Salary</th> <th>Age</th> </tr> </thead> <tfoot> <tr> <th>Empid</th> <th>Name</th> <th>Salary</th> <th>Age</th> </tr> </tfoot> </table> </div> </div> </div> |
Step 3: Instantiate datatable object on table(index.php).
1 2 3 4 5 6 7 8 9 10 11 12 13 | $( document ).ready(function() { $('#employee_grid').DataTable({ "bProcessing": true, "serverSide": true, "ajax":{ url :"response.php", // json datasource type: "post", // type of method , by default would be get error: function(){ // error handling code $("#employee_grid_processing").css("display","none"); } } }); }); |
here we are passing which service/file url.
Step 4: Include connection file into top of the response.php file.
1 2 | //include connection file include_once("../db_connect.php"); |
Step 5: Using parameters to create sql.
- $params[‘order’] : This array contains order information of clicked column
- $params[‘search’] : This array contains serach value information datatable
- $params[‘start’] : start limit of data
- $params[‘length’] : end limit of data
1 | $sql = "SELECT * FROM `employee` ORDER BY id asc LIMIT ".$params['start']." ,".$params['length']." "; |
Step 6: Send json data to the index.php file.
1 2 3 4 5 6 | $json_data = array( "draw" => intval( $params['draw'] ), "recordsTotal" => intval( $totalRecords ), "recordsFiltered" => intval($totalRecords), "data" => $data // total data array ); |
You can download source code and Demo from below link.
