In this jQuery DataTable tutorial, we learn How to Display Data From Database in jQuery DataTable step by step.
Previously we already learn about How jQuery DataTables works with HTML tables with static data.
Complete short Guide About jQuery DataTables
In this article, we perform the MySQL database operation with jQuery DataTables to bind the DB data in DataTables.
Here are the 4 steps to display the MySQL Data to the jQuery DataTable,
- Import the given DB code to create the employee table.
- Create a MySQL database connection.
- Creating the design file and include the jQuery DataTable CDN links.
- Creating the MySQL query file to get the data using jQuery.
Step-1: Create User or Employee Table
Paste below code on your existing database on PHPMYADMIN.
|
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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
-- -- Database: `test` -- -- -------------------------------------------------------- -- -- Table structure for table `emp` -- CREATE TABLE `emp` ( `id` int(50) NOT NULL, `name` varchar(100) NOT NULL, `date_s` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `emp` -- INSERT INTO `emp` (`id`, `name`, `date_s`) VALUES (1, 'Divakar', '2020-04-24'), (2, 'Bhanu', '2020-04-25'), (4, 'Bikash', '2020-04-26'), (5, 'Sanjay', '2020-05-06'); -- -- Indexes for dumped tables -- -- -- Indexes for table `emp` -- ALTER TABLE `emp` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `emp` -- ALTER TABLE `emp` MODIFY `id` int(50) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15; |
After creating the table, table look like this,
Step-2: Creating Connection with MySQL Database
In this step, we create the connection with database and then we check out the complete source code to How to Display Data From Database in jQuery DataTable.
|
1 2 3 |
<?php $con = mysqli_connect("localhost", "DB_USERNAME", "DB_PASSWORD", "DB_NAME"); ?> |
Step-3: Creating the design file and include the jQuery DataTable CDN links
|
1 |
<script src="//cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script> |
Complete Source code to Display Data From Database in jQuery DataTable
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 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
<!DOCTYPE html> <html> <head> <title>How to display data from database in jquery datatable?</title> <!-- Latest compiled and minified CSS --> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css"> <!-- Date picker cdn --> <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css"> <script src="//cdn.datatables.net/1.10.21/js/jquery.dataTables.min.js"></script> <!-- data table cdn --> <link rel="stylesheet" href="//cdn.datatables.net/1.10.20/css/jquery.dataTables.min.css"> </head> <body> <div class="container mt-5" id="get"> <table id="my-example"> <thead> <tr> <th>Id</th> <th>Name</th> <th>Date</th> </tr> </thead> </table> </div> </body> </html> <script type="text/javascript"> $(document).ready(function() { $('#my-example').dataTable({ "bProcessing": true, "sAjaxSource": "get.php", "aoColumns": [ { mData: 'id' } , { mData: 'name' }, { mData: 'date_s' } ] }); }); </script> |
Step-4: Creating the MySQL Query File to get the Data
get.php
This is the query file which gives complete data from emp table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
<?php $con = mysqli_connect("localhost", "root", "", "test"); $sql = "SELECT * FROM emp"; $result = mysqli_query($con,$sql); while($row = mysqli_fetch_assoc($result)){ $data[] = $row; } // print_r($data); $results = ["sEcho" => 1, "iTotalRecords" => count($data), "iTotalDisplayRecords" => count($data), "aaData" => $data ]; echo json_encode($results); ?> |
On the time of implementation, if you facing any issue ping me on comment box. I will try to resolve the issue.
Also Check:
- Integrate Google reCaptcha in Codeigniter
- Laravel 5.7 CRUD application
- How to run Laravel project on localhost
Happy Coding..!
Hi i actually want to refresh my datatable contents using ajax I’m not able find any help can u help me
[…] Display Data From Database in jQuery DataTable […]