Data tables, is a very useful and most popular custom 3rd party JavaScript library to display data in HTML table. Either you want to display static data from Json file or dynamic data from MySQL database this is very useful library to save time on development.
We are going to create dynamic data tables from our MySQL database data.
Our final data tables result will be look a like as the following screenshots.
So, let’s get started with our data tables coding…
Installation
Before we get started our data tables main coding we need to install the libraries / dependencies. As it’s a jQuery data table we must need to include jQuery library. You can use jQuery from the CDN link or from your local downloaded library. I’m going to use CDN from both jQuery and data tables libraries.
Add the following script tags to the head or footer section of your HTML page.I’m going to add it to my footer section to make sure it loads smoothly without script blocking issues on search engines.
jQuery CDN
You can get a jQuery CDN link from jQuery official website or from cdnjs.com/libraries/jquery website. I’m going to use the library from cdnjs.com.
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.5.1/jquery.min.js" integrity="sha512-bLT0Qm9VnAYZDflyKcBaQ2gg0hSYNQrJ8RilYldYQ1FxQYoCLtUjuuRuZo+fjqhx/qtq/1itJ0C2ejDxltZVFg==" crossorigin="anonymous"></script>
Data Tables CDN
Now we need data tables scripts. Same as the jQuery library you can use the CDN link or use the local scripts downloaded to your computer. I’m going to use CDN as it load faster in the browser. You can get the library from cdnjs.com/libraries/datatables website.
Add the following scripts to your header or footer after the jQuery script’s tag added on your HTML file.
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.22/css/jquery.dataTables.css">
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.22/js/jquery.dataTables.js"></script>
So, we are done adding our libraries. If you want to know more about data table installation you can read it from the data table official installation document .
Now we need to go to the actual coding…
HTML and JavaScript / jQuery scripts
HTML code snippets
We need to add valid HTML in our HTML page to render the data smoothly. Take a look at the following example. I’m going to add the following HTML code snippets to my HTML page where I want to appear the data tables.
<table class="table table-striped coin-data-table" style="width: 100%;">
<thead>
<tr>
<th width="5%"></th>
<th>Currency Name</th>
<th>Market Cap.(USD)</th>
<th>Supply Counting</th>
<th>Price(USD)</th>
<th>Last Updated On</th>
</tr>
</thead>
</table>
Then I’m going to add my JavaScripts and jQuery code after the jQuery and data tables library tags.
jQuery code snippets
<script>
$(document).ready(function () {
$('.coin-data-table')
.DataTable({
responsive: true,
info: true,
"processing": true,
"serverSide": true,
"ajax": "../ajax-data.php"
});
});
</script>
If you take a look at the code, you will see I’ve added an option "ajax": "../ajax-data.php"
ajax-data.php
is the PHP file from where we will get MySQL data from our database. I have created the PHP file in my scripts already.
PHP, MySQL data table code snippets
Now we need to create our PHP scripts to get data from our MySQL database. I assume you know how to connect PHP, MySQL and pull data from MySQL table. Suppose, we have pulled our MySQL data and stored in a variable called $coins. We will loop through the results.
$coins = 'data pulled from MySQL';
At first we need to receive values from our jQuery script we sent to our PHP file. We will receive it by following code.
$start = isset($_REQUEST['start']) ? $_REQUEST['start'] : 0;
$length = isset($_REQUEST['length']) ? $_REQUEST['length'] : 10;
$draw = isset($_REQUEST['draw']) ? $_REQUEST['draw'] : 0;
$search = isset($_REQUEST['search']) ? $_REQUEST['search']['value'] : 0;
Now, we will loop through our MySQL data to process it to render in our data tables.
if( isset($coins->data) ){
$i = 0; $data = []; $j = 0; $is_search = false;
foreach( $coins->data as $item ){
if( !empty($search)){
if( strpos( strtolower($item->name), strtolower($search ) ) !== false ){
$data[] = array(
$img,
$item->name . ' ( ' . $item->symbol . ' ) ' . $is_sponsored . $url,
empty($item->market_cap_usd) || $item->market_cap_usd <= 0 ? "N/A" : '$'.$item->market_cap_usd,
$supply,
'$' . floatval( $item->price_usd),
$last_updated
);
}
$is_search = true;
}else{
$data[] = array(
$img, $item->name . ' ( ' . $item->symbol . ' ) ' . $is_sponsored . $url, empty($item->market_cap_usd) || $item->market_cap_usd <= 0 ? "N/A" : '$'. $item->market_cap_usd,
$supply,
'$' . floatval( $item->price_usd),
$last_updated
);
}
}
if( $is_search === true ){
$recordsFiltered = count($data);
$data = array_slice( $data, -$start, $length );
}
}
Now we need to return our result to jQuery. We will send it as Json data. Take a look at the following code snippets..
echo json_encode(
array(
'start' => $start,
'le' => $length,
"draw" => $draw,
"recordsTotal"=> $total_count,
"recordsFiltered"=> $recordsFiltered,
"data" => $data
)
);
That’s all. Our data table should render smoothly and search will work perfectly too. If you want you can add search directly to your MySQL code when you pull your data from database.
My final result is showing here - in my https://coinmarketstats.online website.