Hello guys! Today I will tell you how to use a jQuery plugin to display database records in a table. I must say as a beginner, this is an amazing plugin that makes our work much easier. It's name is DataTables plugin.Only few lines of codes are required. It is connected with Bootstrap. The styling part is done using Bootstrap. So I'm going to tell you the way to use this plugin.
Remember! This is working for TABLES in HTML. You can use it when you want to display records using a table.
Remember! This is working for TABLES in HTML. You can use it when you want to display records using a table.
When you surf on web, you may have seen some records are shown in a specific way with a search bar and paginated pages. I think you will. This is the way how we can do it. A short demo has been uploaded to get an idea about what I'm going to do today. Watch it.
Is it wonderful? Don't worry..jQuery does the job for us. Let's go!
Download source code ; jQuery DataTables
As usual you need to create a project folder and create these files and folders first.
dbcon.php => connects to the database
index.php => shows the records
pics folder => stores images
Before go for coding, create the database next. In my case, database is about countries. I have uploaded the SQL file ti GitHub. You can use it to create database. The instructions to run the project has been given also.
My database => ajax_search
Are you ready to proceed? Ok...Now you have to download the jQuery plugin file. Visit the below link to get the CDN for the plugin.
DataTables Plugin
I will put the code. If you couldn't find, don't worry!
Now I'm going to create dbcon.php file to connect with database as usual.
dbcon.php
<?php
//build the connection variable and assign database credentials
$conn = mysqli_connect('localhost','root','','ajax_search');
//if DB connection fails, get a error message
if(!$conn){
die('Connection Failed'.mysqli_connect_error());
}
?>
Then we have to create our view and the logic to retrieve records using PHP. All the lines have been described by comments.
index.php
<!DOCTYPE html>
<html>
<head>
<title>jQuery DataTables</title>
<meta charset="UTF-8">
<!-- Bootstrap CSS styles -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<!-- jQuery DataTables CSS file CDN -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/dataTables.bootstrap.min.css">
<!-- Google jQuery CDN -->
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<!-- Roboto Google font -->
<link href="https://fonts.googleapis.com/css?family=Roboto" rel="stylesheet">
<!-- Custom Styles -->
<style type="text/css">
body {
padding-top: 30px;
font-family: 'Roboto', sans-serif;
font-size: 16px;
}
h1 {
margin-bottom: 30px;
}
</style>
</head>
<body>
<div class="container">
<div class="row">
<center>
<h1 class="text-center">jQuery DataTables Plugin
</h1>
</center>
<div class="col-lg-1"></div>
<div class="col-lg-10">
<!-- table to displa data -->
<table class="table table-striped table-hover">
<thead>
<tr class="info">
<th>ID</th>
<th>Country Name</th>
<th>Currency</th>
<th>Flag</th>
</tr>
</thead>
<tbody>
<?php
//include database connection
require "dbcon.php";
//SQL query to retieve all the details of the countries stored
$sql = "SELECT * FROM countries";
//execution of SQL query
$res = mysqli_query($conn,$sql);
//check the number of rows is greater than zero
if(mysqli_num_rows($res)>0){
//Fetch the data into an array
while ($row = mysqli_fetch_array($res)) {
//print the data within styling tags
echo "<tr>";
//concatenate data with HTML tags
echo "<td>".$row['id']."</td>
<td>".$row['country']."</td>
<td>".$row['currency']."</td>
<td style='width: 200px;'>"."<img src='".$row["flag"]."' "."style='height: 30px; width:50px; margin-right: 50px'".">"."</td>";
echo "</tr>";
}
}
?>
</tbody>
</table>
</div>
<div class="col-lg-1"></div>
</div>
</div>
<!-- Bootstrap JS file -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
<!-- jQuery DataTables JS file CDN -->
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<!-- jQuery DataTables JS for Bootstrap file CDN -->
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap.min.js"></script>
<!-- start jQuery function -->
<script type="text/javascript">
// start jQuery function to load the content of all functions after the page is loaded completely
$(document).ready(function(){
$('.table').DataTable();
});
</script>
<!-- end jQuery function -->
</body>
</html>
Here carefully look, one CSS file and jQuery file are linked to the page. Those are the two lines that do the job! Remember you need to link jQuery also!
<!-- Google jQuery CDN -->
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<!-- jQuery DataTables JS file CDN -->
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<!-- jQuery DataTables JS for Bootstrap file CDN -->
<script src="https://cdn.datatables.net/1.10.16/js/dataTables.bootstrap.min.js"></script>
These 3 links must be in this index.php file...
You can see a PHP code in the middle of this file. It does the data retrieving part for us. The fetched data will be displayed in a HTML table. Details of each record has been included within the tags. I think it can be understood and now I move to the most important part of this tutorial.
jQuery function
<!-- start jQuery function -->
<script type="text/javascript">
// start jQuery function to load the content of all functions after the page is loaded completely
$(document).ready(function(){
$('.table').DataTable();
});
</script>
<!-- end jQuery function -->
These little 3 lines of codes implement the PAGINATION , SORTING and AUTOCOMPLETE SEARCH functions! Can you believe it? You have to believe! First jQuery is getting ready until page is loaded completely. And then refer to the table element and just include DataTable function which comes with this plugin, Then all done guys! Now you will have a nice records table with pagination and search...
More on DataTable Plugin
There is a bunch of things comes with this plugin..I write about only few options...All the other options should be entered within this DataTable function.
$(document).ready(function(){
$('.table').DataTable({
// include the additional options
// option 1 : status ,
// option 2 : status ,
// option 3 : status,
// ................
// final option : status
});
});
1. Disable Sorting
You can see sorting options when you click on table headers. Those are really interesting. But if you don't want to use it, just include this. Then sorting options will be disappeared.
"ordering" : false
2. Disable Search
If you want to hide the search bar, you can include this.
"searching" : false
3. Disable pagination
You can hide pagination and display all the records in one page by doing this.
"paging" : false
If you want to sort a particular column descending order, you can order data like this. By default, all records are sorted in ascending order.
"searching" : true,
"order" : [[1 , "desc"]]
This code will sort the 2nd column of the table in descending order!
I will link a video to understand more on these DataTables.
Ok guys...Now our DataTable has been implemented completely..At the moment I got to know about this plugin, I was amazed because I have wasted too much time to implement pagination using PHP..This is so easy! So, I invite you to try this and introduce the others if they don't know the way to get pagination, sorting and searching for a set of database records..Hope you will follow me and get the maximum advantage..
Good Luck!
DataTables Plugin available in jQuery with Bootstrap
Reviewed by DAL
on
January 07, 2018
Rating:
Reviewed by DAL
on
January 07, 2018
Rating:
No comments: