This is a combine tutorial on jQuery DataTable, Chart.js library and Date Range Picker with PHP and MySQL database using Ajax. In this tutorial, we will make Advance Date Range Filter Application in PHP using Ajax and under this Application we will first load MySQL table data in the jQuery DataTables with Server-side processing of data using PHP and Ajax and after this, we will make bar chart from data which we have load under jquery DataTable using Chart.js library, so here it will make dynamic chart by using jQuery DataTable data, so when jQuery DataTable data change then bar chart data will be updated. After integrate Chart.js library into jQuery DataTables library and next we will integrate Date Range Picker into jQuery DataTable and Chart.js library application and apply advance date filter into this application, so when we have filter data, then filter data will be display in jQuery DataTable and that data will be use for create dynamic bar chart using Chart.js application.
This type of application we need to required at the time we have develope any analytics based application. This is because, when we have see any analytics data, then in web based application data has been load in jQuery DataTable and that DataTable has been load in Chart and that chart data has been dynamic which is based on DataTable Data. So when DataTable data has been change then chart data has also be updated without refresh of web page. In simple word, here we will display dynamic data of DataTables in the form of Chart on the web page using Chart.js library with PHP Script and MySQL Database using Ajax Server side processing of Data. After this suppose we want to get any specific date data then at the time Date range filter has been use. So when we have apply Date range filter on the jQuery DataTable then that Date Range filter also apply to Chart data also and it will make dynamic chart based on date range filter of data. In this tutorial, we have use Chart.js library for create dynamic chart and for date range filter we have use Advance date range picker for date range filter.
In this tutorial, we have apply date range filter to jQuery DataTable using Ajax with PHP and MySQL database and here we have also apply date range filter to chart also. For build this application we have use purely PHP script MySQL database and under this tutorial, we have use Ajax request from web page for fetch and filter MySQL data at server side using PHP script.
Under this tutorial, we have use following web technology.
Server-side
- PHP : In this tutorial, we have use PHP 7.2 version.
- MySQL Database : Under this tutorial, we have use MySQL 8.0 version
Client Side
- JavaScript : In some client side code, we have use pure javascript code.
- jQuery : In this tutorial, we have use jQuery library for DataTable library.
- Bootstrap 5 : Here we have use latest version of Bootstrap library for make design of this tutorial.
- jQuery DataTables : This library has been used for display order data in tabular format with advance searching, pagination etc feature.
- Chart.js : This Chart.js library has render data from DataTable and create dynamic bar chart in real time.
- Date Range Picker : This library we have used for implement Advance date range filter with different predefine date range filter.
MySQL Database Structure
For build any dynamic application, we have to store data into MySQL Database, So here also we have to create one test_order_table in which we have to store order data for last three year. So for store data into database, first we have to create database into your local phpMyadmin and under that you have to run following script, this script will create table in your MySQL database. Here we have only share table defination but when you have download source code, then under that source code you can find .SQL script with sample data with insert query, so when you have run that script, then table will be created with sample data.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `test_order_table`
--
CREATE TABLE `test_order_table` (
`order_id` int(11) NOT NULL,
`order_number` int(11) NOT NULL,
`order_quantity` decimal(10,2) NOT NULL,
`price_per_unit` decimal(10,2) NOT NULL,
`order_total` decimal(10,2) NOT NULL,
`order_date` date NOT NULL,
`order_status` varchar(100) NOT NULL,
`product_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `test_order_table`
--
ALTER TABLE `test_order_table`
ADD PRIMARY KEY (`order_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `test_order_table`
--
ALTER TABLE `test_order_table`
MODIFY `order_id` int(11) NOT NULL AUTO_INCREMENT;
Integrate Chart.js library within jQuery DataTable library
In this tutorial, first we want to integrate Chart.js library into jQuery DataTable library. So before integreate Chart.js library into jQuery DataTable library. First we want to load MySQL data from into jQuery DataTable library with Server-side processing of data.
So first we want to include required library link at the header of index.php file at the header of web page which you can seen here.
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<!-- Bootstrap CSS -->
<link href="library/bootstrap-5/bootstrap.min.css" rel="stylesheet" />
<link href="library/daterangepicker.css" rel="stylesheet" />
<link href="library/dataTables.bootstrap5.min.css" rel="stylesheet" />
<script src="library/jquery.min.js"></script>
<script src="library/bootstrap-5/bootstrap.bundle.min.js"></script>
<script src="library/moment.min.js"></script>
<script src="library/daterangepicker.min.js"></script>
<script src="library/Chart.bundle.min.js"></script>
<script src="library/jquery.dataTables.min.js"></script>
<script src="library/dataTables.bootstrap5.min.js"></script>
<title>Advance Date Range Filter in PHP MySQL using Date Range Picker</title>
</head>
After this, we have to create one HTML table with id order_table and we will convert this simple HTML table to advance table with inbuild feature like searching, pagination, sorting, paging etc by using jQuery DataTable library.
<table class="table table-striped table-bordered" id="order_table">
<thead>
<tr>
<th>Order Number</th>
<th>Order Value</th>
<th>Order Date</th>
</tr>
</thead>
<tbody></tbody>
</table>
After this, we have move to write jQuer code for initialize jQuery DataTable plugin on HTML table by using DataTable() method. Under this method we have to define different option for send Ajax request for fetch MySQL table data and receive data in JSON format and display data on the web page in DataTable in tabular format.
fetch_data();
function fetch_data()
{
var dataTable = $('#order_table').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
"ajax" : {
url:"action.php",
type:"POST",
data:{action:'fetch'}
},
});
}
So this script will send Ajax request to action.php file for fetch data from MySQL table.
After this, we have to go to action.php file and here we have to write PHP script for fetch data from text_order_table and send back data to Ajax request in JSON format.
<?php
//action.php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
if(isset($_POST["action"]))
{
if($_POST["action"] == 'fetch')
{
$order_column = array('order_number', 'order_total', 'order_date');
$main_query = "
SELECT order_number, SUM(order_total) AS order_total, order_date
FROM test_order_table
";
$search_query = 'WHERE order_date <= "'.date('Y-m-d').'" AND ';
if(isset($_POST["search"]["value"]))
{
$search_query .= '(order_number LIKE "%'.$_POST["search"]["value"].'%" OR order_total LIKE "%'.$_POST["search"]["value"].'%" OR order_date LIKE "%'.$_POST["search"]["value"].'%")';
}
$group_by_query = " GROUP BY order_date ";
$order_by_query = "";
if(isset($_POST["order"]))
{
$order_by_query = 'ORDER BY '.$order_column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
$order_by_query = 'ORDER BY order_date DESC ';
}
$limit_query = '';
if($_POST["length"] != -1)
{
$limit_query = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($main_query . $search_query . $group_by_query . $order_by_query);
$statement->execute();
$filtered_rows = $statement->rowCount();
$statement = $connect->prepare($main_query . $group_by_query);
$statement->execute();
$total_rows = $statement->rowCount();
$result = $connect->query($main_query . $search_query . $group_by_query . $order_by_query . $limit_query, PDO::FETCH_ASSOC);
$data = array();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row['order_number'];
$sub_array[] = $row['order_total'];
$sub_array[] = $row['order_date'];
$data[] = $sub_array;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $total_rows,
"recordsFiltered" => $filtered_rows,
"data" => $data
);
echo json_encode($output);
}
}
?>
So above script will fetch data from MySQL table based on the required data format for load data in jQuery DataTable and above script will send data to ajax required in JSON format.
Now we have to use same data for make bar chart by using Chart.js library, so for this, we have go to index.php file and under that file we have go to jQuery code of DataTable() method.
Under this DataTable() method we have to add drawCallback: function(settings) callback function. This function has receive DataTable data when DataTable has redraw complete data and on every event this callback function has receive data.
So for create chart, first we have to define one var sale_chart; global variable, this is because when we have chart has been redraw with updated data and after this when we have mouse over Chart.js library chart then it will display old chart on the web page. So for this we have to define chart global variable.
After this, under this function, we have to fetch data from this settings argument variable and store under this local variable in an array format. And next we have to define Chart.js configuration for create dynamic bar chart by using DataTable data.
fetch_data();
var sale_chart;
function fetch_data()
{
var dataTable = $('#order_table').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
"ajax" : {
url:"action.php",
type:"POST",
data:{action:'fetch'}
},
"drawCallback" : function(settings)
{
var sales_date = [];
var sale = [];
for(var count = 0; count < settings.aoData.length; count++)
{
sales_date.push(settings.aoData[count]._aData[2]);
sale.push(parseFloat(settings.aoData[count]._aData[1]));
}
var chart_data = {
labels:sales_date,
datasets:[
{
label : 'Sales',
backgroundColor : 'rgba(153, 102, 255)',
color : '#fff',
data:sale
}
]
};
var group_chart3 = $('#bar_chart');
if(sale_chart)
{
sale_chart.destroy();
}
sale_chart = new Chart(group_chart3, {
type:'bar',
data:chart_data
});
}
});
}
So this script will make dynamic bar chart by data render from jQuery DataTable. So on every server processing of DataTable data using Ajax then Chart data will also update when jQuery DataTable data has been change. So still under this tutorial, we have integrate Chart.js library into jQuery DataTable using Ajax with Server-side processing of data using PHP and MySQL database.
Date Range Filter with DataTables and Chart.js using Date Range Picker
Once we have integrate Chart.js library into jQuery DataTables now we want to filter DataTables Data using date range filter and when DataTable table data has been filter then Chart.js bar chart must be redraw chart with updated DataTable data. So for Date Range filter of DataTable & Chart.js data here we have use jQuery Date Range Picker plugin. By using this plugin, we can predefine some date range like filter today data, yesterday data, last 7 days data, this month data, last 30 days data, last month data and this plugin also provide custom date range filter also, so we can define our start date and end date for filter data.
For integrate date range picker plugin into our application, first we need to add date range picker library at the head of our web page. After including library file we can use jQuery Date Range Picker plugin into our web application.
<link href="library/daterangepicker.css" rel="stylesheet" />
<script src="library/daterangepicker.min.js"></script>
After this, we have to create one textbox for initialize date range picker plugin. So when we have click on the textbox then date range picker plugin will be pop up on the web page and under that textbox it will display start and end date range.
<input type="text" id="daterange_textbox" class="form-control" readonly />
Next we want to initialize date range picker plugin on newly created textbox. So for this we have go to jQuery code part, and here we have write jQuery Code for intialize date range picker library. By using daterangepicker() method we can initialize date range picker library. And under method we can define different predefine date ranges under ranges option, and for define date format, we have to define date format under format function.
After this under daterangepicker() method, we have to write one callback function, with argument like start and end date. This function will be every time called when this method has been called and under this function we have to again called fetch_date() function with start and end date as argument for filter data from two date range.
<script>
$(document).ready(function(){
fetch_data();
var sale_chart;
function fetch_data(start_date = '', end_date = '')
{
var dataTable = $('#order_table').DataTable({
"processing" : true,
"serverSide" : true,
"order" : [],
"ajax" : {
url:"action.php",
type:"POST",
data:{action:'fetch', start_date:start_date, end_date:end_date}
},
"drawCallback" : function(settings)
{
var sales_date = [];
var sale = [];
for(var count = 0; count < settings.aoData.length; count++)
{
sales_date.push(settings.aoData[count]._aData[2]);
sale.push(parseFloat(settings.aoData[count]._aData[1]));
}
var chart_data = {
labels:sales_date,
datasets:[
{
label : 'Sales',
backgroundColor : 'rgb(255, 205, 86)',
color : '#fff',
data:sale
}
]
};
var group_chart3 = $('#bar_chart');
if(sale_chart)
{
sale_chart.destroy();
}
sale_chart = new Chart(group_chart3, {
type:'bar',
data:chart_data
});
}
});
}
$('#daterange_textbox').daterangepicker({
ranges:{
'Today' : [moment(), moment()],
'Yesterday' : [moment().subtract(1, 'days'), moment().subtract(1, 'days')],
'Last 7 Days' : [moment().subtract(6, 'days'), moment()],
'Last 30 Days' : [moment().subtract(29, 'days'), moment()],
'This Month' : [moment().startOf('month'), moment().endOf('month')],
'Last Month' : [moment().subtract(1, 'month').startOf('month'), moment().subtract(1, 'month').endOf('month')]
},
format : 'YYYY-MM-DD'
}, function(start, end){
$('#order_table').DataTable().destroy();
fetch_data(start.format('YYYY-MM-DD'), end.format('YYYY-MM-DD'));
});
});
</script>
After this, we have go to action.php file and under this file, we have to write MySQL query for filter data from two date range which you can seen below.
<?php
//action.php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
if(isset($_POST["action"]))
{
if($_POST["action"] == 'fetch')
{
$order_column = array('order_number', 'order_total', 'order_date');
$main_query = "
SELECT order_number, SUM(order_total) AS order_total, order_date
FROM test_order_table
";
$search_query = 'WHERE order_date <= "'.date('Y-m-d').'" AND ';
if(isset($_POST["start_date"], $_POST["end_date"]) && $_POST["start_date"] != '' && $_POST["end_date"] != '')
{
$search_query .= 'order_date >= "'.$_POST["start_date"].'" AND order_date <= "'.$_POST["end_date"].'" AND ';
}
if(isset($_POST["search"]["value"]))
{
$search_query .= '(order_number LIKE "%'.$_POST["search"]["value"].'%" OR order_total LIKE "%'.$_POST["search"]["value"].'%" OR order_date LIKE "%'.$_POST["search"]["value"].'%")';
}
$group_by_query = " GROUP BY order_date ";
$order_by_query = "";
if(isset($_POST["order"]))
{
$order_by_query = 'ORDER BY '.$order_column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
$order_by_query = 'ORDER BY order_date DESC ';
}
$limit_query = '';
if($_POST["length"] != -1)
{
$limit_query = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($main_query . $search_query . $group_by_query . $order_by_query);
$statement->execute();
$filtered_rows = $statement->rowCount();
$statement = $connect->prepare($main_query . $group_by_query);
$statement->execute();
$total_rows = $statement->rowCount();
$result = $connect->query($main_query . $search_query . $group_by_query . $order_by_query . $limit_query, PDO::FETCH_ASSOC);
$data = array();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row['order_number'];
$sub_array[] = $row['order_total'];
$sub_array[] = $row['order_date'];
$data[] = $sub_array;
}
$output = array(
"draw" => intval($_POST["draw"]),
"recordsTotal" => $total_rows,
"recordsFiltered" => $filtered_rows,
"data" => $data
);
echo json_encode($output);
}
}
?>
So here our code is ready and our application is ready for load data into jQuery DataTable plugin and and then after we have to load DataTable data into Chart.js library. So when Datatable data has been updated then Chart.js bar chart will be redraw chart with updated data. After this, for filter DataTable data and Chart.js bar chart data using Date range filter, So for date range filter of DataTable and Chart.js bar chart data, here we have use Date range picker library. So by using this Date range picker library we can date range filter DataTable and Chart.js bar chart data.
So here we have complete this tutorial and we have hope you have learn something new from this tutorial. You can download complete source code with library filter and .SQL file with data by click on the below link and even you can also check online demo also.
0 comments:
Post a Comment