The Datatable 1.10.9 has been released now, Datatable is most popular grid plugin in web development. The Main benefit of Datatable is open source and light weighted jquery plugin. You can use this plugin with other CSS frameworks like bootstrap and foundation. There are a lot of datatable plugins 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 the MySQL database, So I have got many requests 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
There are following files are participate in this Datatble tutorial:
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
We will create simple PHP application to fecth data from server using PHP.The php is using mysql to store and fetch data.
The jQuery datatable help to display records into html page with pagination sorting and searching feature.
How To Create MySQL Database Connection Using mysql_connect
We will Create db_connect.php
file into root of the project path, Now we will put the below connection code in this file.
1 2 3 4 5 6 | $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ""; $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql'); $dbname = 'test'; $connection = mysql_select_db($dbname); |
Step 1: we will include jquery datatable and jquery library.
1 2 3 | <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 | <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"); } } }); }); |
Above code – We have applied datatable on employee_grid
table.The serverSide have the rest url path which will use to get data from backend server.
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.